Slt voici ce que donne le plan d'éxécution avec cette condition dans la jointure de gauche:
Code : Sélectionner tout - Visualiser dans une fenêtre à part p.designation LIKE 'BON de RECEPTION de TRANSFERT%' AND p.NUM_PIECE_INTERNE NOT IN (SELECT num_piece_interne FROM achat WHERE designation LIKE 'ANNULATION d''un BON de RECEPTION de TRANSFERT%')
et sans cette condtion:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 1spjpwqkhvtkb, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */DISTINCT t.NUM_PIECE_INTERNE "PIECE INTERNE",t.NUM_PIECE AS NUM_PIECE_E,TO_CHAR(t.date_achat ,'DD/MM/YYYY')"DATE ENVOI",t.VALEUR "VALEUR ENVOI",t.tiers ||' : ' || e.description AS Receveur,p.NUM_PIECE AS NUM_PIECE_R,TO_CHAR(p.date_achat ,'DD/MM/YYYY') "DATE RECEPTION",p.VALEUR "VALEUR RECEPTION",t.VALEUR+p.VALEUR AS Ecart FROM achat t LEFT JOIN etablissement e ON t.tiers= e.code_etabl LEFT JOIN achat p ON TRANSLATE(t.NUM_PIECE_INTERNE , '0123456789' || t.NUM_PIECE_INTERNE, '0123456789')=TRANSLATE(p.NUM_PIECE_INTERNE , '0123456789' || p.NUM_PIECE_INTERNE, '0123456789') AND p.designation LIKE 'BON de RECEPTION de TRANSFERT%' AND p.NUM_PIECE_INTERNE NOT IN (SELECT num_piece_interne FROM achat WHERE designation LIKE 'ANNULATION d''un BON de RECEPTION de TRANSFERT%') AND p.tiers= t.code_etabl AND p.CODE_ETABL=t.tiers WHERE t.CODE_ETABL ='432' AND t.designation LIKE 'BON d'' EXPEDITION de TRANSFERT%' AND t.NUM_PIECE_INTERNE NOT IN (SELECT num_piece_inte Plan hash value: 4009208567 ----------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 200 |00:00:09.11 | 105K| 985 | | | | | 1 | SORT ORDER BY | | 1 | 1 | 200 |00:00:09.11 | 105K| 985 | 33792 | 33792 |30720 (0)| | 2 | HASH UNIQUE | | 1 | 1 | 200 |00:00:09.11 | 105K| 985 | 721K| 721K| 1224K (0)| |* 3 | FILTER | | 1 | | 200 |00:00:31.42 | 105K| 985 | | | | | 4 | NESTED LOOPS OUTER | | 1 | 1 | 767 |00:00:22.35 | 105K| 985 | | | | | 5 | NESTED LOOPS OUTER | | 1 | 6 | 765 |00:00:00.59 | 1223 | 464 | | | | |* 6 | HASH JOIN ANTI NA | | 1 | 6 | 765 |00:00:00.54 | 464 | 462 | 752K| 752K| 1242K (0)| | 7 | TABLE ACCESS BY INDEX ROWID | ACHAT | 1 | 644 | 918 |00:00:00.36 | 438 | 437 | | | | | 8 | BITMAP CONVERSION TO ROWIDS | | 1 | | 918 |00:00:00.29 | 194 | 194 | | | | | 9 | BITMAP AND | | 1 | | 1 |00:00:00.28 | 194 | 194 | | | | | 10 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 2 |00:00:00.22 | 178 | 178 | | | | | 11 | SORT ORDER BY | | 1 | | 22111 |00:00:00.22 | 178 | 178 | 619K| 472K| 550K (0)| |* 12 | INDEX RANGE SCAN | INDEX2 | 1 | 35647 | 22111 |00:00:00.13 | 178 | 178 | | | | | 13 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.05 | 16 | 16 | | | | | 14 | SORT ORDER BY | | 1 | | 4100 |00:00:00.05 | 16 | 16 | 133K| 133K| 118K (0)| |* 15 | INDEX RANGE SCAN | INDEXDATEGENVCODEA | 1 | 35647 | 4100 |00:00:00.05 | 16 | 16 | | | | |* 16 | INDEX RANGE SCAN | INDEX2 | 1 | 2330 | 2274 |00:00:00.03 | 26 | 25 | | | | | 17 | TABLE ACCESS BY INDEX ROWID | ETABLISSEMENT | 765 | 1 | 755 |00:00:00.04 | 759 | 2 | | | | |* 18 | INDEX UNIQUE SCAN | ETABL_CODE_ETABL | 765 | 1 | 755 |00:00:00.02 | 4 | 1 | | | | | 19 | VIEW | | 765 | 1 | 567 |00:00:08.51 | 104K| 521 | | | | |* 20 | HASH JOIN ANTI NA | | 765 | 1 | 567 |00:00:08.51 | 104K| 521 | 769K| 769K| 385K (0)| |* 21 | TABLE ACCESS BY INDEX ROWID | ACHAT | 765 | 1 | 609 |00:00:02.62 | 38046 | 410 | | | | |* 22 | INDEX RANGE SCAN | INDEX1 | 765 | 5 | 73906 |00:00:00.11 | 2500 | 7 | | | | |* 23 | INDEX RANGE SCAN | INDEX2 | 588 | 12383 | 6706K|00:00:04.67 | 66454 | 111 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("P"."NUM_PIECE" IS NULL) 6 - access("T"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 12 - access("T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%') filter(("T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%' AND "T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%')) 15 - access("T"."CODE_ETABL"='432' AND "T"."DATE_ACHAT">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DATE_ACHAT"<=TO_DATE(' 2013-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter(("T"."DATE_ACHAT"<=TO_DATE(' 2013-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DATE_ACHAT">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."CODE_ETABL"='432')) 16 - access("DESIGNATION" LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') filter("DESIGNATION" LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') 18 - access("T"."TIERS"="E"."CODE_ETABL") 20 - access("P"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 21 - filter(("P"."DESIGNATION" LIKE 'BON de RECEPTION de TRANSFERT%' AND TRANSLATE("T"."NUM_PIECE_INTERNE",'0123456789'||"T"."NUM_PIECE_INTERNE",'0123456789')=TRANSLATE("P"."NUM_PIECE_INTERNE",'0123456789'||"P"."NUM_PIEC E_INTERNE",'0123456789'))) 22 - access("P"."TIERS"="T"."CODE_ETABL" AND "P"."CODE_ETABL"="T"."TIERS") 23 - access("DESIGNATION" LIKE 'ANNULATION d''un BON de RECEPTION de TRANSFERT%') filter("DESIGNATION" LIKE 'ANNULATION d''un BON de RECEPTION de TRANSFERT%') 72 rows selected
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 40tw30cyr5kv0, child number 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */DISTINCT t.NUM_PIECE_INTERNE "PIECE INTERNE",t.NUM_PIECE AS NUM_PIECE_E,TO_CHAR(t.date_achat ,'DD/MM/YYYY')"DATE ENVOI",t.VALEUR "VALEUR ENVOI",t.tiers ||' : ' || e.description AS Receveur,p.NUM_PIECE AS NUM_PIECE_R,TO_CHAR(p.date_achat ,'DD/MM/YYYY') "DATE RECEPTION",p.VALEUR "VALEUR RECEPTION",t.VALEUR+p.VALEUR AS Ecart FROM achat t LEFT JOIN etablissement e ON t.tiers= e.code_etabl LEFT JOIN achat p ON TRANSLATE(t.NUM_PIECE_INTERNE , '0123456789' || t.NUM_PIECE_INTERNE, '0123456789')=TRANSLATE(p.NUM_PIECE_INTERNE , '0123456789' || p.NUM_PIECE_INTERNE, '0123456789') AND p.tiers= t.code_etabl AND p.CODE_ETABL=t.tiers WHERE t.CODE_ETABL ='432' AND t.designation LIKE 'BON d'' EXPEDITION de TRANSFERT%' AND t.NUM_PIECE_INTERNE NOT IN (SELECT num_piece_interne FROM achat WHERE designation LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') AND t.date_achat BETWEEN '01/01/2013' AND '18/11/2013' AND p.NUM_PIECE IS NULL ORDER BY t.NUM_PIECE_INTERNE Plan hash value: 3368236973 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 177 |00:00:00.90 | 37928 | | | | | 1 | SORT ORDER BY | | 1 | 1 | 177 |00:00:00.90 | 37928 | 31744 | 31744 |28672 (0)| | 2 | HASH UNIQUE | | 1 | 1 | 177 |00:00:00.89 | 37928 | 721K| 721K| 1259K (0)| | 3 | NESTED LOOPS OUTER | | 1 | 1 | 177 |00:00:01.76 | 37928 | | | | |* 4 | FILTER | | 1 | | 177 |00:00:01.76 | 37757 | | | | | 5 | NESTED LOOPS OUTER | | 1 | 1 | 805 |00:00:00.79 | 37757 | | | | |* 6 | HASH JOIN ANTI NA | | 1 | 6 | 765 |00:00:00.08 | 463 | 752K| 752K| 1196K (0)| | 7 | VIEW | VW_DTP_91207A43 | 1 | 644 | 918 |00:00:00.08 | 437 | | | | | 8 | HASH UNIQUE | | 1 | 644 | 918 |00:00:00.08 | 437 | 750K| 750K| 1272K (0)| | 9 | TABLE ACCESS BY INDEX ROWID | ACHAT | 1 | 644 | 918 |00:00:00.07 | 437 | | | | | 10 | BITMAP CONVERSION TO ROWIDS | | 1 | | 918 |00:00:00.07 | 194 | | | | | 11 | BITMAP AND | | 1 | | 1 |00:00:00.07 | 194 | | | | | 12 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 2 |00:00:00.07 | 178 | | | | | 13 | SORT ORDER BY | | 1 | | 22111 |00:00:00.07 | 178 | 619K| 472K| 550K (0)| |* 14 | INDEX RANGE SCAN | INDEX2 | 1 | 35647 | 22111 |00:00:00.02 | 178 | | | | | 15 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 16 | | | | | 16 | SORT ORDER BY | | 1 | | 4100 |00:00:00.01 | 16 | 133K| 133K| 118K (0)| |* 17 | INDEX RANGE SCAN | INDEXDATEGENVCODEA | 1 | 35647 | 4100 |00:00:00.01 | 16 | | | | |* 18 | INDEX RANGE SCAN | INDEX2 | 1 | 2330 | 2274 |00:00:00.01 | 26 | | | | |* 19 | TABLE ACCESS BY INDEX ROWID | ACHAT | 765 | 1 | 628 |00:00:00.79 | 37294 | | | | |* 20 | INDEX RANGE SCAN | INDEX1 | 765 | 43 | 73906 |00:00:00.04 | 1748 | | | | | 21 | TABLE ACCESS BY INDEX ROWID | ETABLISSEMENT | 177 | 1 | 167 |00:00:00.01 | 171 | | | | |* 22 | INDEX UNIQUE SCAN | ETABL_CODE_ETABL | 177 | 1 | 167 |00:00:00.01 | 4 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("P"."NUM_PIECE" IS NULL) 6 - access("ITEM_2"="NUM_PIECE_INTERNE") 14 - access("T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%') filter(("T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%' AND "T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%')) 17 - access("T"."CODE_ETABL"='432' AND "T"."DATE_ACHAT">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DATE_ACHAT"<=TO_DATE(' 2013-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter(("T"."DATE_ACHAT"<=TO_DATE(' 2013-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DATE_ACHAT">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."CODE_ETABL"='432')) 18 - access("DESIGNATION" LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') filter("DESIGNATION" LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') 19 - filter(TRANSLATE("ITEM_2",'0123456789'||"ITEM_2",'0123456789')=TRANSLATE("P"."NUM_PIECE_INTERNE",'0123456789'||"P"."NUM_PIECE_INTERNE ",'0123456789')) 20 - access("P"."TIERS"="ITEM_3" AND "P"."CODE_ETABL"="ITEM_1") 22 - access("ITEM_1"="E"."CODE_ETABL") Note ----- - cardinality feedback used for this statement 71 rows selected
Partager