creation d'un index

**breizh76** · 10/08/2009, 14h19

Je souhaiterais optimiser le temps d'exécution d'une requete qui contient un <nom_colonne> in not null

La semaine dernière, j'ai regardé le plan d'execution de la requête et j'ai detecté un full scan. J'ai donc mis en place un index sur plusieurs colonnes (celles listées dans la requête). Les statistiques de la requête ont montrés une net amélioration

avant:

1185 recursive calls
30 db block gets
123866 consistent gets
129050 physical reads
0 redo size
3933 bytes sent via SQL*Net to client
722 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
59 sorts (memory)
1 sorts (disk)
26 rows processed

maintenant:

595 recursive calls
0 db block gets
59343 consistent gets
59090 physical reads
260 redo size
1652 bytes sent via SQL*Net to client
653 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

Mais je trouve le consistent gets et le physical read encore trop elevés.

Puis je refaire l'index afin de mieux prendre en compte le "is not null" ???

Merci d'avance

**breizh76** · 10/08/2009, 15h02

Dans le plan d'execution, j'obtiens:

29 18 INDEX (FAST FULL SCAN) OF 'IDX' (UNIQUE)
(Cost=4 Card=16386523 Bytes=245797845)

**pachot** · 10/08/2009, 15h17

Attention, tes requêtes ne ramènent pas le même résultat:

avant: 26 rows processed
maintenant: 3 rows processed

**breizh76** · 10/08/2009, 15h20

La création d'un index peut changer le résultat de la requete ????

**breizh76** · 10/08/2009, 15h49

Non C'est bon, j'ai supprimé les index mise en place, relancé la commande et re-mis en place les index. Cela me retourne toujours 3 lignes. Je ne suis pas le seul à travailler sur la base. Je vous rassure c'est une base de dev

Mais faut pas me faire peur comme ca

.

Voici le resultat obtenu:

avant:

Statistics
----------------------------------------------------------
595 recursive calls
0 db block gets
185302 consistent gets
184517 physical reads
0 redo size
1652 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

apres:
Statistics
----------------------------------------------------------
595 recursive calls
0 db block gets
51759 consistent gets
51533 physical reads
0 redo size
1652 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

Voici le FULL SCAN:

INDEX (FAST FULL SCAN) OF 'IDX' (UNIQUE) (Cost=4 Card=45096 Bytes=2344992)

**pachot** · 10/08/2009, 15h49

non. Est-tu sur que c'est la même requête, les mêmes données et les bonnes stats ?

**breizh76** · 10/08/2009, 16h01

Oui C'est la même requete, (Copier/Coller), les mêmes données (même base, on est que 2 à travailler dessus, en tous cas aujourd'hui).

Et les bonnes stats ???? C'est à dire ??

je fais un set autotrace on avant d'executer la requete.

Le résultats avec les 26 lignes datait de la semaine dernière au matin.

Les stats vous semblent suspect ??

**pachot** · 10/08/2009, 16h09

Ok pour les stats maintenant.
Pour le reste, sans connaitre la requête ni les indexes, ni le plan d'exécution total, je n'ai pas de conseil.
Cordialement,
Franck.

**breizh76** · 10/08/2009, 16h18

Voici le;plan d'execution...

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25370 Card=67892 Byt
es=13171048)

1 0 NESTED LOOPS (OUTER) (Cost=17 Card=1 Bytes=65)
2 1 NESTED LOOPS (OUTER) (Cost=10 Card=1 Bytes=39)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (Cost=3 C
ard=1 Bytes=13)

4 3 INDEX (UNIQUE SCAN) OF 'PK_POL_IDENT' (UNIQUE) (Cost
=2 Card=1)

5 2 VIEW (Cost=7 Card=1 Bytes=26)
6 5 SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7
Card=2 Bytes=16)

8 7 INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON
-UNIQUE) (Cost=3 Card=6)

9 1 VIEW (Cost=7 Card=1 Bytes=26)
10 9 SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7 C
ard=2 Bytes=16)

12 11 INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON-U
NIQUE) (Cost=3 Card=6)

13 0 SORT (UNIQUE) (Cost=24388 Card=67892 Bytes=13171048)
14 13 FILTER
15 14 NESTED LOOPS (Cost=23406 Card=67892 Bytes=13171048)
16 15 HASH JOIN (Cost=23406 Card=67892 Bytes=12899480)
17 16 TABLE ACCESS (FULL) OF 'T_POL_ETAT' (Cost=7 Card=4
2 Bytes=1512)

