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

**thanaos** · 23/05/2006, 16h15

Bonjour à tous.
Pour mon premier post voilà une épine que je n'arrive pas à retirer.

La requête suivante :

SELECT tb2.* FROM tb1,tb2 WHERE tb1.id_tb1=tb2.fk_id_tb1
AND tb1.id_tb1 IN (nb1,nb2,nb3,nb4,nb5)

à un millénaire en temps de réponse alors que

SELECT tb2.* FROM tb1,tb2 WHERE tb1.id_tb1=tb2.fk_id_tb1
AND ( tb1.id_tb1 IN LIKE('nb1')
OR tb1.id_tb1 IN LIKE('nb2')
OR tb1.id_tb1 IN LIKE('nb3')
OR tb1.id_tb1 IN LIKE('nb4')
OR tb1.id_tb1 IN LIKE('nb5')
)

répond tel Flash l'éclair.
Vous l'aurez compris id_tb1 est une PK de type NUMBER indexée.

O Grands Maîtres de l'Oracle, montrez moi la lumière.

**orafrance** · 23/05/2006, 16h47

IN LIKE ça marche ????

Merci de penser à mettre les balises CODE

**dyvim** · 23/05/2006, 17h42

Est ce que tu n'aurais pas par hasard lancé tes deux commandes l'une après l'autre?

Parce que dans ce cas, la première commande aura remonté la table en mémoire et la deuxième aura donc gagné tout le temps nécessaire aux accès disque.

Sinon l'explication devrait t'être donnée par un explain plan et le choix des index par Oracle.

**orafrance** · 23/05/2006, 17h46

on remarque aussi que la 1° référence des colonnes et non des valeurs

**thanaos** · 24/05/2006, 10h45

Désolé pour le IN LIKE...c'est LIKE bien sur.

J'aurais du écrire

Code :

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

1
2
3
4
 
SELECT tb2.* FROM tb1,tb2
WHERE tb1.id_tb1=tb2.fk_id_tb1
AND tb1.id_tb1 IN (154,342,856,1452,2563)

et

Code :

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

1
2
3
4
5
6
7
8
SELECT tb2.* FROM tb1,tb2
WHERE tb1.id_tb1=tb2.fk_id_tb1
AND ( tb1.id_tb1 LIKE('154')
OR tb1.id_tb1 LIKE('342')
OR tb1.id_tb1 LIKE('856')
OR tb1.id_tb1 LIKE('1452')
OR tb1.id_tb1 LIKE('2563')
)

Pour les requêtes lancées l'une après l'autre, c'est non. Les indexs, eux, ont été reconstruits plusieurs fois. Ils portes sur les id respectifs des deux tables.
Voici une analyse faite par Toad 8.6 :

Library Cache Get Hit Ratio - 74.5657 - Dynamique or Unsharable SQL ?
Library Cache Pin Hit Ratio - 80.4034 - Shared Pool area too small
Chained Fetch Ratio - 0.5586 - PCTFREE too low for a table
Parse to Execute Ratio - 96.2764 - High parse to execute ratio

J'ai du mal à comprendre que sur des tables qui ne dépassent pas 3000 lignes, une instance qui fait moins de 50Mo de données, un select par IN ou = sur des colonnes de type NUMBER soit moins rapide qu'un LIKE et même un LIKE UPPER

Pour ma par, cela resemble à une dégradation de l'instance...peut être , peut être pas...je ne sais plus que penser.

(merci pour vos réponses)

**dyvim** · 24/05/2006, 10h53

Est ce que tu as les EXPLAIN PLAN de tes deux SELECT?
Que l'on voie comment il accède aux données...

**pifor** · 24/05/2006, 11h21

Il faudrait éviter de fairde des LIKE sur des colonnes NUMBER car cela oblige Oracle à faire des conversions systématiques pour chaque ligne NUMBER en VARCHAR2.

Quelle est la requête qui prend le plus de temps de temps ? Celle avec IN ou celle avec LIKE ?

Exécutez le script

Code :

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

<ORACLE_HOME>/rdbms/admin/utlxplan.sql

dans le schéma qui exécute la requête et avant de lancer le requête lancer la commande:

Code :

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

SET AUTOTRACE ON

.
Vous aurez:
- le plan d'exécution de la requête
- les ressources utilisées par l'exécution de la requête.

**orafrance** · 24/05/2006, 11h27

que donne ceci :

Code :

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

