"LIKE UPPER" plus rapide que "IN" ou "="

**thanaos** · 29/05/2006, 16h23

Euuuh.... oui. Enfin je crois...attends je regarde...
L'enterprise Manager me dit que ce sont des types NUMBER...
TOAD me dit que ce sont des types NUMBER...
A moins d'une conspiration internationnale je crois que ce sont sincèrement des types NUMBER.

Ou alors mille excuses je n'est pas compris ta remarque.

**SheikYerbouti** · 29/05/2006, 16h28

Ok, et tb2.fk_id_tb1 est de quel type ?

**McM** · 29/05/2006, 16h36

Je ne comprend pas pourquoi depuis le début tu compares un IN avec un

Code :

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

LIKE .. OR LIKE ...

Pourquoi tu n'utilises pas des

Code :

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

= ... OR = ...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13

AND CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION IN(41,4,5,6,7,9)
 
comparé à
 
AND( CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION =41
OR CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION =4
OR CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION =5
OR CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION =6
OR CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION =7
OR CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION =9
)

**thanaos** · 29/05/2006, 17h38

Toutes les colonnes ID (PK et donc FK) sont de type NUMBER.
Pour les " OR = " le résultat est du même gout qu'avec le IN.

**SheikYerbouti** · 29/05/2006, 17h40

Un index foireux ?

**aline** · 29/05/2006, 19h53

Bonjour à tous.

et bien, si vous voulez mon avis, les deux plans sont faux!
un est juste moins pire que l'autre.

Alors, si j'ai bien compris, la colonne ID_ETAT_EVOLUTION est la primary key de CDE_ETAT_EVOLUTION et CDE_EVOLUTION_SOFO pointe dessus
première question:

est ce que tu as explicitement dis qu'il y avait

**thanaos** · 29/05/2006, 20h01

CDE_DEMANDE (ID_DEMANDE PK)
-> CDE_SOFO (ID_SOFO PK, ID_DEMANDE FK)
-> CDE_EVOLUTION_SOFO (ID_EVOLUTION PK, ID_SOFO FK, ID_ETAT_EVOLUTION FK)
-> CDE_ETAT_EVOLUTION (ID_ETAT_EVOLUTION PK)
Voilà pour les PK et les FK.

**aline** · 29/05/2006, 20h07

Bonjour à tous.

et bien, si vous voulez mon avis, les deux plans sont faux!
un est juste moins pire que l'autre.

Alors, si j'ai bien compris, la colonne ID_ETAT_EVOLUTION est la primary key de CDE_ETAT_EVOLUTION et CDE_EVOLUTION_SOFO pointe dessus
première question:

est ce que tu as explicitement dis qu'il y avait une foreign key de sofo vers evolution? C'est très important.
Et c'est toujours oublié!
Il faut dire à Oracle ce qu'il se passe, il va pas le deviner pour vous!
Je suis quasiment sûre que cela n'est pas fait.
et en numéro deux:
Y a t'il un index sur CDE_EVOLUTION_SOFO.ID_ETAT_EVOLUTION ?
La encore, j'ai un gros doute!

Au moins un des deux d*n'est pas fait.

voici un résumé de tes deux plans d'éxécution:
Celui qui marche:

Code :

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

1
2
3
4
 
 HASH JOIN
TABLE ACCESS FULL   CDE_ETAT_EVOLUTION
TABLE ACCESS FULL   CDE_EVOLUTION_SOFO

C'est le moins pire aujourd'hui.
Oracle ne vois pas la liaison entre les deux tables et n'utilise aucun index.
Quand tes deux tables vont grandir, cela va se dégrader très rapidement.

Celui qui ne marche pas:

Code :

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

1
2
3
4
5
6
 
HASH JOIN          
 INLIST ITERATOR   
    TABLE ACCESS  BY INDEX ROWID CDE_ETAT_EVOLUTION
      INDEX RANGE SCAN           PK_CDE_ETAT_EVOLUTION_ID
    TABLE ACCESS FULL            CDE_EVOLUTION_SOFO

toujours le table acces full

C'est presque bon en fait.
Il faux que l'on arrive à un nested loop et que l'on passe par l'index qui recouvre la colonne ID_ETAT_EVOLUTION de CDE_EVOLUTION_SOFO.