18 16 HASH JOIN (OUTER) (Cost=23396 Card=67901 Bytes=104
56754)

19 18 HASH JOIN (Cost=17883 Card=47838 Bytes=6649482)
20 19 NESTED LOOPS (Cost=6 Card=45096 Bytes=3742968)
21 20 NESTED LOOPS (Cost=2 Card=1 Bytes=31)
22 21 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRODUI
TASS' (Cost=1 Card=1 Bytes=16)

23 22 INDEX (UNIQUE SCAN) OF 'PK_PAS_IDENT' (U
NIQUE)

24 21 TABLE ACCESS (BY INDEX ROWID) OF 'F_BRANCH
EASS' (Cost=1 Card=1 Bytes=15)

25 24 INDEX (UNIQUE SCAN) OF 'PK_BAS_IDENT' (U
NIQUE)

26 20 INDEX (FAST FULL SCAN) OF 'IDX_ESSAI' (UNIQU
E) (Cost=4 Card=45096 Bytes=2344992)

27 19 TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (
Cost=17534 Card=348329 Bytes=19506424)

28 27 INDEX (RANGE SCAN) OF 'IND_POL_PTRPASID' (NO
N-UNIQUE) (Cost=505 Card=7)

29 18 INDEX (FAST FULL SCAN) OF 'IDX_ESSAI2' (UNIQUE)
(Cost=4 Card=16386523 Bytes=245797845)

30 15 INDEX (UNIQUE SCAN) OF 'PK_IN_IDENT' (UNIQUE)
31 14 SORT (AGGREGATE)
32 31 FIRST ROW (Cost=2 Card=1 Bytes=15)
33 32 INDEX (RANGE SCAN (MIN/MAX)) OF 'IDX_DIEGO2' (UNIQ
UE) (Cost=2 Card=18581147)

la requête:

SELECT /* POLICE_RECHERCHE - M_SQL_SELECT_LISTE_POLICE2 */ distinct laPolice.POL_IDENT POL_IDENT, laPolice.POL_NUMPOL POL_NUMPOL, laPolice.POL_NUM_DOSSIER POL_NUM_DOSSIER, laPolice.POL_CODETAT POL_CODETAT, laPolice.POL_DATDEB POL_DATDEB, DECODE(PAS_CODE_PRODUIT, 'MAP', (SELECT DECODE(polices.POL_CODETAT, 0,'PRE-CONTRAT',96,'CONTRAT SANS EFFET', 1,DECODE(nvl(PRIMEP.NBP,0),1,DECODE(nvl(PRIMEA.NBA,0),0,'DEVIS','DEVIS AVENANT'), 'EN COURS')) ETAT_CONTRAT FROM F_POLICES polices, (SELECT count(PRM_IDENT) NBA , PRM_PTRPOLIDENT FROM F_PRIME WHERE PRM_TYPE = 'A' GROUP BY PRM_PTRPOLIDENT) PRIMEA, (SELECT count(PRM_IDENT) NBP, PRM_PTRPOLIDENT FROM F_PRIME WHERE PRM_TYPE = 'P' GROUP BY PRM_PTRPOLIDENT) PRIMEP WHERE PRIMEA.PRM_PTRPOLIDENT(+) = polices.POL_IDENT AND PRIMEP.PRM_PTRPOLIDENT(+) = polices.POL_IDENT AND polices.POL_PTRPASID = 1254 AND polices.pol_ident = laPolice.POL_IDENT), UPPER(TPE_LIBELLE)) TPE_LIBELLE , CL_CODPOST,CL_VILLE,CL_CODE,CL_NOMAPPEL,BAS_CODE_BRANCHE,PAS_CODE_PRODUIT, '' SOR_CODE_PRODUCT FROM F_POLICES laPolice, F_CLIENTS, F_PRODUITASS, F_BRANCHEASS, F_SIT_OBJET_RISQUE, T_POL_ETAT, F_INTERMEDIAIRE WHERE SUBSTR(CL_CODPOST, 1 , 2) = '82' AND PAS_IDENT=204 AND laPolice.POL_PTRCLID=CL_IDENT AND PAS_IDENT=laPolice.POL_PTRPASID AND BAS_IDENT=PAS_PTRBASID AND ((F_SIT_OBJET_RISQUE.SOR_IDENT = (select min(SOR_IDENT) from F_SIT_OBJET_RISQUE s2 WHERE s2.SOR_PTRPOLID=POL_IDENT AND s2.SOR_PTRSORSUIVID=0)) OR (F_SIT_OBJET_RISQUE.SOR_IDENT is null)) AND SOR_PTRPOLID(+) = laPolice.POL_IDENT AND SOR_PTRSORSUIVID(+)=0 AND TPE_IDENT = laPolice.POL_PTRTPEIDENT AND (NVL(laPolice.POL_OPTION,'-') not in ('ACCUEIL','LITIGE','COLLECTIF') ) AND laPolice.POL_PTRINID = IN_IDENT ORDER BY CL_NOMAPPEL

