optimisation requete SQL

Version imprimable

Voici une requete sur laquelle toutes les tables sont ridiculement petites sauf la table paie2 76 millions de lignes et la table salaries 250000 lignes.

Code:

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
SELECT   
  AGENDA.ANN || lpad(AGENDA.MOI,2,0),
  SERVICES.COD_SOCIETE,
  SERVICES.COD_ETAB,
  SERVICES.COD_SERV,
  SERVICES.LIB_SERV,
  SALARIES.CDMATN,
  SALARIES.NOMSAN,
  sum(decode(PAIE2.PAYE.CDRUB9,'330090',PAIE2.PAYE.MTSR29,'330110', - PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330010',PAIE2.PAYE.MTSR29,'330030', - PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330070',PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330122',PAIE2.PAYE.MTSR29,'330124', - PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'241510',PAIE2.PAYE.MTSR29,'242010',PAIE2.PAYE.MTSR29,'241580', - PAIE2.PAYE.MTSR29,'242140',-PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330090',PAIE2.PAYE.MTSR29,'330110', - PAIE2.PAYE.MTSR29,'330010',PAIE2.PAYE.MTSR29,'330030', - PAIE2.PAYE.MTSR29,'330070',-PAIE2.PAYE.MTSR29,'330122',PAIE2.PAYE.MTSR29,'330124', - PAIE2.PAYE.MTSR29,'241510',PAIE2.PAYE.MTSR29,'242010',PAIE2.PAYE.MTSR29,'241580', - PAIE2.PAYE.MTSR29,'242140',-PAIE2.PAYE.MTSR29,0))
FROM
  SERVICES,
  AGENDA,
  PAIE.CENTRE_COUT,
  SALARIES,
  PAIE2.PAYE
WHERE
  ( PAIE.CENTRE_COUT.COD_SOCIETE=SERVICES.COD_SOCIETE and PAIE.CENTRE_COUT.MCMCU=SERVICES.COD_CC  )
  AND  ( SERVICES.COD_DEP=SALARIES.CDDEPN and SERVICES.COD_ETAB=SALARIES.CDETSN and SERVICES.COD_SERV=SALARIES.CDSECN and SERVICES.COD_SOCIETE=SALARIES.CDSOCN and SERVICES.COD_CC=SALARIES.CTANAN  )
  AND  ( PAIE2.PAYE.CDMAT9=SALARIES.CDMATN AND                                                        PAIE2.PAYE.CDSOC9=SALARIES.CDSOCN  AND 
PAIE2.PAYE.CDETS9=SALARIES.CDETSN  AND                                                        PAIE2.PAYE.CDDEP9=SALARIES.CDDEPN  AND                                                       PAIE2.PAYE.CDSEC9=SALARIES.CDSECN  AND                                        to_number(to_char(PAIE2.PAYE.AADPP9)||lpad(to_char(PAIE2.PAYE.MMDPP9),2,0)||lpad(to_char(PAIE2.PAYE.JJDPP9),2,0)) = SALARIES.DTDPPN  AND to_number(to_char(PAIE2.PAYE.AADPP9)||lpad(to_char(PAIE2.PAYE.MMDPP9),2,0)) = SALARIES.PERATN  )
  AND  ( SERVICES.COD_DEP=PAIE2.PAYE.CDDEP9 and SERVICES.COD_ETAB=PAIE2.PAYE.CDETS9 and SERVICES.COD_SERV=PAIE2.PAYE.CDSEC9 and SERVICES.COD_SOCIETE=PAIE2.PAYE.CDSOC9  )
  AND  ( AGENDA.ANN=PAIE2.PAYE.AAPPA9 and AGENDA.CCCO=PAIE2.PAYE.CDSOC9 and AGENDA.MOI=PAIE2.PAYE.MMPPA9  )
  AND  ( PAIE.CENTRE_COUT.USR_BO='buonaguidi'  )
  AND  (SALARIES.CDSOCN IN ('HBS','SBM','MIR','STM','SMS') )
  AND  (
  AGENDA.ANN || lpad(AGENDA.MOI,2,0)  =  '200607'
  AND  SERVICES.COD_SOCIETE  IN  'STM'
  AND  ( (SALARIES.cdsocn, SALARIES.cdmatn, SALARIES.rrn) in (SELECT c.CDSOCN,c.CDMATN,max(c.rrn)
FROM
  SALARIES C
WHERE   C.PERATN  =  '200607'
  group by c.cdsocn, c.cdmatn)
  )
  )
