[SQL] Optimiser un WHERE Conditionnel : supprimer un LIKE '%'

Bonjour à tous,

Je cherche à optimiser un WHERE conditionnel en SQL Oracle (pas de PL/SQL).

Dans un premier écran de filtrage, l'utilisateur peut saisir ou non un numéro de département dans le champ :C3_DEP.
Je voudrais pouvoir utiliser ou non ce paramètre dans le WHERE d'une requête.

Actuellement, j'en suis à :

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT ADR_RUE, ADR_CP, ADR_VILLE, ADR_DEP FROM T_ADRESSE WHERE (ADR_DEP LIKE ( CASE WHEN :C3_DEP IS NULL then '%' ELSE :C3_DEP end) )

---

Cela fonctionne correctement, mais le code est très lent, surtout quand il n'y a aucun filtrage (le LIKE '%' qui en résulte est à la fois inutile et gourmand)

Précisions :
- Je ne peux pas construire la requête puis faire un EXECUTE IMMEDIATE car le contexte (htmldb 2.0) ne le permet pas. Ce doit donc être un SELECT "pur"
- je suis sous Oracle 10.2
- il y 6 autres critères similaires (cad avec la possibilité d'avoir un LIKE '%')

En vous remerciant par avance,

Yvan

Est-ce que cela peut t'aider ?

Code:

---

1 2 3

SELECT ADR_RUE, ADR_CP, ADR_VILLE, ADR_DEP FROM T_ADRESSE WHERE ADR_DEP = nvl(:C3_DEP, ADR_DEP)

---

Nicolas.

Merci beaucoup Nicolas !
Et merci pour ta réponse rapide.

La ligne que tu proposes accélère considérablement le travail de 5 des 7 critères ! Simple et terriblement efficace.

Je vais regarder comment appliquer cette idée aux deux autres critères.

Yvan

Pense que plutôt de gérer des case when, tu peux également faire des or :

Code:

---

1 2 3 4 5

select * from maTable where ((:param1 = 1 and maCol1 = 'A') or (:param1 = 2 and maCol2 = 'A') or :param1 is null=);

---

Citation:

---

Envoyé par NGasparotto

Est-ce que cela peut t'aider ?

Code:

---

1 2 3

SELECT ADR_RUE, ADR_CP, ADR_VILLE, ADR_DEP FROM T_ADRESSE WHERE ADR_DEP = nvl(:C3_DEP, ADR_DEP)

---

Nicolas.

---

Attention si :C3_DEP n'est pas renseigné, la requete ne ramenera jamais les lignes pour lesquelles adr_rep est NULL (s'il peut être NULL)

Si ce cas peut se présenter faut faire

Code:

---

WHERE NVL(ADR_DEP, '-1') = nvl(:C3_DEP, NVL(ADR_DEP,'-1'))

---

avec '-1' une chaine qui n'existe pas dans adr_dep.. Facile dans le cas de numbers > 0, plus dur pour les chaines.
Le mieux dans ce cas, c'est encore le OR IS NULL

Citation:

---

Envoyé par McM

Attention si :C3_DEP n'est pas renseigné, la requete ne ramenera jamais les lignes pour lesquelles adr_rep est NULL (s'il peut être NULL)

---

Biensur, mais j'ai donné une requête équivalente à la requête initialement donné, pour laquelle rien n'est ramené dans le cas d'une valeur null.

Ou bien je me trompe ?

Nicolas.

Je tenais juste à préciser. Si Ypicot ne connais pas le NVL, ça peut lui éviter des mauvaises surprises.

Histoire de faciliter la compréhension s'il en était encore besoin :

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT ADR_RUE, ADR_CP, ADR_VILLE, ADR_DEP FROM T_ADRESSE WHERE (ADR_DEP LIKE ( CASE WHEN :C3_DEP IS NULL then ADR_DEP ELSE :C3_DEP end) )

---

Et LIKE :arrow: = si possible :)

Merci beacoup à vous tous pour ces approfondissements !