**breizh76** · 10/08/2009, 16h22

Voici les index mis en place:

CREATE UNIQUE INDEX "IDX_ESSAI2

ON "F_SIT_OBJET_RISQUE" ("SOR_PTRSORSUIVID",

"SOR_PTRPOLID", "SOR_IDENT");

CREATE UNIQUE INDEX IDX_ESSAI"

ON "F_CLIENTS" ("CL_IDENT", "CL_CODPOST",

"CL_VILLE", "CL_CODE", "CL_NOMAPPEL");

**Z3phur** · 10/08/2009, 17h20

Envoyé par breizh76

Voici le;plan d'execution...

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25370 Card=67892 Byt
es=13171048)

1 0 NESTED LOOPS (OUTER) (Cost=17 Card=1 Bytes=65)
2 1 NESTED LOOPS (OUTER) (Cost=10 Card=1 Bytes=39)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (Cost=3 C
ard=1 Bytes=13)

4 3 INDEX (UNIQUE SCAN) OF 'PK_POL_IDENT' (UNIQUE) (Cost
=2 Card=1)

5 2 VIEW (Cost=7 Card=1 Bytes=26)
6 5 SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7
Card=2 Bytes=16)

8 7 INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON
-UNIQUE) (Cost=3 Card=6)

9 1 VIEW (Cost=7 Card=1 Bytes=26)
10 9 SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7 C
ard=2 Bytes=16)

12 11 INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON-U
NIQUE) (Cost=3 Card=6)

13 0 SORT (UNIQUE) (Cost=24388 Card=67892 Bytes=13171048)
14 13 FILTER
15 14 NESTED LOOPS (Cost=23406 Card=67892 Bytes=13171048)
16 15 HASH JOIN (Cost=23406 Card=67892 Bytes=12899480)
17 16 TABLE ACCESS (FULL) OF 'T_POL_ETAT' (Cost=7 Card=4
2 Bytes=1512)

18 16 HASH JOIN (OUTER) (Cost=23396 Card=67901 Bytes=104
56754)

19 18 HASH JOIN (Cost=17883 Card=47838 Bytes=6649482)
20 19 NESTED LOOPS (Cost=6 Card=45096 Bytes=3742968)
21 20 NESTED LOOPS (Cost=2 Card=1 Bytes=31)
22 21 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRODUI
TASS' (Cost=1 Card=1 Bytes=16)

23 22 INDEX (UNIQUE SCAN) OF 'PK_PAS_IDENT' (U
NIQUE)

24 21 TABLE ACCESS (BY INDEX ROWID) OF 'F_BRANCH
EASS' (Cost=1 Card=1 Bytes=15)

25 24 INDEX (UNIQUE SCAN) OF 'PK_BAS_IDENT' (U
NIQUE)

26 20 INDEX (FAST FULL SCAN) OF 'IDX_ESSAI' (UNIQU
E) (Cost=4 Card=45096 Bytes=2344992)

27 19 TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (
Cost=17534 Card=348329 Bytes=19506424)

28 27 INDEX (RANGE SCAN) OF 'IND_POL_PTRPASID' (NO
N-UNIQUE) (Cost=505 Card=7)

29 18 INDEX (FAST FULL SCAN) OF 'IDX_ESSAI2' (UNIQUE)
(Cost=4 Card=16386523 Bytes=245797845)

30 15 INDEX (UNIQUE SCAN) OF 'PK_IN_IDENT' (UNIQUE)
31 14 SORT (AGGREGATE)
32 31 FIRST ROW (Cost=2 Card=1 Bytes=15)
33 32 INDEX (RANGE SCAN (MIN/MAX)) OF 'IDX_DIEGO2' (UNIQ
UE) (Cost=2 Card=18581147)

la requête:

SELECT /* POLICE_RECHERCHE - M_SQL_SELECT_LISTE_POLICE2 */ distinct laPolice.POL_IDENT POL_IDENT, laPolice.POL_NUMPOL POL_NUMPOL, laPolice.POL_NUM_DOSSIER POL_NUM_DOSSIER, laPolice.POL_CODETAT POL_CODETAT, laPolice.POL_DATDEB POL_DATDEB, DECODE(PAS_CODE_PRODUIT, 'MAP', (SELECT DECODE(polices.POL_CODETAT, 0,'PRE-CONTRAT',96,'CONTRAT SANS EFFET', 1,DECODE(nvl(PRIMEP.NBP,0),1,DECODE(nvl(PRIMEA.NBA,0),0,'DEVIS','DEVIS AVENANT'), 'EN COURS')) ETAT_CONTRAT FROM F_POLICES polices, (SELECT count(PRM_IDENT) NBA , PRM_PTRPOLIDENT FROM F_PRIME WHERE PRM_TYPE = 'A' GROUP BY PRM_PTRPOLIDENT) PRIMEA, (SELECT count(PRM_IDENT) NBP, PRM_PTRPOLIDENT FROM F_PRIME WHERE PRM_TYPE = 'P' GROUP BY PRM_PTRPOLIDENT) PRIMEP WHERE PRIMEA.PRM_PTRPOLIDENT(+) = polices.POL_IDENT AND PRIMEP.PRM_PTRPOLIDENT(+) = polices.POL_IDENT AND polices.POL_PTRPASID = 1254 AND polices.pol_ident = laPolice.POL_IDENT), UPPER(TPE_LIBELLE)) TPE_LIBELLE , CL_CODPOST,CL_VILLE,CL_CODE,CL_NOMAPPEL,BAS_CODE_BRANCHE,PAS_CODE_PRODUIT, '' SOR_CODE_PRODUCT FROM F_POLICES laPolice, F_CLIENTS, F_PRODUITASS, F_BRANCHEASS, F_SIT_OBJET_RISQUE, T_POL_ETAT, F_INTERMEDIAIRE WHERE SUBSTR(CL_CODPOST, 1 , 2) = '82' AND PAS_IDENT=204 AND laPolice.POL_PTRCLID=CL_IDENT AND PAS_IDENT=laPolice.POL_PTRPASID AND BAS_IDENT=PAS_PTRBASID AND ((F_SIT_OBJET_RISQUE.SOR_IDENT = (select min(SOR_IDENT) from F_SIT_OBJET_RISQUE s2 WHERE s2.SOR_PTRPOLID=POL_IDENT AND s2.SOR_PTRSORSUIVID=0)) OR (F_SIT_OBJET_RISQUE.SOR_IDENT is null)) AND SOR_PTRPOLID(+) = laPolice.POL_IDENT AND SOR_PTRSORSUIVID(+)=0 AND TPE_IDENT = laPolice.POL_PTRTPEIDENT AND (NVL(laPolice.POL_OPTION,'-') not in ('ACCUEIL','LITIGE','COLLECTIF') ) AND laPolice.POL_PTRINID = IN_IDENT ORDER BY CL_NOMAPPEL

tu peux nous mettre tout ca dans des balises, car la c'est illisible.

Merci

**breizh76** · 10/08/2009, 17h33

Autant pour moi, en esperant que ca sera mieux.

La requête:

Code sql :

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

1
2
 