Cela devrait être fait si mes conseils ne sont pas débiles!

Et donc le in est mieux que le like, c'est juste le modèle qui est bancale!

de nouveaux parmis vous!

**thanaos** · 29/05/2006, 21h58

est ce que tu as explicitement dis qu'il y avait une foreign key de sofo vers evolution? C'est très important.
Et c'est toujours oublié!
Il faut dire à Oracle ce qu'il se passe, il va pas le deviner pour vous!
Je suis quasiment sûre que cela n'est pas fait.

Toutes les PK ont un INDEX et toutes les FK sont déclarées.

et en numéro deux:
Y a t'il un index sur CDE_EVOLUTION_SOFO.ID_ETAT_EVOLUTION ?
La encore, j'ai un gros doute!

CDE_EVOLUTION_SOFO.ID_ETAT_EVOLUTION est une FK déclarée comme suit :

Code :

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

1
2
CONSTRAINT FK_CDE_EVOLUTION_ID_ETAT_EVOL FOREIGN KEY (ID_ETAT_EVOLUTION)
REFERENCES CDE_ETAT_EVOLUTION (ID_ETAT_EVOLUTION) ON DELETE CASCADE

...sans plus. Y a un blem ?

**aline** · 30/05/2006, 08h18

Bonjour thanaos,

Et y a t'il un index sur CDE_EVOLUTION_SOFO.ID_ETAT_EVOLUTION ?
Tu ne m'as pas répondu.
Si oui, les statistiques sont elles calculées?
Dans ce cas, j'en perd mon latin, mais un hint pourra toujours arranger cela avantageusement! Il faut avoir confiance!

**thanaos** · 30/05/2006, 08h51

Mes hommages du matin, chère Aline.
N'étant pas DBA, mais néanmoins à l'origine du script sus nommé, ma naïveté de "larve de développeur SQL" m'insite à te poser cette simple question :
à quoi qu'ça sert un index sur une FK quand'y en a déjà un sur la PK de référence ?

**aline** · 30/05/2006, 09h39

cher Thanaos,

cela sert à pleins de choses!
l'index te permet d'accelerer l'accès aux données sur une table. Dans ce cas particulier, il te servira à acceder aux données de la table CDE_EVOLUTION_SOFO. en effet la primary key ne pourra en aucun cas le faire.
Et chaque fois que tu voudras acceder à cette table, il y aura beaucoup de chances que cela soit à travers une condition restrictive forte sur cette colonne et tu ne pourras pas en profiter comme dans l'exemple que tu nous à donné.
Poser un index résoudra ici tout tes problèmes!
Et comme le supposait tout mes éminents collègues, le in sera au moins aussi bon que le like!*Et je maintiens que ton exemple qui marche bien aujourd'hui ne suppoortera pas une augmentation dui volume des données.

Je ne serais contente que quand tu auras une nested loop entre les deux tables avec un acces unique sur la table primaire et un range scan sur l'index de la table référante (index que tu vas créer bien sur)

**thanaos** · 30/05/2006, 11h09

Je viens de créer cet index.

Code :

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

1
2
3
4
CREATE INDEX INDX_EVOLUTION_SOFO_ETAT ON CDE_EVOLUTION_SOFO
(ID_ETAT_EVOLUTION) LOGGING TABLESPACE IDEA_INDX PCTFREE 10 INITRANS 2
MAXTRANS 255 NOPARALLEL;

Pour rappel le code de la requête

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT CDE_EVOLUTION_SOFO.*
FROM
LAST_EVOLUTION_DEMANDE,
CDE_DEMANDE,
CDE_SOFO, 
CDE_EVOLUTION_SOFO, 
CDE_ETAT_EVOLUTION
WHERE LAST_EVOLUTION_DEMANDE.ID_DEMANDE=CDE_DEMANDE.ID_DEMANDE
AND LAST_EVOLUTION_DEMANDE.ID_SOFO=CDE_SOFO.ID_SOFO
AND LAST_EVOLUTION_DEMANDE.ID_EVOLUTION=CDE_EVOLUTION_SOFO.ID_EVOLUTION
AND CDE_EVOLUTION_SOFO.ID_ETAT_EVOLUTION=CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION
AND CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION IN (41,4,5,6,7,9)