plaineR, ta solution (qui marche très bien, je l'ai testée :D) soulève une question : est-ce que le fait d'utiliser plusieurs fois la colonne A ne va pas amoindrir les performances ? Je ne connais pas suffisament Oracle pour savoir s'il utilise les évaluations paresseuses (je n'ai sous la main qu'une base de test, trop petite pour faire des timing précis).

Merci McM pour cette précision sur le NVL : j'étais tombé dans dans le piège.

Par contre, ma requête exemple était un peu trop simplifiée, et surtout mal choisie. Le champ ADR_DEP ne peut pas être NULL, mais d'autres champs de la rq peuvent l'être. Donc, NGasparotto, ta réponse était tout à fait conforme à la question, c'est la question qui était incomplète.

Et merci à Fred_D, qui me remet en mémoire le "LIKE" vs "=", que j'avais lu dans un bouquin (ou un tuto), mais qui était tombé dans une trappe (un NULL ?) de mon cerveau.

Yvan

Citation:

---

Envoyé par ypicot

est-ce que le fait d'utiliser plusieurs fois la colonne A ne va pas amoindrir les performances ?

---

Sans connaître ta requête, tes indexes et ton plan d'exécution, il m'est difficile de répondre à la question.

Néanmoins, il faut savoir que si ta colonne est indexée, l'index ne sera pas forcément pris en compte si tu as des or. Mais avec le case when proposé par Fred ou le nvl proposé par Nicolas, tu risques d'avoir un table access full.

[EDIT] Suite à la remarque de Nicolas, j'ai corrigé mon post afin qu'il ne porte pas à confusion. [/EDIT]

Citation:

---

Envoyé par plaineR

...le nvl proposé par Nicolas, l'index n'est pas non plus pris en compte.

---

Ah bon ?
Bon, ce n'est peut-être pas super optimisé, mais la requête utilise bien l'index :

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

SQL> create table myobjects as select * from all_objects;   Table created.   SQL> create index myindex on myobjects(object_id);   Index created.   SQL> exec dbms_stats.gather_table_stats(user,'MYOBJECTS')   PL/SQL procedure successfully completed.   SQL> var myvar number SQL> exec :myvar:=null   PL/SQL procedure successfully completed.   SQL> explain plan for 2 select count(*) 3 from myobjects 4 where object_id = nvl(:myvar,object_id);   Explained.   SQL> select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------   ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 4 | | 1 | SORT AGGREGATE | | 1 | 5 | | | 2 | CONCATENATION | | | | | |* 3 | FILTER | | | | | | 4 | INDEX FAST FULL SCAN| MYINDEX | 1 | 5 | 1 | |* 5 | FILTER | | | | | |* 6 | INDEX RANGE SCAN | MYINDEX | 1 | 5 | 1 | -----------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   3 - filter(:Z IS NULL) 5 - filter(:Z IS NOT NULL) 6 - access("MYOBJECTS"."OBJECT_ID"=:Z)   Note: cpu costing is off   21 rows selected.

---

Même chose pour les OR, on utilise bien l'index :

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

SQL> explain plan for 2 select count(*) 3 from myobjects 4 where (object_id = :myvar OR :myvar is NULL);   Explained.   SQL> select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT -----------------------------------------------------------------------   --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 | | 1 | SORT AGGREGATE | | 1 | 5 | | |* 2 | INDEX FAST FULL SCAN| MYINDEX | 3544 | 17720 | 3 | ---------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   2 - filter("MYOBJECTS"."OBJECT_ID"=TO_NUMBER(:Z) OR :Z IS NULL)   Note: cpu costing is off   SQL> explain plan for 2 select count(*) 3 from myobjects 4 where (object_id = :myvar OR (:myvar is NULL and object_id is null));   Explained.   SQL> select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------   -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 1 | | 1 | SORT AGGREGATE | | 1 | 5 | | |* 2 | INDEX RANGE SCAN | MYINDEX | 1 | 5 | 1 | --------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   2 - access("MYOBJECTS"."OBJECT_ID"=TO_NUMBER(:Z))   Note: cpu costing is off   15 rows selected.

---

Nicolas.

Ok, mais si tu fais :

Code:

---

1 2 3

select object_name from myobjects where object_id = nvl(:maVar,object_id);

---

il y a bien un table access full

Pour les or si tu fais :

Code:

---

1 2 3 4 5

select object_name from myobjects where ((object_id = 1 and :maVar=1) or (object_id = 2 and :maVar=2) or :maVar is null)

---

l'index n'est pas pris en compte (et c'est dans ce cas que semble être ypicot)

C'est sûr qu'avec des exemples simplistes, on peut démentir ce que j'ai dit plus haut, mais dans le contexte de ypicot, je pense avoir raison. Tu noteras néanmoins que je lui ai demandé des précisions car il me semble difficile de répondre à ce genre de question de perf sans avoir toutes les informations.

Citation:

---

Envoyé par plaineR

Ok, mais si tu fais :

Code:

---

1 2 3

select object_name from myobjects where object_id = nvl(:maVar,object_id);

---

il y a bien un table access full

---

Oui, mais il y a aussi utilisation de l'index :

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

SQL> EXPLAIN plan FOR 2 SELECT object_name 3 FROM myobjects 4 WHERE object_id = nvl(:myvar,object_id);   Explained.   SQL> SELECT * FROM TABLE(dbms_xplan.display);   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------   ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 70868 | 1591K| 60 | | 1 | CONCATENATION | | | | | |* 2 | FILTER | | | | | | 3 | TABLE ACCESS FULL | MYOBJECTS | 1 | 23 | 2 | |* 4 | FILTER | | | | | | 5 | TABLE ACCESS BY INDEX ROWID| MYOBJECTS | 1 | 23 | 2 | |* 6 | INDEX RANGE SCAN | MYINDEX | 1 | | 1 | -----------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   2 - filter(:Z IS NULL) 4 - filter(:Z IS NOT NULL) 6 - access("MYOBJECTS"."OBJECT_ID"=:Z)   Note: cpu costing is off   21 rows selected.

---

Citation:

---

Envoyé par plaineR

C'est sûr qu'avec des exemples simplistes, on peut démentir ce que j'ai dit plus haut,...

---

Qu'est-ce qu'ils ont mes exemples "simplistes" ? C'est avec des exemples "simplistes" que l'on montre et apprend le mieux, non ?
De plus, c'était tout simplement pour dire que la réponse n'était pas si évidente, trop de facteurs rentrent en compte.

Citation:

---

Envoyé par plaineR

Tu noteras néanmoins que je lui ai demandé des précisions car il me semble difficile de répondre à ce genre de question de perf sans avoir toutes les informations.

---

Certes.

Allez, restons cool ;)

Nicolas.

Citation:

---

Envoyé par NGasparotto

Qu'est-ce qu'ils ont mes exemples "simplistes" ?

---

Ils risquent de faire penser que dans tous les cas, avec les or, l'index est pris en compte et que les perf vont être meilleures. Cela peut être le cas, comme cela peut ne pas l'être.

Citation:

---

Envoyé par NGasparotto

C'est avec des exemples "simplistes" que l'on montre et apprend le mieux, non ?

---

Non, parce qu'avec des exemples simplistes on risque de créer des vérités qui n'en sont pas et des recettes toutes faites pour améliorer les perf qui s'avèreront désastreuses dans de nombreux cas.

Citation:

---

Envoyé par NGasparotto

De plus, c'était tout simplement pour dire que la réponse n'était pas si évidente, trop de facteurs rentrent en compte.

---

Entièrement d'accord avec toi, c'est pour cela que je me suis permis de te contredire et c'est également ce que j'ai signalé dès mon premier post.

Citation:

---

Allez, restons cool ;)