1
2
3
SELECT tb2.* FROM tb1,tb2
WHERE tb1.id_tb1=tb2.fk_id_tb1
AND tb1.id_tb1 IN ('154','342','856','1452','2563')

**thanaos** · 24/05/2006, 15h39

Voilà les plan d'éxecution des requêtes :

Query avec LIKE

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= 19851                    0-0-1985  COUT=19851,Card=1639760
                                                                                                  1

  NESTED LOOPS                                                                                    1-0-1     COUT=19851,Card=1639760
    HASH JOIN                                                                                     2-1-1     COUT=733,Card=19118
      HASH JOIN                                                                                   3-2-1     COUT=194,Card=12625
        HASH JOIN                                                                                 4-3-1     COUT=15,Card=3280
          TABLE ACCESS                        FULL                 CDE_ETAT_EVOLUTION             5-4-1     COUT=2,Card=5
          TABLE ACCESS                        FULL                 CDE_EVOLUTION_SOFO             6-4-2     COUT=12,Card=10935
        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
              TABLE ACCESS                    FULL                 CDE_SOFO                       11-9-2    COUT=12,Card=8577
      TABLE ACCESS                            FULL                 CDE_DEMANDE                    12-2-2    COUT=17,Card=12988
    TABLE ACCESS                              BY INDEX ROWID       CDE_SOFO                       13-1-2    COUT=1,Card=86
      INDEX                                   UNIQUE SCAN          PK_CDE_SOFO_ID                 14-13-1   COUT=,Card=1

Query avec IN :

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
OPERATION                                     TYPE_ACCES           NOM_OBJET                      ORDRE    Cout Op
--------------------------------------------- -------------------- ------------------------------ --
SELECT STATEMENT                                                   COST= 7729725364               0-0-7729  COUT=7729725364,Card=28256
                                                                                                  725364

  FILTER                                                                                          1-0-1     COUT=,Card=
    SORT                                      GROUP BY                                            2-1-1     COUT=7729725364,Card=28256
      MERGE JOIN                              CARTESIAN                                           3-2-1     COUT=160780907,Card=114901372329
        HASH JOIN                                                                                 4-3-1     COUT=23483,Card=13396452
          TABLE ACCESS                        FULL                 CDE_DEMANDE                    5-4-1     COUT=17,Card=12988
          HASH JOIN                                                                               6-4-2     COUT=3346,Card=8846733
            TABLE ACCESS                      FULL                 CDE_EVOLUTION_SOFO             7-6-1     COUT=12,Card=10935
            MERGE JOIN                        CARTESIAN                                           8-6-2     COUT=2534,Card=1802517
              HASH JOIN                                                                           9-8-1     COUT=14,Card=210
                INLIST ITERATOR                                                                   10-9-1    COUT=,Card=
                  TABLE ACCESS                BY INDEX ROWID       CDE_ETAT_EVOLUTION             11-10-1   COUT=1,Card=1
                    INDEX                     RANGE SCAN           PK_CDE_ETAT_EVOLUTION_ID       12-11-1   COUT=2,Card=1
                TABLE ACCESS                  FULL                 CDE_EVOLUTION_SOFO             13-9-2    COUT=12,Card=4101
              BUFFER                          SORT                                                14-8-2    COUT=2522,Card=8577
                TABLE ACCESS                  FULL                 CDE_SOFO                       15-14-1   COUT=12,Card=8577
        BUFFER                                SORT                                                16-3-2    COUT=7729725347,Card=8577
          TABLE ACCESS                        FULL                 CDE_SOFO                       17-16-1   COUT=12,Card=8577

**orafrance** · 24/05/2006, 15h40

et les requêtes ???

le problème est là :
MERGE JOIN CARTESIAN 3-2-1 COUT=160780907,Card=114901372329

une jointure entre les tables manquent probablement

**thanaos** · 24/05/2006, 15h46