SELECT /* POLICE_RECHERCHE - M_SQL_SELECT_LISTE_POLICE2 */ distinct laPolice.POL_IDENT POL_IDENT, laPolice.POL_NUMPOL POL_NUMPOL, laPolice.POL_NUM_DOSSIER POL_NUM_DOSSIER, laPolice.POL_CODETAT POL_CODETAT, laPolice.POL_DATDEB POL_DATDEB, DECODE(PAS_CODE_PRODUIT, 'MAP', (SELECT DECODE(polices.POL_CODETAT, 0,'PRE-CONTRAT',96,'CONTRAT SANS EFFET', 1,DECODE(nvl(PRIMEP.NBP,0),1,DECODE(nvl(PRIMEA.NBA,0),0,'DEVIS','DEVIS AVENANT'), 'EN COURS')) ETAT_CONTRAT FROM F_POLICES polices, (SELECT count(PRM_IDENT) NBA , PRM_PTRPOLIDENT FROM F_PRIME WHERE PRM_TYPE = 'A' GROUP BY PRM_PTRPOLIDENT) PRIMEA, (SELECT count(PRM_IDENT) NBP, PRM_PTRPOLIDENT FROM F_PRIME WHERE PRM_TYPE = 'P' GROUP BY PRM_PTRPOLIDENT) PRIMEP WHERE PRIMEA.PRM_PTRPOLIDENT(+) = polices.POL_IDENT AND PRIMEP.PRM_PTRPOLIDENT(+) = polices.POL_IDENT AND polices.POL_PTRPASID = 1254 AND polices.pol_ident = laPolice.POL_IDENT), UPPER(TPE_LIBELLE)) TPE_LIBELLE , CL_CODPOST,CL_VILLE,CL_CODE,CL_NOMAPPEL,BAS_CODE_BRANCHE,PAS_CODE_PRODUIT, '' SOR_CODE_PRODUCT FROM F_POLICES laPolice, F_CLIENTS, F_PRODUITASS, F_BRANCHEASS, F_SIT_OBJET_RISQUE, T_POL_ETAT, F_INTERMEDIAIRE WHERE SUBSTR(CL_CODPOST, 1 , 2) = '82' AND PAS_IDENT=204 AND laPolice.POL_PTRCLID=CL_IDENT AND PAS_IDENT=laPolice.POL_PTRPASID AND BAS_IDENT=PAS_PTRBASID AND ((F_SIT_OBJET_RISQUE.SOR_IDENT = (select min(SOR_IDENT) from F_SIT_OBJET_RISQUE s2 WHERE s2.SOR_PTRPOLID=POL_IDENT AND s2.SOR_PTRSORSUIVID=0)) OR (F_SIT_OBJET_RISQUE.SOR_IDENT is null)) AND SOR_PTRPOLID(+) = laPolice.POL_IDENT AND SOR_PTRSORSUIVID(+)=0 AND TPE_IDENT = laPolice.POL_PTRTPEIDENT AND (NVL(laPolice.POL_OPTION,'-') not in ('ACCUEIL','LITIGE','COLLECTIF') ) AND laPolice.POL_PTRINID = IN_IDENT ORDER BY CL_NOMAPPEL

Plan d'execution:

Code sql :

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
 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25370 Card=67892 Bytes=13171048)
1 0 NESTED LOOPS (OUTER) (Cost=17 Card=1 Bytes=65)
2 1 NESTED LOOPS (OUTER) (Cost=10 Card=1 Bytes=39)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (Cost=3 Card=1 Bytes=13)
4 3 INDEX (UNIQUE SCAN) OF 'PK_POL_IDENT' (UNIQUE) (Cost=2 Card=1)
5 2 VIEW (Cost=7 Card=1 Bytes=26)
6 5 SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7Card=2 Bytes=16)
8 7 INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON-UNIQUE) (Cost=3 Card=6)
9 1 VIEW (Cost=7 Card=1 Bytes=26)
10 9 SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7 Card=2 Bytes=16)
12 11 INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON-UNIQUE) (Cost=3 Card=6)
13 0 SORT (UNIQUE) (Cost=24388 Card=67892 Bytes=13171048)
14 13 FILTER
15 14 NESTED LOOPS (Cost=23406 Card=67892 Bytes=13171048)
16 15 HASH JOIN (Cost=23406 Card=67892 Bytes=12899480)
17 16 TABLE ACCESS (FULL) OF 'T_POL_ETAT' (Cost=7 Card=42 Bytes=1512)
18 16 HASH JOIN (OUTER) (Cost=23396 Card=67901 Bytes=10456754)
19 18 HASH JOIN (Cost=17883 Card=47838 Bytes=6649482)
20 19 NESTED LOOPS (Cost=6 Card=45096 Bytes=3742968)
21 20 NESTED LOOPS (Cost=2 Card=1 Bytes=31)
22 21 TABLE ACCESS (BY INDEX ROWID) OF 'F_PRODUI
TASS' (Cost=1 Card=1 Bytes=16)
23 22 INDEX (UNIQUE SCAN) OF 'PK_PAS_IDENT' (UNIQUE)
24 21 TABLE ACCESS (BY INDEX ROWID) OF 'F_BRANCHEASS' (Cost=1 Card=1 Bytes=15)
25 24 INDEX (UNIQUE SCAN) OF 'PK_BAS_IDENT' (UNIQUE)
26 20 INDEX (FAST FULL SCAN) OF 'IDX_ESSAI' (UNIQUE) (Cost=4 Card=45096 Bytes=2344992)
27 19 TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (Cost=17534 Card=348329 Bytes=19506424)
28 27 INDEX (RANGE SCAN) OF 'IND_POL_PTRPASID' (NON-UNIQUE) (Cost=505 Card=7)
29 18 INDEX (FAST FULL SCAN) OF 'IDX_ESSAI2' (UNIQUE)(Cost=4 Card=16386523 Bytes=245797845)
30 15 INDEX (UNIQUE SCAN) OF 'PK_IN_IDENT' (UNIQUE)
31 14 SORT (AGGREGATE)
32 31 FIRST ROW (Cost=2 Card=1 Bytes=15)
33 32 INDEX (RANGE SCAN (MIN/MAX)) OF 'IDX_DIEGO2' (UNIQUE) (Cost=2 Card=18581147)