et voilà le plan d'execution:

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
OPERATION                                     TYPE_ACCES           NOM_OBJET                      ORDRE    Cout Op
--------------------------------------------- -------------------- ------------------------------ --
SELECT STATEMENT                                                   COST= 11816150684              0-0-1181  COUT=11816150684,Card=1149013724
                                                                                                  6150684
FILTER                                                                                          1-0-1     COUT=,Card=
    SORT                                      GROUP BY                                            2-1-1     COUT=11816150684,Card=1149013724
      NESTED LOOPS                                                                                3-2-1     COUT=28772261,Card=114901372329
        HASH JOIN                                                                                 4-3-1     COUT=28772261,Card=75878431665
          TABLE ACCESS                        FULL                 CDE_EVOLUTION_SOFO             5-4-1     COUT=12,Card=10935
          MERGE JOIN                          CARTESIAN                                           6-4-2     COUT=21631056,Card=15460187083
            MERGE JOIN                        CARTESIAN                                           7-6-1     COUT=852,Card=1802517
              NESTED LOOPS                                                                        8-7-1     COUT=12,Card=210
OPERATION                                     TYPE_ACCES           NOM_OBJET                      ORDRE    Cout Op
--------------------------------------------- -------------------- ------------------------------ --
                TABLE ACCESS                  FULL                 CDE_EVOLUTION_SOFO             9-8-1     COUT=12,Card=4101
                INLIST ITERATOR                                                                   10-8-2    COUT=,Card=
                  INDEX                       UNIQUE SCAN          PK_CDE_ETAT_EVOLUTION_ID       11-10-1   COUT=,Card=1
              BUFFER                          SORT                                                12-7-2    COUT=852,Card=8577
                INDEX                         FAST FULL SCAN       PK_CDE_SOFO_ID                 13-12-1   COUT=4,Card=8577
            BUFFER                            SORT                                                14-6-2    COUT=21631052,Card=8577
              TABLE ACCESS                    FULL                 CDE_SOFO                       15-14-1   COUT=12,Card=8577
        INDEX                                 UNIQUE SCAN          PK_CDE_DEMANDE_ID_DEMANDE      16-3-2    COUT=,Card=2

(Merci de ton aide Aline

)

**aline** · 30/05/2006, 11h14

