Bonjour, j'ai une requête sql qui prend énormément de temps à s'exécuter en jointure externe gauche(LEFT JOIN), alors que si je fais une jointure interne(INNER JOIN) en récupérant ceux qui respectent le critère de jointure c'est à dire ceux qui ont des données a droite dans une CTE et les exclure ces lignes dans la jointure la requête s'exécute plus rapidement. j'aimerais qu'on m'aide à optimisé cette requête:
Voici la première requete avec toutes les lignes:
et voici ses statistiques:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 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_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 asc
pour la seconde requête avec les lignes respectant la jointure exclues:
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 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 16tfxvhh1ydhk, 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: 1999085137 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 200 |00:11:08.77 | 5249K| 5067K| | | | | 1 | SORT ORDER BY | | 1 | 1 | 200 |00:11:08.77 | 5249K| 5067K| 33792 | 33792 |30720 (0)| | 2 | HASH UNIQUE | | 1 | 1 | 200 |00:11:08.77 | 5249K| 5067K| 721K| 721K| 1275K (0)| |* 3 | FILTER | | 1 | | 200 |00:15:01.60 | 5249K| 5067K| | | | | 4 | NESTED LOOPS OUTER | | 1 | 1 | 767 |00:10:14.11 | 5249K| 5067K| | | | | 5 | NESTED LOOPS OUTER | | 1 | 2 | 765 |00:00:02.96 | 9771 | 8931 | | | | |* 6 | HASH JOIN ANTI NA | | 1 | 2 | 765 |00:00:02.90 | 9012 | 8929 | 752K| 752K| 1238K (0)| |* 7 | TABLE ACCESS BY INDEX ROWID| ACHAT | 1 | 234 | 918 |00:00:00.09 | 401 | 328 | | | | |* 8 | INDEX RANGE SCAN | INDEXCODE_ETABLA | 1 | 4925 | 4100 |00:00:00.03 | 11 | 10 | | | | |* 9 | TABLE ACCESS FULL | ACHAT | 1 | 8016 | 2274 |00:00:01.03 | 8611 | 8601 | | | | | 10 | TABLE ACCESS BY INDEX ROWID | ETABLISSEMENT | 765 | 1 | 755 |00:00:00.05 | 759 | 2 | | | | |* 11 | INDEX UNIQUE SCAN | ETABL_CODE_ETABL | 765 | 1 | 755 |00:00:00.03 | 4 | 1 | | | | | 12 | VIEW | | 765 | 1 | 567 |00:12:08.02 | 5239K| 5059K| | | | |* 13 | HASH JOIN ANTI NA | | 765 | 1 | 567 |00:12:08.02 | 5239K| 5059K| 769K| 769K| 409K (0)| |* 14 | TABLE ACCESS BY INDEX ROWID| ACHAT | 765 | 1 | 609 |00:00:06.89 | 176K| 1675 | | | | |* 15 | INDEX RANGE SCAN | INDEXCODE_ETABLA | 765 | 335 | 1481K|00:00:01.69 | 25784 | 536 | | | | |* 16 | TABLE ACCESS FULL | ACHAT | 588 | 8016 | 6706K|00:02:37.15 | 5063K| 5057K| | | | ---------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("P"."NUM_PIECE" IS NULL) 6 - access("T"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 7 - filter(("T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%' 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'))) 8 - access("T"."CODE_ETABL"='432') 9 - filter("DESIGNATION" LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') 11 - access("T"."TIERS"="E"."CODE_ETABL") 13 - access("P"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 14 - filter(("P"."TIERS"="T"."CODE_ETABL" AND "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"."N UM_PIECE_INTERNE",'0123456789'))) 15 - access("P"."CODE_ETABL"="T"."TIERS") 16 - filter("DESIGNATION" LIKE 'ANNULATION d''un BON de RECEPTION de TRANSFERT%')
voici ses statisques:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14 with AZ as(select distinct t.NUM_PIECE_INTERNE "PIECE INTERNE",t.NUM_PIECE as NUM_PIECE_E,p.NUM_PIECE as NUM_PIECE_R from achat t left join etablissement e on t.tiers= e.code_etabl inner 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_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' order by t.NUM_PIECE_INTERNE asc) 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_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 t.NUM_PIECE not in (select NUM_PIECE_E from AZ) order by t.NUM_PIECE_INTERNE asc
Merci de m'apporter votre aide car j'ai besoin d'utiliser la première requete du faite qu'il y'a des établissemnt qui possèdent plus de lignes et donc la seconde requete dure a ce niveau. Ou si ya des possibilités d'amélioration avec de la requête. je précise que la requête s’exécute plus vite en jointure interne ou externe droite
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
77
78
79
80
81
82
83
84
85
86
87 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 6xqj632pz0sv5, child number 0 ------------------------------------- with AZ as(select distinct t.NUM_PIECE_INTERNE "PIECE INTERNE",t.NUM_PIECE as NUM_PIECE_E,p.NUM_PIECE as NUM_PIECE_R from achat t left join etablissement e on t.tiers= e.code_etabl inner 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_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' order by t.NUM_PIECE_INTERNE asc) select /*+ GATHER_PLAN_STATISTICS */distinct t.NUM_PIECE_INTER Plan hash value: 716002260 ---------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 200 |00:00:37.50 | 458K| 223K| | | | | 1 | SORT ORDER BY | | 1 | 1 | 200 |00:00:37.50 | 458K| 223K| 33792 | 33792 |30720 (0)| | 2 | HASH UNIQUE | | 1 | 1 | 200 |00:00:37.50 | 458K| 223K| 721K| 721K| 1249K (0)| | 3 | NESTED LOOPS OUTER | | 1 | 1 | 200 |00:02:09.55 | 458K| 223K| | | | | 4 | NESTED LOOPS OUTER | | 1 | 1 | 200 |00:00:07.12 | 207K| 26090 | | | | |* 5 | HASH JOIN ANTI NA | | 1 | 1 | 200 |00:00:07.11 | 207K| 26090 | 752K| 752K| 1181K (0)| |* 6 | HASH JOIN ANTI NA | | 1 | 2 | 765 |00:00:01.24 | 9012 | 8646 | 752K| 752K| 1219K (0)| |* 7 | TABLE ACCESS BY INDEX ROWID | ACHAT | 1 | 234 | 918 |00:00:00.04 | 401 | 45 | | | | |* 8 | INDEX RANGE SCAN | INDEXCODE_ETABLA | 1 | 4925 | 4100 |00:00:00.01 | 11 | 7 | | | | |* 9 | TABLE ACCESS FULL | ACHAT | 1 | 8016 | 2274 |00:00:00.31 | 8611 | 8601 | | | | | 10 | VIEW | | 1 | 1 | 567 |00:00:05.87 | 198K| 17444 | | | | | 11 | SORT ORDER BY | | 1 | 1 | 567 |00:00:05.87 | 198K| 17444 | 43008 | 43008 |38912 (0)| | 12 | HASH UNIQUE | | 1 | 1 | 567 |00:00:05.87 | 198K| 17444 | 776K| 776K| 1223K (0)| |* 13 | HASH JOIN ANTI NA | | 1 | 1 | 567 |00:00:05.86 | 198K| 17444 | 780K| 780K| 1233K (0)| | 14 | NESTED LOOPS | | 1 | | 652 |00:00:04.96 | 189K| 8843 | | | | | 15 | NESTED LOOPS | | 1 | 1 | 400K|00:00:01.91 | 11455 | 8664 | | | | |* 16 | HASH JOIN ANTI NA | | 1 | 1 | 729 |00:00:01.60 | 9168 | 8614 | 797K| 797K| 1243K (0)| |* 17 | TABLE ACCESS BY INDEX ROWID| ACHAT | 1 | 94 | 804 |00:00:01.08 | 557 | 13 | | | | |* 18 | INDEX RANGE SCAN | INDEXTIERSA | 1 | 1983 | 1369 |00:00:00.03 | 6 | 5 | | | | |* 19 | TABLE ACCESS FULL | ACHAT | 1 | 8016 | 11405 |00:00:00.05 | 8611 | 8601 | | | | |* 20 | INDEX RANGE SCAN | INDEXTIERSA | 729 | 518 | 400K|00:00:00.34 | 2287 | 50 | | | | |* 21 | TABLE ACCESS BY INDEX ROWID | ACHAT | 400K| 1 | 652 |00:00:02.35 | 177K| 179 | | | | |* 22 | TABLE ACCESS FULL | ACHAT | 1 | 8016 | 2274 |00:00:00.34 | 8611 | 8601 | | | | | 23 | TABLE ACCESS BY INDEX ROWID | ETABLISSEMENT | 200 | 1 | 190 |00:00:00.01 | 194 | 0 | | | | |* 24 | INDEX UNIQUE SCAN | ETABL_CODE_ETABL | 200 | 1 | 190 |00:00:00.01 | 4 | 0 | | | | | 25 | VIEW | | 200 | 1 | 0 |00:00:30.38 | 251K| 197K| | | | |* 26 | HASH JOIN ANTI NA | | 200 | 1 | 0 |00:00:30.38 | 251K| 197K| 719K| 719K| 363K (0)| |* 27 | TABLE ACCESS BY INDEX ROWID | ACHAT | 200 | 1 | 38 |00:00:00.45 | 53437 | 0 | | | | |* 28 | INDEX RANGE SCAN | INDEXCODE_ETABLA | 200 | 335 | 308K|00:00:00.20 | 21556 | 0 | | | | |* 29 | TABLE ACCESS FULL | ACHAT | 23 | 8016 | 262K|00:00:05.38 | 198K| 197K| | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T"."NUM_PIECE"="NUM_PIECE_E") 6 - access("T"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 7 - filter(("T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%' 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'))) 8 - access("T"."CODE_ETABL"='432') 9 - filter("DESIGNATION" LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') 13 - access("T"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 16 - access("P"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 17 - filter("P"."DESIGNATION" LIKE 'BON de RECEPTION de TRANSFERT%') 18 - access("P"."TIERS"='432') 19 - filter("DESIGNATION" LIKE 'ANNULATION d''un BON de RECEPTION de TRANSFERT%') 20 - access("P"."CODE_ETABL"="T"."TIERS") 21 - filter(("T"."CODE_ETABL"='432' AND "T"."DESIGNATION" LIKE 'BON d'' EXPEDITION de TRANSFERT%' 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') AND TRANSLATE("T"."NUM_PIECE_INTERNE",'0123456789'||"T"."NUM_PIECE_INTERNE",'0123456789')=TRANSLATE("P"."NUM_PIECE_INTERNE",'0123456789'||"P"."NUM_PIE CE_INTERNE",'0123456789'))) 22 - filter("DESIGNATION" LIKE 'ANNULATION d''un BON d'' EXPEDITION de TRANSFERT%') 24 - access("T"."TIERS"="E"."CODE_ETABL") 26 - access("P"."NUM_PIECE_INTERNE"="NUM_PIECE_INTERNE") 27 - filter(("P"."TIERS"="T"."CODE_ETABL" AND "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_PIE CE_INTERNE",'0123456789'))) 28 - access("P"."CODE_ETABL"="T"."TIERS") 29 - filter("DESIGNATION" LIKE 'ANNULATION d''un BON de RECEPTION de TRANSFERT%') 83 rows selected
Partager