**Z3phur** · 10/08/2009, 17h35

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
/* Formatted on 10/08/2009 17:36:14 (QP5 v5.115.810.9015) */
  SELECT                   /* POLICE_RECHERCHE - M_SQL_SELECT_LISTE_POLICE2 */
        DISTINCT
           laPolice.POL_IDENT POL_IDENT,
           laPolice.POL_NUMPOL POL_NUMPOL,
           laPolice.POL_NUM_DOSSIER POL_NUM_DOSSIER,
           laPolice.POL_CODETAT POL_CODETAT,
           laPolice.POL_DATDEB POL_DATDEB,
           DECODE (
              PAS_CODE_PRODUIT,
              'MAP',
              (SELECT   DECODE (
                           polices.POL_CODETAT,
                           0,
                           'PRE-CONTRAT',
                           96,
                           'CONTRAT SANS EFFET',
                           1,
                           DECODE (
                              NVL (PRIMEP.NBP, 0),
                              1,
                              DECODE (NVL (PRIMEA.NBA, 0),
                                      0, 'DEVIS',
                                      'DEVIS AVENANT'),
                              'EN COURS'
                           )
                        )
                           ETAT_CONTRAT
                 FROM   F_POLICES polices,
                        (  SELECT   COUNT (PRM_IDENT) NBA, PRM_PTRPOLIDENT
                             FROM   F_PRIME
                            WHERE   PRM_TYPE = 'A'
                         GROUP BY   PRM_PTRPOLIDENT) PRIMEA,
                        (  SELECT   COUNT (PRM_IDENT) NBP, PRM_PTRPOLIDENT
                             FROM   F_PRIME
                            WHERE   PRM_TYPE = 'P'
                         GROUP BY   PRM_PTRPOLIDENT) PRIMEP
                WHERE       PRIMEA.PRM_PTRPOLIDENT(+) = polices.POL_IDENT
                        AND PRIMEP.PRM_PTRPOLIDENT(+) = polices.POL_IDENT
                        AND polices.POL_PTRPASID = 1254
                        AND polices.pol_ident = laPolice.POL_IDENT),
              UPPER (TPE_LIBELLE)
           )
              TPE_LIBELLE,
           CL_CODPOST,
           CL_VILLE,
           CL_CODE,
           CL_NOMAPPEL,
           BAS_CODE_BRANCHE,
           PAS_CODE_PRODUIT,
           '' SOR_CODE_PRODUCT
    FROM   F_POLICES laPolice,
           F_CLIENTS,
           F_PRODUITASS,
           F_BRANCHEASS,
           F_SIT_OBJET_RISQUE,
           T_POL_ETAT,
           F_INTERMEDIAIRE
   WHERE       SUBSTR (CL_CODPOST, 1, 2) = '82'
           AND PAS_IDENT = 204
           AND laPolice.POL_PTRCLID = CL_IDENT
           AND PAS_IDENT = laPolice.POL_PTRPASID
           AND BAS_IDENT = PAS_PTRBASID
           AND ( (F_SIT_OBJET_RISQUE.SOR_IDENT =
                     (SELECT   MIN (SOR_IDENT)
                        FROM   F_SIT_OBJET_RISQUE s2
                       WHERE   s2.SOR_PTRPOLID = POL_IDENT
                               AND s2.SOR_PTRSORSUIVID = 0))
                OR (F_SIT_OBJET_RISQUE.SOR_IDENT IS NULL))
           AND SOR_PTRPOLID(+) = laPolice.POL_IDENT
           AND SOR_PTRSORSUIVID(+) = 0
           AND TPE_IDENT = laPolice.POL_PTRTPEIDENT
           AND (NVL (laPolice.POL_OPTION, '-') NOT IN
                      ('ACCUEIL', 'LITIGE', 'COLLECTIF'))
           AND laPolice.POL_PTRINID = IN_IDENT
ORDER BY   CL_NOMAPPEL

c'est plus lisible comme ceci

**breizh76** · 10/08/2009, 17h45

J'ai fait la modif...

Désolé.

**breizh76** · 11/08/2009, 10h52

Pensez vous qu'il est possible d'eviter le full scan ??

Merci

**farenheiit** · 11/08/2009, 14h27

dans ta requete il faudrait que tu rajoutes des alias et que tu prefixes les noms de champs par ces alias car là on ne comprends pas du tout comment sont fait les jointures.