C'est déjà mieux puisq'on a une nested loop (c'est appréciable ici).
J'imagine que cela doit déjà aller mieux.
Maintenant, il te faut analyser cet index ainsi que(si ce ,'est pas déjà fait) toutes les tables participant à ta requête.

**thanaos** · 30/05/2006, 11h23

Ca ne va pas mieux.

Le résultat de requete n'arrive pas. La seule solution viable que j'ai trouvé et celle-ci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT CDE_EVOLUTION_SOFO.*
FROM
LAST_EVOLUTION_DEMANDE,
CDE_DEMANDE,
CDE_SOFO, 
CDE_EVOLUTION_SOFO, 
CDE_ETAT_EVOLUTION
WHERE CDE_DEMANDE.ID_DEMANDE=CDE_SOFO.ID_DEMANDE
AND CDE_SOFO.ID_SOFO=CDE_EVOLUTION_SOFO.ID_SOFO
AND CDE_EVOLUTION_SOFO.ID_EVOLUTION=LAST_EVOLUTION_DEMANDE.ID_EVOLUTION
AND CDE_EVOLUTION_SOFO.ID_ETAT_EVOLUTION=CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION
AND CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION IN (41,4,5,6,7,9)

ne faire appelle qu'une fois à la vue.

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
OPERATION                                     TYPE_ACCES           NOM_OBJET                      ORDRE    Cout Op
--------------------------------------------- -------------------- ------------------------------ -------- ---------------------
SELECT STATEMENT                                                   COST= 192                      0-0-192   COUT=192,Card=4716
  NESTED LOOPS                                                                                    1-0-1     COUT=192,Card=4716
    HASH JOIN                                                                                     2-1-1     COUT=192,Card=3114
      HASH JOIN                                                                                   3-2-1     COUT=178,Card=809
        NESTED LOOPS                                                                              4-3-1     COUT=12,Card=210
          TABLE ACCESS                        FULL                 CDE_EVOLUTION_SOFO             5-4-1     COUT=12,Card=4101
          INDEX                               UNIQUE SCAN          PK_CDE_ETAT_EVOLUTION_ID       6-4-2     COUT=,Card=1
        VIEW                                                       LAST_EVOLUTION_DEMANDE         7-3-2     COUT=165,Card=42096
          SORT                                GROUP BY                                            8-7-1     COUT=165,Card=42096
            HASH JOIN                                                                             9-8-1     COUT=27,Card=42096
              TABLE ACCESS                    FULL                 CDE_EVOLUTION_SOFO             10-9-1    COUT=12,Card=10935
OPERATION                                     TYPE_ACCES           NOM_OBJET                      ORDRE    Cout Op
--------------------------------------------- -------------------- ------------------------------ -------- ---------------------
              TABLE ACCESS                    FULL                 CDE_SOFO                       11-9-2    COUT=12,Card=8577
      TABLE ACCESS                            FULL                 CDE_SOFO                       12-2-2    COUT=12,Card=8577
    INDEX                                     UNIQUE SCAN          PK_CDE_DEMANDE_ID_DEMANDE      13-1-2    COUT=,Card=2

**aline** · 30/05/2006, 11h38

Il faut croire que ce ne sont pas les mêmes requetes.
Je t'avoue que je n'ai pas le temps de démeler tes deux requêtes

.
dans la première il y a un autre problème il me semble que celui que nous étudions puisque tu a des jointures cartesiennes.

dans ta deuxième qui semble marcher, essayes ceci:

Code :

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

1
2
3
 
SELECT /*+ index(CDE_EVOLUTION_SOFO INDX_EVOLUTION_SOFO_ETAT) */
CDE_EVOLUTION_SOFO.* FROM LAST_EVOLUTION_DEMANDE, CDE_DEMANDE, CDE_SOFO, CDE_EVOLUTION_SOFO, CDE_ETAT_EVOLUTION WHERE CDE_DEMANDE.ID_DEMANDE=CDE_SOFO.ID_DEMANDE AND CDE_SOFO.ID_SOFO=CDE_EVOLUTION_SOFO.ID_SOFO AND CDE_EVOLUTION_SOFO.ID_EVOLUTION=LAST_EVOLUTION_DEMANDE.ID_EVOLUTION AND CDE_EVOLUTION_SOFO.ID_ETAT_EVOLUTION=CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION AND CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION IN (41,4,5,6,7,9)

**thanaos** · 30/05/2006, 11h58

Lapin compris moi ! On fait appelle à l'index nominativement dans le SELECT ?

**aline** · 30/05/2006, 12h06

oui

**thanaos** · 30/05/2006, 12h23

Yé suis une crétino!

Je mérite la flagellation en place public.
Jointures cartésiennes. Evidement que la dernière requête fonctionne bien, ce n'est pas ID_ETAT_EVOLUTION_SOFO qui est en cause, c'est bien les liens avec la vue. C'est à croire qu'Oracle ne fait pas les jointures?
Mauvais

Code :

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

1
2
3
4
 
WHERE LAST_EVOLUTION_DEMANDE.ID_DEMANDE=CDE_DEMANDE.ID_DEMANDE
AND LAST_EVOLUTION_DEMANDE.ID_SOFO=CDE_SOFO.ID_SOFO
AND LAST_EVOLUTION_DEMANDE.ID_EVOLUTION=CDE_EVOLUTION_SOFO.ID_EVOLUTION

Bon

Code :

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

1
2
3
4
 
WHERE CDE_DEMANDE.ID_DEMANDE=CDE_SOFO.ID_DEMANDE
AND CDE_SOFO.ID_SOFO=CDE_EVOLUTION_SOFO.ID_SOFO
AND CDE_EVOLUTION_SOFO.ID_EVOLUTION=LAST_EVOLUTION_DEMANDE.ID_EVOLUTION

**dyvim** · 30/05/2006, 16h46

Envoyé par thanaos

Lapin compris moi ! On fait appelle à l'index nominativement dans le SELECT ?

C'est ce qui s'appelle un HINT pour ceux qui ne le savent pas...
Il en existe de nombreux autres mais celui ci permet de spécifier l'index à utiliser.
Note: à toujours utiliser avec précautions.

"LIKE UPPER" plus rapide que "IN" ou "="

Oracle

Discussions similaires

Partager

Partager