---

Aucun soucis ;) , le débat constructif n'empêche pas d'être cool ;)

Citation:

---

Envoyé par plaineR

Ils risquent de faire penser que dans tous les cas, avec les or, l'index est pris en compte et que les perf vont être meilleures. Cela peut être le cas, comme cela peut ne pas l'être.


Non, parce qu'avec des exemples simplistes on risque de créer des vérités qui n'en sont pas et des recettes toutes faites pour améliorer les perf qui s'avèreront désastreuses dans de nombreux cas.

---

Au fait, comment définir un exemple "simpliste" comme tu le dis ?
Il ne faudrait donc jamais donner d'exemple ? Tu n'en donnes donc jamais ?

Au contraire je pense que des exemples simples permettent d'illustrer facilement une situation ou une explication, mais ok, il ne faut pas généraliser et avoir à l'esprit que chaque situation est différente.

Nicolas.

Heuuu... on se calme, car ce n'était pas tout à fait ma question.
En gros, l'évaluation paresseuse (lazy evaluation) est une évaluation qui ne fait que ce qui est nécessaire.

Il s'agit d'une optimisation par ligne, alors que vous discutez d'une évaluation par colonne (car les index portent bien sur les colonnes, non ?)

Dans l'exemple cité par plaineR :

Code:

---

1 2 3

... (object_id = 1 AND :maVar=1) OR (object_id = 2 AND :maVar=2) OR :maVar IS NULL)

---

il y a 5 évaluations possibles.
Supposons que object_id vaille 2 et :maVar vaille 2.
Dans la première parenthèse, il n'est pas nécessaire d'évaluer :maVar=1 car avec le AND, le bloc est toujours faux. La parenthèse suivante retourne VRAI, et dans ce cas on peut s'arrêter et ne pas évaluer le dernier IS NULL.
On a donc fait 3 évaluations et non 5.

Ce genre d'optimisation peut être importante si un des termes est une fonction (genre :maVar=MaFonc(MonChamp)).