GROUP BY
  AGENDA.ANN || lpad(AGENDA.MOI,2,0), 
  SERVICES.COD_SOCIETE, 
  SERVICES.COD_ETAB, 
  SERVICES.COD_SERV, 
  SERVICES.LIB_SERV, 
  SALARIES.CDMATN, 
  SALARIES.NOMSAN;

cette requete rame, elle dur 40 minutes, comment l'optimiser ?

Merci

23/08/2007, 09h07
orafrance

nous ne sommes pas devin :roll:

merci de lire et respecter les règles du forum notamment dans la précision d'éléments fondamentaux pour pouvoir t'aider : version, indexes, plan d'exécution et éventuellement événement d'attente (v$session_wait ou tkprof)

pour éliminer le double table scan de salaries

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT   
  SALARIES.CDMATN,
  SALARIES.NOMSAN
FROM
  SALARIES
WHERE
  (SALARIES.cdsocn, SALARIES.cdmatn, SALARIES.rrn) in 
  (
    SELECT c.CDSOCN,c.CDMATN,max(c.rrn) 
    FROM SALARIES C 
    WHERE  C.PERATN  =  '200607' 
    group by c.cdsocn, c.cdmatn
  )
GROUP BY
  SALARIES.CDMATN, 
  SALARIES.NOMSAN;

Code:

1
2
3
4
5
6
7
8
9
10
 
SELECT   
  CDMATN,
  max(NOMSAN) keep (dense_rank last order by RRN)
FROM
  SALARIES
WHERE
  PERATN  =  '200607' 
GROUP BY
  CDMATN;

PS: ce n'est pas une solution, les requêtes ne sont pas 100% équivalentes, c'est juste une piste !

déjà, si c'est possible, supprime :

Code:

1
2
3
4
5
6
7
8
AND (salaries.cdsocn, salaries.cdmatn, salaries.rrn) IN (
                                        SELECT   c.cdsocn, c.cdmatn,
                                                 MAX (c.rrn) 
                                            FROM salaries c
                                           WHERE c.peratn = '200607'
                                        GROUP BY c.cdsocn, c.cdmatn)
              )
     AND salaries.cdsocn IN ('HBS', 'SBM', 'MIR', 'STM', 'SMS')

et remplace salaries par :

Code:

1
2
3
4
5
6
 SELECT   c.cdsocn, c.cdmatn,cdetsn,cddepn,cdsecn
                                                 MAX (c.rrn) rrn
                                            FROM salaries c
                                           WHERE c.peratn = '200607'
                                               AND c.cdsocn IN ('HBS', 'SBM', 'MIR', 'STM', 'SMS')
                                        GROUP BY c.cdsocn, c.cdmatn,cdetsn,cddepn,cdsecn

dans la clause FROM

Edit : si fonctionnellement, le GROUP BY te géne, utilise la fonction analytique comme indiqué par Laurent ;)

23/08/2007, 09h18
orafrance
sans le plan d'exécution c'est pas évident mais ces jointures :
Code:

1 2 3 4 AND services.cod_dep = paie2.paye.cddep9 AND services.cod_etab = paie2.paye.cdets9 AND services.cod_serv = paie2.paye.cdsec9 AND services.cod_societe = paie2.paye.cdsoc9
posent peut-être problème en faisant un NL services-paie2.paye au lieu de salaire-paie2.paye. Comme fonctionnellement ça ne sert à rien (les jointures services-salaire et salaire-paie2.paye étant présentes) tu peux tenter de les supprimer ;)
23/08/2007, 09h20
laurentschneider

Code:

max(...) keep (dense_rank last order by ...)

est fonction d'aggrégation pas une fonction analytique.une fonction analytique, c'est quelque chose comme

Code:

max(...) over ()

ou

Code:

dense_rank() over (order by ...)
23/08/2007, 09h24
orafrance
ha... pour moi une fonction d'aggrégation c'est uniquement :
Code:

1 2 3 SELECT MAX ... GROUP BY
me tromperais-je donc ? :koi:

Edit : Apparemment : http://download.oracle.com/docs/cd/B...s.htm#i1006709

MAX est une fonction analytique lorsqu'elle est utilisé avec un fenêtrage et l'expression que tu proposes est une expression d'aggrégation... c'est bien ça ?

Citation:

Windowing Aggregate Functions

analytic_function([ arguments ])
OVER (analytic_clause)

FIRST/LAST, ce sont des fonctions d'aggrégations qui requièrent la clause KEEP

le plus haut salaire du département (aggrégat)

Code:

1
2
3
4
5
6
7
8
 