**breizh76** · 12/08/2009, 08h53

JE suis d'accord mais le problème c'est que j'ai récupéré cette requête via la SQLAREA, j'ai détecté 2 access Full sur 2 tables et pour le corriger, j'ai simplement créé deux index sur plusieurs colonne (Celle listée dans la requête). En essayant de suivre un ordre logique.

Pas évident

mais cela semble pour le moment fonctionner

Ce qui m'inquiète le plus C'est les conséquence de cette création d'index. Je crains qu'elle augmente le temps des inserts dans la table....

**farenheiit** · 12/08/2009, 09h50

corrige ta requête en mettant des alias.
envoie nous la requête d'origine sans les indexes avec le plan d'execution associée et les statistqiues d'execution et la durée d'execution (set timing on + set autotrace traceonly).

**breizh76** · 12/08/2009, 10h25

Je ne pourrais pas vous envoyer cela avant lundi prochain...

**breizh76** · 17/08/2009, 16h46

Je vous renvoie la requete:

Code sql :

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
/* Formatted on 10/08/2009 17:36:14 (QP5 v5.115.810.9015) */
  SELECT                   /* POLICE_RECHERCHE - M_SQL_SELECT_LISTE_POLICE2 */
        DISTINCT
           laPolice.POL_IDENT POL_IDENT,
           laPolice.POL_NUMPOL POL_NUMPOL,
           laPolice.POL_NUM_DOSSIER POL_NUM_DOSSIER,
           laPolice.POL_CODETAT POL_CODETAT,
           laPolice.POL_DATDEB POL_DATDEB,
           DECODE (
              PAS_CODE_PRODUIT,
              'MAP',
              (SELECT   DECODE (
                           polices.POL_CODETAT,
                           0,
                           'PRE-CONTRAT',
                           96,
                           'CONTRAT SANS EFFET',
                           1,
                           DECODE (
                              NVL (PRIMEP.NBP, 0),
                              1,
                              DECODE (NVL (PRIMEA.NBA, 0),
                                      0, 'DEVIS',
                                      'DEVIS AVENANT'),
                              'EN COURS'
                           )
                        )
                           ETAT_CONTRAT
                 FROM   F_POLICES polices,
                        (  SELECT   COUNT (PRM_IDENT) NBA, PRM_PTRPOLIDENT
                             FROM   F_PRIME
                            WHERE   PRM_TYPE = 'A'
                         GROUP BY   PRM_PTRPOLIDENT) PRIMEA,
                        (  SELECT   COUNT (PRM_IDENT) NBP, PRM_PTRPOLIDENT
                             FROM   F_PRIME
                            WHERE   PRM_TYPE = 'P'
                         GROUP BY   PRM_PTRPOLIDENT) PRIMEP
                WHERE       PRIMEA.PRM_PTRPOLIDENT(+) = polices.POL_IDENT
                        AND PRIMEP.PRM_PTRPOLIDENT(+) = polices.POL_IDENT
                        AND polices.POL_PTRPASID = 1254
                        AND polices.pol_ident = laPolice.POL_IDENT),
              UPPER (TPE_LIBELLE)
           )
              TPE_LIBELLE,
           CL_CODPOST,
           CL_VILLE,
           CL_CODE,
           CL_NOMAPPEL,
           BAS_CODE_BRANCHE,
           PAS_CODE_PRODUIT,
           '' SOR_CODE_PRODUCT
    FROM   F_POLICES laPolice,
           F_CLIENTS,
           F_PRODUITASS,
           F_BRANCHEASS,
           F_SIT_OBJET_RISQUE,
           T_POL_ETAT,
           F_INTERMEDIAIRE
   WHERE       SUBSTR (CL_CODPOST, 1, 2) = '82'
           AND PAS_IDENT = 204
           AND laPolice.POL_PTRCLID = CL_IDENT
           AND PAS_IDENT = laPolice.POL_PTRPASID
           AND BAS_IDENT = PAS_PTRBASID
           AND ( (F_SIT_OBJET_RISQUE.SOR_IDENT =
                     (SELECT   MIN (SOR_IDENT)
                        FROM   F_SIT_OBJET_RISQUE s2
                       WHERE   s2.SOR_PTRPOLID = POL_IDENT
                               AND s2.SOR_PTRSORSUIVID = 0))
                OR (F_SIT_OBJET_RISQUE.SOR_IDENT IS NULL))
           AND SOR_PTRPOLID(+) = laPolice.POL_IDENT
           AND SOR_PTRSORSUIVID(+) = 0
           AND TPE_IDENT = laPolice.POL_PTRTPEIDENT
           AND (NVL (laPolice.POL_OPTION, '-') NOT IN
                      ('ACCUEIL', 'LITIGE', 'COLLECTIF'))
           AND laPolice.POL_PTRINID = IN_IDENT