Par ailleurs, je vous rappelle que toutes vos solutions ont conduit à une amélioration très sensible des performances.

Sinon, à titre indicatif, seule une des colonnes du filtre est indexée, car la "ventilation" (cad le nombre de valeurs différentes par rapports au nombre total de valeurs) est très mauvaise (dans un cas extrème, 4 valeurs possibles sur 50 millions de lignes)

Sinon, voici le plan d'exécution pour le OR et le NVL
http://perso.orange.fr/yvan.picot/im...ecution_OR.png

http://perso.orange.fr/yvan.picot/im...cution_NVL.png

En vous remerciant pour le temps que vous avez consacré à ce problème.

Yvan

Citation:

---

Envoyé par NGasparotto

Au fait, comment définir un exemple "simpliste" comme tu le dis ?

---

Un exemple simpliste est un exemple qui ne montre qu'une face de la réalité. Et je le différencie d'un exemple simple qui lui illustre, de manière compréhensible par tous, les différents cas possibles.

Bien sûr que je donne des exemples, mais dans les cas où il peut y avoir contradiction, si je la connais, je la précise.

Pour conclure en aucun cas, mes remarques n'étaient des attaques à ton encontre, si tu l'as pris ainsi je m'en excuse, j'ai juste voulu préciser les choses pour que les personnes qui lisent / liront le poste ne fassent pas de généralisation hâtive.

Je pense dans ton cas que le or obtient des meilleurs résultats, le coût est moins élevé, le nombre de lignes moins important.

Pour ce qui est de l'évaluation paresseuse, je dirais à priori oui. Mais l'ordre des tests me semble important : soit les 2 requêtes suivantes :

Code:

---

1 2 3 4

select * from myobjects where (object_id = 1 and 1 = 2) or (object_id = f1 and 1 = 1);

---

ou

Code:

---

1 2 3 4

select * from myobjects where (object_id = 1 and 1 = 1) or (object_id = f1 and 1 = 2);

---

La lecture de la table qui est faite dans la fonction f1 est faite pour toutes les lignes de myobjects dans les 2 cas.

Par contre si on prend cette requête :

Code:

---

1 2 3 4

select * from myobjects where (1 = 1 and object_id = 1) or (1 = 2 and object_id = f1);

---

Dans ce cas, la table de la fonction f1 n'est pas lue.

Citation:

---

Envoyé par plaineR

Un exemple simpliste est un exemple qui ne montre qu'une face de la réalité. Et je le différencie d'un exemple simple qui lui illustre, de manière compréhensible par tous, les différents cas possibles.

---

Comprend pas vraiment. Comment un seul exemple ne peut-il pas n'être qu'une face de la réalité ?