SQL> select deptno, max(sal) from emp group by deptno
 
    DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000

l'employé et son salaire comparé au plus haut salaire du département (analytique)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select deptno, ename,sal,max(sal) over (partition by deptno) from emp
 
    DEPTNO ENAME             SAL MAX(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- ---------- -------------------------------
        10 CLARK            2450                            5000
        10 KING             5000                            5000
        10 MILLER           1300                            5000
        20 JONES            2975                            3000
        20 FORD             3000                            3000
        20 ADAMS            1100                            3000
        20 SMITH             800                            3000
        20 SCOTT            3000                            3000
        30 WARD             1250                            2850
        30 TURNER           1500                            2850
        30 ALLEN            1600                            2850
        30 JAMES             950                            2850
        30 BLAKE            2850                            2850
        30 MARTIN           1250                            2850

l'employé le mieux payé et son salaire par département (aggrégat)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select deptno, max(ename) keep (dense_rank last order by sal) from emp
select deptno, max(ename) keep (dense_rank last order by sal) from emp
       *
Error at line 1
ORA-00937: not a single-group group function
 
SQL> select deptno, max(ename) keep (dense_rank last order by sal) from emp group by deptno
 
    DEPTNO MAX(ENAME)
---------- ----------
        10 KING      
        20 SCOTT     
        30 BLAKE

l'erreur ORA-937 montre bien qu'il s'agit d'un aggrégat et non d'une fonction analytique

PS: la clause analytique est toujours précédée de OVER()

23/08/2007, 09h49
orafrance

magnifique démonstration, c'est très clair :)

:merci:

pour ajouter un peu de piment, voici keep et over réunis 8-)

chaque employé (nom et salaire) comparé à l'employé le mieux payé du département (nom et salaire)

Code:

1
2
3
4
5
6
7
8
 
select deptno, 
  ename, 
  sal, 
  max(ename) keep (
    dense_rank last order by sal) over (partition by deptno) maxsal_ename,
  max(sal) over (partition by deptno) maxsal_sal
from emp;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
    DEPTNO ENAME             SAL MAXSAL_ENA MAXSAL_SAL
---------- ---------- ---------- ---------- ----------
        10 CLARK            2450 KING             5000
        10 KING             5000 KING             5000
        10 MILLER           1300 KING             5000
        20 JONES            2975 SCOTT            3000
        20 FORD             3000 SCOTT            3000
        20 ADAMS            1100 SCOTT            3000
        20 SMITH             800 SCOTT            3000
        20 SCOTT            3000 SCOTT            3000
        30 WARD             1250 BLAKE            2850
        30 TURNER           1500 BLAKE            2850
        30 ALLEN            1600 BLAKE            2850
        30 JAMES             950 BLAKE            2850
        30 BLAKE            2850 BLAKE            2850
        30 MARTIN           1250 BLAKE            2850

23/08/2007, 11h21
orafrance

c'est trop beau :king:

avec tout ça, la discussion initiale n'avance pas :koi:
23/08/2007, 15h22
lepierot

optimisation requete SQL

merci pour vos reponses.
J'essaierais demain si j'ai le temps de vous fournir un plan d'execution.
j'ai oublié de vous dire que cette requete etait quasi immediate en 8.1.7 c'est depuis que j'ai migré en 10.2.0.3 que ca deconne.

j'ai alors fait un alter session set optimizer_features_enable='8.1.7' et al ca refonctionne correctement.
23/08/2007, 15h26
lepierot

Plan execution

Citation:

Plan d'exécution
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 181 |14 |
| 1 | HASH GROUP BY | | 1 | 181 |14 |
| 2 | HASH JOIN SEMI | | 1 | 181 |13 |
| 3 | TABLE ACCESS BY INDEX ROWID | PAYE | 1 | 38 |3 |
| 4 | NESTED LOOPS | | 1 | 158 |10 |
| 5 | NESTED LOOPS | | 1 | 120 |7 |
| 6 | HASH JOIN | | 1 | 66 |5 |
| 7 | MERGE JOIN CARTESIAN | | 110 | 2640 |2 |
| 8 | INDEX RANGE SCAN | PK_AGENDA | 1 | 8 |1 |
| 9 | BUFFER SORT | | 114 | 1824 |1 |
| 10 | INDEX RANGE SCAN | FK_SOC | 114 | 1824 |1 |
| 11 | TABLE ACCESS BY INDEX ROWID| SERVICES | 23 | 966 |2 |
| 12 | INDEX RANGE SCAN | SERVICES_OUTL01 | 23 | |1 |
| 13 | TABLE ACCESS BY INDEX ROWID | SALARIES | 1 | 54 |2 |
| 14 | INDEX RANGE SCAN | FK_SALSERV | 1 | |1 |
| 15 | INDEX RANGE SCAN | FK_TYE_PART | 1 | |2 |
| 16 | VIEW | VW_NSO_1 | 6 | 138 |3 |
| 17 | HASH GROUP BY | | 6 | 120 |3 |
| 18 | TABLE ACCESS BY INDEX ROWID | SALARIES | 181 | 3620 |2 |
| 19 | INDEX RANGE SCAN | SALARIE_OUTL01 | 3 | |1 |
---------------------------------------------------------------
23/08/2007, 15h34
orafrance

