Améliorer une requête complexe

**cyberyan** · 03/09/2008, 11h52

Bonjour

J'ai une requete de ce style

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT 1,TAB1.CHAMP1
FROM TAB1
WHERE TAB1.CHAMP2= 1
UNION
SELECT 2,TAB2.CHAMP1
FROM TAB2
WHERE TAB2.CHAMP2 IN (SELECT TAB1.CHAMP1 FROM TAB1 WHERE TAB1.COMP2=1)
UNION
SELECT 3,TAB3.CHAMP1
FROM TAB3
WHERE TAB3.CHAMP2 IN (SELECT TAB1.CHAMP1 FROM TAB1 WHERE TAB1.COMP2=1)

Quelqu'un peut-il me dire s'il y a moyen d'améliorer cette requête

Merci d'avance

Cordialement

**McM** · 03/09/2008, 12h26

Peut être. Est ce que les UNION sont obligatoires(cela fait un distinct => générateur de tri).

Voir ensuite si en remplaçant les IN par des EXISTS c'est mieux, voire à faire une jointure entre les tables.

**cyberyan** · 03/09/2008, 12h41

Voila l'original de la requete construite en php

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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
SELECT 
  'f' TYPELIGNE,
  imp.cdprj CDPRJ, 
  imp.CDWPK CDWPK, 
  plg.lidep LIDEP, 
  plg.cdmat CDMAT,
  plg.cdposte CDPOSTE,
  PLG.TYRESS TYRESS,
  plq.dtmoisco DTMOISCO,
  PLG.TYLIGNE TYLIGNE,
  DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2', 
  'Clos', '3', 'Stand By', 'Non valide') INVALSIM, 
  DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide') INVALWPM, 
  fixed(sum(plq.qtprev),6,2) QtPREV 
FROM FF_PLG PLG, FD_IMP IMP, FF_PLQ PLQ, FD_WPK WPK, FF_HWP HWP, FD_TRG TRG
Where 
  PLG.CDORG = '".$VALEUR_CDORG."' 
  AND IMP.CDORG = PLG.CDORG  
  AND IMP.TYIMPU = PLG.TYIMPU 
  AND IMP.CDIMPU = PLG.CDIMPU 
  AND PLQ.CDORG = PLG.CDORG 
  AND PLQ.TYIMPU = PLG.TYIMPU 
  AND PLQ.CDIMPU = PLG.CDIMPU 
  AND PLQ.NULIGNE = PLG.NULIGNE 
  AND PLQ.DTMOISCO > ".$MOISCOD." 
  AND WPK.CDPRJ = IMP.CDPRJ  
  AND WPK.CDWPK = IMP.CDWPK 
  AND HWP.CDPRJ (+) = WPK.CDPRJ 
  AND HWP.CDWPK (+) = WPK.CDWPK 
  AND (HWP.CDPRJ IS NULL 
  OR HWP.DTMOISDHD = ".$MOISCOD." ) 
  AND TRG.CDORG (+) = PLG.CDORG 
  AND TRG.CDPOSTE (+) = PLG.CDPOSTE  
  ".$WHERE_RESTRICTION."
GROUP BY 
  IMP.CDPRJ, 
  IMP.CDWPK, 
  PLG.LIDEP, 
  PLG.CDMAT, 
  PLG.CDPOSTE, 
  PLG.TYRESS, 
  PLG.TYLIGNE, 
  PLQ.DTMOISCO, 
  DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2', 
  'Clos', '3', 'Stand By', 'Non valide'), 
  DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide')
UNION
SELECT 
  'd' TYPELIGNE,
  NVL(TRIM(IMP.CDPRJ),DECODE(TRIM(IMP.CDWPK),NULL,'ABSENCES',NULL)) CDPRJ,
  NVL(TRIM(IMP.CDWPK),DECODE(TRIM(IMP.CDPRJ),NULL,'ABSENCES',NULL)) CDWPK, 
  max(DWL.LIMATC) as LIMATC ,
  DWL.CDMAT,
  NULL,
  NULL, 
  DWL.DTMOISR , 
  NULL,
  DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2', 
  'Clos', '3', 'Stand By', 'Non valide') INVALSIM, 
  DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide') INVALWPM, 
  sum(DWL.QTHDEPCT )/150 as QTHDEPCT 					
FROM ff_DWL DWL,FD_IMP IMP, FF_HWP HWP
WHERE 
  DWL.CDORG='".$VALEUR_CDORG."' 							
  AND DWL.CDMAT IN
    (SELECT PLG.CDMAT 
      FROM  FF_PLG PLG, FD_IMP IMP, FF_PLQ PLQ, FD_WPK WPK, FF_HWP HWP, FD_TRG TRG
      Where 
        PLG.CDORG = '".$VALEUR_CDORG."' 
        AND IMP.CDORG = PLG.CDORG  
        AND IMP.TYIMPU = PLG.TYIMPU 
        AND IMP.CDIMPU = PLG.CDIMPU 
        AND PLQ.CDORG = PLG.CDORG 
        AND PLQ.TYIMPU = PLG.TYIMPU 
        AND PLQ.CDIMPU = PLG.CDIMPU 
        AND PLQ.NULIGNE = PLG.NULIGNE 
        AND PLQ.DTMOISCO > ".$MOISCOD." 
        AND WPK.CDPRJ = IMP.CDPRJ  
        AND WPK.CDWPK = IMP.CDWPK 
        AND HWP.CDPRJ (+) = WPK.CDPRJ 
        AND HWP.CDWPK (+) = WPK.CDWPK 
        AND (HWP.CDPRJ IS NULL 
        OR HWP.DTMOISDHD = ".$MOISCOD." ) 
        AND TRG.CDORG (+) = PLG.CDORG 
        AND TRG.CDPOSTE (+) = PLG.CDPOSTE  
        ".$WHERE_RESTRICTION.")						
  AND DWL.DTMOISR >= ".$MOISDEBDEP." 	 							
  AND DWL.DTMOISR <= ".$MOISFINDEP."
  AND IMP.CDORG = DWL.CDORG
  AND IMP.CDIMPU = DWL.CDIMPU
  AND IMP.CDPRJ = HWP.CDPRJ (+) 
  AND IMP.CDWPK = HWP.CDWPK (+) 
  AND (HWP.CDPRJ IS NULL OR HWP.DTMOISDHD = ".$MOISCOD." ) 