Je remet cette discussion dans le contexte.
J'ai montré avec l'exemple que l'on pouvait arriver à utiliser l'index (c'était en réaction à ton post d'avant où tu avais dis que l'index n'était pas utilisé - j'ai bien mis ta citation il me semble), c'est tout. Après à chacun à s'arranger dans sa propre config, ce n'était pas une généralisation (et je ne l'ai pas dit).

Enfin, simpliste ou simple, c'est de la sémantique. Et très subjectif.

Nicolas.

Je vais compléter ce qu'à dit PlaineR. Je m'étais toujours posé la question : Est ce que le moteur SQL vérifie les conditions dans un ordre précis et s'arrête quand il peut.

Prenons la fonction

Code:

---

1 2 3 4 5 6 7

CREATE OR REPLACE FUNCTION wmc_f1 RETURN VARCHAR2 IS BEGIN dbms_output.put_line(TO_CHAR(SYSDATE, 'HH24:MI:SS')); RETURN 'X'; END;

---

Ce qui génère une sortie DBMS :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT * FROM DUAL WHERE (dummy = WMC_F1 AND 1 = 1) OR (1 = 1 AND dummy = 'X')   SELECT * FROM DUAL WHERE (1 = 2 AND dummy = 'X') OR (dummy = WMC_F1 AND 1 = 1)

---

Ce qui ne génère pas le DBMS (Le 3ème cas est surprenant)

Code:

---

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

SELECT * FROM DUAL WHERE (dummy = WMC_F1 AND 1 = 2) OR (1 = 1 AND dummy = 'X')   SELECT * FROM DUAL WHERE (1 = 1 AND dummy = 'X') OR (dummy = WMC_F1 AND 1 = 2)   SELECT * FROM DUAL WHERE (1 = 1 AND dummy = 'X') OR (dummy = WMC_F1 AND 1 = 1)

---

Ceci n'en génère qu'un

Code:

---

1 2 3

SELECT * FROM DUAL WHERE ('A' = WMC_F1 AND dummy = WMC_F1)

---

Ceci 2

Code:

---

1 2 3

SELECT * FROM DUAL WHERE ('X' = WMC_F1 AND dummy = WMC_F1)

---

Très intéressant cela McM. Peux-tu essayer les cas suivants (je n'ai pas SQL*Plus sous la main pour l'instant) :

Code:

---

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

SELECT * FROM dual WHERE (dummy = 'A' AND 1 = 2) OR (dummy = WMC_F2 AND 1 = 1);   SELECT * FROM dual WHERE (dummy = 'A' AND 1 = 1) OR (dummy = WMC_F2 AND 1 = 2);   SELECT * FROM dual WHERE (1 = 1 AND dummy = 'A') OR (1 = 2 AND dummy = WMC_F2 );

---

Avec WMC_F2 return 'B' par exemple.

Citation:

---

Envoyé par plaineR

Très intéressant cela McM. Peux-tu essayer les cas suivants (je n'ai pas SQL*Plus sous la main pour l'instant) :

---

DBMS généré

Code:

---

1 2 3 4

SELECT * FROM dual WHERE (dummy = 'A' AND 1 = 2) OR (dummy = WMC_F2 AND 1 = 1);

---

DBMS non généré

Code:

---

1 2 3 4 5 6 7 8 9

SELECT * FROM dual WHERE (dummy = 'A' AND 1 = 1) OR (dummy = WMC_F2 AND 1 = 2);   SELECT * FROM dual WHERE (1 = 1 AND dummy = 'A') OR (1 = 2 AND dummy = WMC_F2 );

---

Merci Mcm !

Il y a un truc étrange quand même : je n'ai pas les mêmes résultats que toi avec les tests que je fais...

- soit la fonction suivante :

Code:

---

1 2 3 4 5 6 7

create or replace function f1 return number is v_num number; begin select 1 into v_num from dual; return v_num; end; /

---

- soit la table suivante :

Code:

---

create table as select * from all_objects;

---

1. avec la requête :

Code:

---

1 2 3 4

select * from myobjects where (object_id = 1 and 1 = 1) or (object_id = f1 and 1 = 2);

---

Lorsque je fais un tkprof, il m'a exécuté la requête sur dual, une fois par ligne présentes dans myobjects
=> la fonction f1 est exécutée pour toutes les lignes

2. par contre cette requête :

Code:

---

1 2 3 4

SELECT * FROM myobjects WHERE (1 = 1 AND object_id = 1) OR (1 = 2 AND object_id = f1);

---

j'ai le même résultat que toi à savoir que la table dual n'est pas interrogée.
=> la fonction f1 n'est jamais exécutée.

Si quelqu'un a une explication...

Citation:

---

Envoyé par plaineR

je n'ai pas SQL*Plus sous la main pour l'instant

---

Maintenant ce n'est plus un problème :king:

http://www.developpez.net/forums/sho...d.php?t=287843 ;)

Citation:

---

Envoyé par Fred_D

Maintenant ce n'est plus un problème :king:

http://www.developpez.net/forums/sho...d.php?t=287843 ;)

---

:oops: J'étais passé à côté de l'info. Merci :ccool:

normal, je l'ai fait pour l'occasion ;)

Comme il est bien vu PlaineR ! :mouarf:

J'ai essayé de tester l'environnement. J'ai un souci :
J'ai créé la fonction WMC_F1
Par contre je n'arrive pas à afficher le dbms_output dans la console sql
C'est possible ?

Code:

---

select wmc_f1 from dual;

---

mais en effet, il n'affiche pas le dbms dans la fonction :koi: mais comme il ne sert à rien ce n'est pas un problème :mrgreen:

Note que contrairement à ce que j'ai compris :

Code:

---

1 2 3 4

SELECT * FROM DUAL WHERE (dummy = WMC_F1 AND 1 = 2) OR (1 = 1 AND dummy = 'X')

---

cette requête retourne bien un résultat.

je pense qu'Oracle n'affiche tout simplement pas le résultat PL/SQL dans une instruction SQL. Ceci fonctionne en revanche :

Code:

---

1 2 3 4 5 6

declare toto varchar2(1) := 'A'; begin toto := WMC_F1; end; /

---

J'ai retrouvé l'accès à SQL*Plus :D

Il semble que cela soit lié à la version de SQL*Plus. Avec ma version 10.1.0.4.2, il m'affiche les dbms_output, avec la version 9.2.0.4 il ne me les affiche pas.