MERGE JOIN CARTESIAN :koi:

Dis donc, tu pourrais pas le formater un peu mieux ? Une impression écran de TOAD par exemple ce serait pas mal :)

Et elle est passé où la 2° table paye... t'es sûr que c'est le bon plan ?
24/08/2007, 11h11
lepierot

optimiseur 8.1.7

En fait cette requete s'executait parfaitement en 8I. C'est avec la 10G (10.2.0.3) que ca a commencer a deconner. Pour l'instant j'ai résolu mon probleme en forcant l'optimieur 8.1.7 par la commande :

alter session set optimizer_features_enable='8.1.7'

j'ai mis ca dans un trigger de logon et ca marche.

D'autre part, j'ai supprimer les statistiques sur la grosse table Paie et je les ai laissée sur toutes les autres.

Ensuite j'ai aussi supprimer presque toutes les fonctions.

Voila et merci à tous.

cette discussion m'aura permis de comprendre que je dois absolument suivre une formation d'optimisation SQL.
24/08/2007, 11h28
orafrance

une optimisation bien dégueu sans aucun doute :sm:

Citation:
Envoyé par laurentschneider
pour ajouter un peu de piment, voici keep et over réunis 8-)

chaque employé (nom et salaire) comparé à l'employé le mieux payé du département (nom et salaire)
Code:

1 2 3 4 5 6 7 8 select deptno, ename, sal, max(ename) keep ( dense_rank last order by sal) over (partition by deptno) maxsal_ename, max(sal) over (partition by deptno) maxsal_sal from emp;
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 DEPTNO ENAME SAL MAXSAL_ENA MAXSAL_SAL ---------- ---------- ---------- ---------- ---------- 10 CLARK 2450 KING 5000 10 KING 5000 KING 5000 10 MILLER 1300 KING 5000 20 JONES 2975 SCOTT 3000 20 FORD 3000 SCOTT 3000 20 ADAMS 1100 SCOTT 3000 20 SMITH 800 SCOTT 3000 20 SCOTT 3000 SCOTT 3000 30 WARD 1250 BLAKE 2850 30 TURNER 1500 BLAKE 2850 30 ALLEN 1600 BLAKE 2850 30 JAMES 950 BLAKE 2850 30 BLAKE 2850 BLAKE 2850 30 MARTIN 1250 BLAKE 2850
J'ai une petite remarque : l'utilisation de max(ename) keep (
dense_rank peut nous induire en erreurs à cause des doublons; pour le département 20 il y a scott et ford qui ont le meilleur salaire mais le max(ename) keep (
dense_rank last order by sal) over (partition by deptno) donne seulement SCOTT, donc en cas d'égalité il prend le max(ename) ???????

13/09/2007, 14h46
laurentschneider

Citation:

Envoyé par salim11

donc en cas d'égalité il prend le max(ename) ?

oui
13/09/2007, 15h01
salim11

Salut,

La conclusion :
c'est mieux alors d'utiliser la fonction analytique dense_rank () over().
13/09/2007, 15h41
laurentschneider

Citation:

Envoyé par salim11

Salut,

La conclusion :
c'est mieux alors d'utiliser la fonction analytique dense_rank () over().

Bof, ce n'est ni mieux ni moins bien, c'est juste différent. Si tu ne veux qu'une seule ligne alors KEEP est beaucoup plus performant. Si tu veux retourner tous les employés avec le même salaire, tu peux employer DENSE_RANK, mais attention, tu devras utiliser une sous-requête en plus (where r=1)

Salut,

Complètement d'accord avec toi laurent, utiliser une subquery c'est génant mais ...

Exemple :
Code:

1 2 3 4 5 6 SELECT ename, sal, deptno FROM (SELECT ename, sal, deptno, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) r FROM emp_test) WHERE r = 1