Désolé Fred je n'ai pas vu ta réponse :
Voilà ce que cela donne : ORA-01467: sort key too long
Bon j'ose vous donner 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
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT
CDE_DEMANDE.REFERENCE_INTERNE_DEMANDE,
CDE_DEMANDE.REFERENCE_DEMANDE,
CDE_DEMANDE.LIBELLE_DEMANDE,
CDE_DEMANDE.EXPOSE_DEMANDE,
CDE_DEMANDE.ID_DEMANDE,
CDE_DEMANDE.ID_PERE_DEMANDE,
CDE_DEMANDE.REDACTEUR_ORIGINE_DEMANDE,
CDE_DEMANDE.SERVICE_ORIGINE_DEMANDE,
CDE_SOFO.COMMENTAIRE_SOFO,
CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION,
CDE_ETAT_EVOLUTION.NOM_ETAT_EVOLUTION,
CDE_EVOLUTION_SOFO.ID_EVOLUTION,
CDE_EVOLUTION_SOFO.ATTENTE_EVOLUTION,
CDE_EVOLUTION_SOFO.COMMENTAIRE_EVOLUTION,
CDE_DEMANDE.DATE_CREA_ORIGINE_DEMANDE,
CDE_SOFO.PRIORITE_SOFO,
CDE_SOFO.QSIHM_SOFO,
CDE_SOFO.MOTIF_SOFO,
CDE_SOFO.EXIGENCE_SOFO,
CDE_SOFO.DATE_REPONSE_SOFO,
CDE_SOFO.CAL_SOUHAIT_SOFO,
CDE_SOFO.IMPACT_SOFO,
CDE_SOFO.ID_TRANSVERS,
CDE_SOFO.ID_OFFRE,
CDE_SOFO.IDINSC_RECEPTEUR_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)

LAST_EVOLUTION_DEMANDE est une vue :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT
 MAX(CDE_EVOLUTION_SOFO.ID_EVOLUTION) AS ID_EVOLUTION,
 MAX(CDE_SOFO.ID_SOFO) AS ID_SOFO,
 CDE_SOFO.ID_DEMANDE
FROM
 CDE_EVOLUTION_SOFO,
 CDE_SOFO
WHERE CDE_SOFO.ID_SOFO= CDE_EVOLUTION_SOFO.ID_SOFO
GROUP BY CDE_SOFO.ID_DEMANDE

Comme sa vous savez tous, vous pouvez me taper dessus.

**orafrance** · 24/05/2006, 15h48

et avec le LIKE ?

**thanaos** · 24/05/2006, 15h53

Tu remplace :

Code :

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

AND CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION IN (41,4,5,6,7,9)

par

Code :

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

1
2
3
4
5
6
7
 AND ( UPPER(CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION) LIKE UPPER('41')
 OR UPPER(CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION) LIKE UPPER('4')
 OR UPPER(CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION) LIKE UPPER('5')
 OR UPPER(CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION) LIKE UPPER('6')
 OR UPPER(CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION) LIKE UPPER('7')
 OR UPPER(CDE_ETAT_EVOLUTION.ID_ETAT_EVOLUTION) LIKE UPPER('9')
)

**thanaos** · 29/05/2006, 11h30

Bon...

Je crois que mon problème n'inspire plus personne. Triste et burp.
Merci pour vos réponses en tout cas, et bravo pour votre site.

I'll be back.

**SheikYerbouti** · 29/05/2006, 11h35

Dans le cas du IN, les valeurs sont considérées comme NUMBER.
Dansd le cas du Like, elles sount entre cotes donc considérées comme CHAR.... (on se demande bien pourquoi d'ailleurs)

**SheikYerbouti** · 29/05/2006, 11h36

et en plus Upper() sur une chaine qui contient un chiffre, c'est fort !
c'est quoi 4 en minuscule ?

**thanaos** · 29/05/2006, 12h22

Début de réponse :
apparement le problème vient du fait de réaliser le lien entre trois tables en passant par la vue.
La vue LAST_EVOLUTION_DEMANDE référence les trois Id des tables CDE_DEMANDE, CDE_SOFO, CDE_EVOLUTION_SOFO. Son but est de ramener la dernière évolution (enregistrement des changements d'état) pour un sofo (version) d'une demande donnée. Ce que je fais, c'est que j'utilise la vue pour aller chercher les infos des trois tables

Code :

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

1
2
3
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

alors que si les liens "naturels" entre les 3 tables plus une référence à la vue sont utilisés

Code :

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

1
2
3
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

le IN fonctionne parfaitement.
Cela vous parait il logique ? Et si j'ose ... pourquoi ?

**SheikYerbouti** · 29/05/2006, 13h58

Une chose est sûre si vous avez collé avec exactitude le code. dans un cas vous manipulez des NUMBER et dans l'autre des CHAR, ce qui induit forcément dans le mauvais cas une conversion implicite extrêmement couteuse.