optimisation de requête SQL [Résolu]

[millien]

Bonjour,
Je recherche un moyen d'optimiser cette requête qui demande plusieurs minutes pour s'exécuter.
L'objectif est de récupérer l'occurence la plus récente dans une période donnée (ici, janvier 2007).
d'avance merci.

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 SELECT  CUF_C_UF, mat_c_agent
FROM taff b
WHERE  
AFF_D_DEBUT <= to_date('01/01/2007','DD/MM/YYYY')
AND AFF_D_FIN>=LAST_DAY(to_date('01/01/2007','DD/MM/YYYY'))
AND EHR_C_EH='21066'
AND AFF_D_DEBUT=
(
    SELECT max(c.AFF_D_DEBUT) 
    FROM taff c 
    WHERE c.MAT_C_AGENT=b.MAT_C_AGENT 
    AND c.AFF_D_DEBUT<=to_date('01/01/2007','DD/MM/YYYY')
)
 
CREATE TABLE TAFF ( 
  EHR_C_EH      CHAR (5)      NOT NULL, 
  MAT_C_AGENT   VARCHAR2 (14)  NOT NULL, 
  AFF_D_DEBUT   DATE          NOT NULL, 
  AFF_C_NATURE  CHAR (3)      NOT NULL, 
  EHR_C_EHUF    CHAR (5)      NOT NULL, 
  CUF_C_UF      VARCHAR2 (17)  NOT NULL, 
  AFF_D_APPAR   DATE          NOT NULL, 
  AFF_D_DISPAR  DATE          NOT NULL, 
  AFF_D_FIN     DATE          NOT NULL, 
  AFF_N_POINTS  NUMBER (6), 
  AFF_N_NUMER   NUMBER (6), 
  AFF_N_DENOM   NUMBER (6), 
  CONSTRAINT PK_TAFF
  PRIMARY KEY ( EHR_C_EH, MAT_C_AGENT, AFF_D_DEBUT, AFF_C_NATURE, EHR_C_EHUF, CUF_C_UF, AFF_D_APPAR ) 
    USING INDEX 
     TABLESPACE AGEINDEX PCTFREE 10
     STORAGE ( INITIAL 90232K NEXT 4632K PCTINCREASE 0 ))
   TABLESPACE AGEDATA NOLOGGING 
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE ( 
   INITIAL 78499840
   NEXT 1331200
   MINEXTENTS 1
   MAXEXTENTS 99
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE; 
 
ALTER TABLE PH7.TAFF ADD  CONSTRAINT FK_TAFF_AFF_CUF_TCUF
 FOREIGN KEY (EHR_C_EH, CUF_C_UF) 
  REFERENCES PH7.TCUF (EHR_C_EH, CUF_C_UF) ;
 
ALTER TABLE PH7.TAFF ADD  CONSTRAINT FK_TAFF_AFF_MAT_TMAT
 FOREIGN KEY (EHR_C_EHUF, MAT_C_AGENT) 
  REFERENCES PH7.TMAT (EHR_C_EH, MAT_C_AGENT) ;
 
CREATE INDEX AFF_CUF_FK ON 
  TAFF(EHR_C_EH, CUF_C_UF) 
  TABLESPACE AGEINDEX PCTFREE 10  STORAGE(INITIAL 31744000 NEXT 4706304 PCTINCREASE 10 ) 
; 
 
CREATE INDEX AFF_MAT_FK ON 
  TAFF(EHR_C_EHUF, MAT_C_AGENT) 
  TABLESPACE AGEINDEX PCTFREE 10  STORAGE(INITIAL 34816000 NEXT 4943872 PCTINCREASE 10 ) 
;

[mnitu]

Peut tu ajouter le plan d'exécution de la requête, éventuellement faire un tkprof ?

[Michel SALAIS]

Envoyé par millien

Bonjour,
Je recherche un moyen d'optimiser cette requête qui demande plusieurs minutes pour s'exécuter.
L'objectif est de récupérer l'occurence la plus récente dans une période donnée (ici, janvier 2007).
d'avance merci.

Code :

Il y a beaucoup d'information qui manque notamment la version, le plan d'exécution, la volumetrie et si les statistiques sont collectées Certains index sont probablement à créer. Mais mon nez me dit que la requête ci-après va se comporter mieux. Elle marche à partir d'Oracle 9i et elle peut être réécrite différemment pour les versions précédentes

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH t AS
(
  SELECT mat_c_agent, max(AFF_D_DEBUT) max_aff_d_debut
  FROM taff
  WHERE AFF_D_DEBUT<=to_date('01/01/2007','DD/MM/YYYY')
  GROUP BY mat_c_agent
)
SELECT  CUF_C_UF, mat_c_agent
FROM taff t1, t
WHERE  
  t1.AFF_D_DEBUT <= to_date('01/01/2007','DD/MM/YYYY')
  AND t1.AFF_D_FIN>=LAST_DAY(to_date('01/01/2007','DD/MM/YYYY'))
  AND t1.EHR_C_EH='21066'
  AND t1.AFF_D_DEBUT= t.max_aff_d_debut
  AND t1.mat_c_agent = t.mat_c_agent

[Yorglaa]

bonjour,
je sais que ça joue peu sur les perf, mais au lieu de faire

Code :

AND t1.AFF_D_FIN>=LAST_DAY(to_date('01/01/2007','DD/MM/YYYY'))

il serais plus simple de faire

Code :

AND t1.AFF_D_FIN>=to_date('31/01/2007','DD/MM/YYYY')

un last_day sur une valeur "en dur" c'est pas très bon...puisque la valeur calculée du last_day est connue d'avance.

De plus le fait d'avoir une clause sur une fonction peut empêcher l'optimiseur d'utiliser un index...

[millien]

Je suis passée par une table temporaire parce que je suis en version 8 d'oracle.
Ca marche bien merci beaucoup