ORDER BY   CL_NOMAPPEL

ainsi que le plan d'execution et les statistiques avant la mise en place des index:

Code sql :

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
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=50830 Card=67321 Byt
          es=13060274)
 
   1    0   NESTED LOOPS (OUTER) (Cost=17 Card=1 Bytes=65)
   2    1     NESTED LOOPS (OUTER) (Cost=10 Card=1 Bytes=39)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (Cost=3 C
          ard=1 Bytes=13)
 
   4    3         INDEX (UNIQUE SCAN) OF 'PK_POL_IDENT' (UNIQUE) (Cost
          =2 Card=1)
 
   5    2       VIEW (Cost=7 Card=1 Bytes=26)
   6    5         SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
   7    6           TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7
           Card=2 Bytes=16)
 
   8    7             INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON
          -UNIQUE) (Cost=3 Card=6)
 
   9    1     VIEW (Cost=7 Card=1 Bytes=26)
  10    9       SORT (GROUP BY) (Cost=7 Card=2 Bytes=16)
  11   10         TABLE ACCESS (BY INDEX ROWID) OF 'F_PRIME' (Cost=7 C
          ard=2 Bytes=16)
 
  12   11           INDEX (RANGE SCAN) OF 'IND_PRM_PTRPOLIDENT' (NON-U
          NIQUE) (Cost=3 Card=6)
 
  13    0   SORT (UNIQUE) (Cost=49856 Card=67321 Bytes=13060274)
  14   13     FILTER
  15   14       NESTED LOOPS (Cost=48882 Card=67321 Bytes=13060274)
  16   15         HASH JOIN (Cost=48882 Card=67321 Bytes=12790990)
  17   16           TABLE ACCESS (FULL) OF 'T_POL_ETAT' (Cost=7 Card=4
          2 Bytes=1512)
 
  18   16           HASH JOIN (OUTER) (Cost=48872 Card=67331 Bytes=103
          68974)
 
  19   18             HASH JOIN (Cost=28290 Card=47449 Bytes=6595411)
  20   19               NESTED LOOPS (Cost=10815 Card=44864 Bytes=3723
          712)
 
  21   20                 NESTED LOOPS (Cost=2 Card=1 Bytes=31)
  22   21                   TABLE ACCESS (BY INDEX ROWID) OF 'F_PRODUI
          TASS' (Cost=1 Card=1 Bytes=16)
 
  23   22                     INDEX (UNIQUE SCAN) OF 'PK_PAS_IDENT' (U
          NIQUE)
 
  24   21                   TABLE ACCESS (BY INDEX ROWID) OF 'F_BRANCH
          EASS' (Cost=1 Card=1 Bytes=15)
 
  25   24                     INDEX (UNIQUE SCAN) OF 'PK_BAS_IDENT' (U
          NIQUE)
 
  26   20                 TABLE ACCESS (FULL) OF 'F_CLIENTS' (Cost=108
          13 Card=44864 Bytes=2332928)
 
  27   19               TABLE ACCESS (BY INDEX ROWID) OF 'F_POLICES' (
          Cost=17142 Card=335952 Bytes=18813312)
 
  28   27                 INDEX (RANGE SCAN) OF 'IND_POL_PTRPASID' (NO
          N-UNIQUE) (Cost=504 Card=6)
 
  29   18             TABLE ACCESS (FULL) OF 'F_SIT_OBJET_RISQUE' (Cos
          t=15277 Card=15773533 Bytes=236602995)
 
  30   15         INDEX (UNIQUE SCAN) OF 'PK_IN_IDENT' (UNIQUE)
  31   14       SORT (AGGREGATE)
  32   31         TABLE ACCESS (BY INDEX ROWID) OF 'F_SIT_OBJET_RISQUE
          ' (Cost=4 Card=1 Bytes=15)
 
  33   32           INDEX (RANGE SCAN) OF 'IND_SOR_PTRPOLID' (NON-UNIQ
          UE) (Cost=3 Card=2)
 
Statistics
----------------------------------------------------------
        725  recursive calls
          0  db block gets
     281300  consistent gets
     280353  physical reads
          0  redo size
       1652  bytes sent via SQL*Net to client
        676  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

creation d'un index

Administration Oracle

Discussions similaires

Partager

Partager