group by NVL(TRIM(IMP.CDPRJ),DECODE(TRIM(IMP.CDWPK),NULL,'ABSENCES',NULL)),
  NVL(TRIM(IMP.CDWPK),DECODE(TRIM(IMP.CDPRJ),NULL,'ABSENCES',NULL)),DWL.CDMAT,DWL.DTMOISR,
  DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2', 
  'Clos', '3', 'Stand By', 'Non valide'), 
  DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide') 
UNION
SELECT 
  'c' TYPELIGNE,
  'CONGES' CDPRJ, 
  'CONGES' CDWPK, 
  MAT.CDTITRE || ' ' || MAT.NMPMAT || ' ' || MAT.NMMAT LIDEP, 
  rec.cdmat CDMAT,
  NULL,
  NULL, 
  rec.dtmois DTMOISCO, 
  NULL, 
  NULL, 
  NULL, 
  fixed(sum(rec.qtmois),5,3)/150 QtPREV 
FROM FD_REC REC, HD_MAT MAT 
WHERE 
  REC.CDORG = '".$VALEUR_CDORG."' 
  AND REC.CDMAT IN 
    (SELECT PLG.CDMAT 
      FROM  FF_PLG PLG, FD_IMP IMP, FF_PLQ PLQ, FD_WPK WPK, FF_HWP HWP, FD_TRG TRG
      Where 
        PLG.CDORG = '".$VALEUR_CDORG."' 
        AND IMP.CDORG = PLG.CDORG  
        AND IMP.TYIMPU = PLG.TYIMPU 
        AND IMP.CDIMPU = PLG.CDIMPU 
        AND PLQ.CDORG = PLG.CDORG 
        AND PLQ.TYIMPU = PLG.TYIMPU 
        AND PLQ.CDIMPU = PLG.CDIMPU 
        AND PLQ.NULIGNE = PLG.NULIGNE 
        AND PLQ.DTMOISCO > ".$MOISCOD." 
        AND WPK.CDPRJ = IMP.CDPRJ  
        AND WPK.CDWPK = IMP.CDWPK 
        AND HWP.CDPRJ (+) = WPK.CDPRJ 
        AND HWP.CDWPK (+) = WPK.CDWPK 
        AND (HWP.CDPRJ IS NULL 
        OR HWP.DTMOISDHD = ".$MOISCOD." ) 
        AND TRG.CDORG (+) = PLG.CDORG 
        AND TRG.CDPOSTE (+) = PLG.CDPOSTE  
        ".$WHERE_RESTRICTION.")						
  AND REC.DTMOIS >= ".$MOISDEBUTAFFICHAGE." 
  and MAT.CDORG = REC.CDORG 
  AND MAT.CDMAT = REC.CDMAT 
GROUP BY REC.CDMAT, REC.DTMOIS, MAT.CDTITRE || ' ' || MAT.NMPMAT || ' ' || MAT.NMMAT
order by 5,2,3,6,1 desc

les deuxiemes et troisiemes blocks doivent etre récupérés à partir de données du premier

peut etre une idée ??

sinon passé par une table sur procedure pipelined
avec des selects independants et gestion de collection ne
serait il pas mieux ??

cordialement

**mnitu** · 03/09/2008, 22h13

Il faut une trace sql et un tkprof.

**orafrance** · 03/09/2008, 23h08

au moins le plan d'exécution et la version d'Oracle

**McM** · 04/09/2008, 09h54

Visiblement le UNION peut être remplacé par un UNION ALL vu que c'est groupé au sein de chaque requête et que les typeligne sont différents.

Ensuite, tu peux utiliser la clause WITH pour la requête qui se répète.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
WITH toto AS ( SELECT 21 AS a FROM dual)
SELECT 1 FROM DUAL 
WHERE 21 =  (SELECT a FROM toto)
UNION ALL
SELECT a FROM toto

**murgen23** · 04/09/2008, 12h10

La clause 'with' n'est elle pas purement cosmetique, ou bien elle fournit un reel avantage temps quand un sub select est repeté plusieurs fois ?

**McM** · 04/09/2008, 12h49

J'avais lu un post qui disait que c'était mieux (mais je pense que ça dépend de l'optimiseur).

**dragon74** · 04/09/2008, 13h43

La doc d'Oracle semble dier que dans ce cas-ci, tu pourrais gagner en performance si ton query dans le 'with' ne ramène pas des millions de lignes.

"The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table."

**mnitu** · 04/09/2008, 17h34

Envoyé par dragon74

La doc d'Oracle semble dier que dans ce cas-ci, tu pourrais gagner en performance si ton query dans le 'with' ne ramène pas des millions de lignes.

"The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table."

Peux tu nous expliquer comment à partir de cette information t'a tire cette conclusion ?

Améliorer une requête complexe

PL/SQL Oracle

Discussions similaires

Partager

Partager