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 : 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
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