Discussion :

[ypicot]

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 :

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

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

[NGasparotto]

Est-ce que cela peut t'aider ?

Code :

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

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

Nicolas.

[ypicot]

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

[plaineR]

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

Code :

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

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

[McM]

Est-ce que cela peut t'aider ?

Code :

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

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 :

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

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

[NGasparotto]

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.

[McM]

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

[orafrance]

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

Code :

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

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 = si possible

[ypicot]

Merci beacoup à vous tous pour ces approfondissements !

plaineR, ta solution (qui marche très bien, je l'ai testée ) 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

[plaineR]

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]

[NGasparotto]

...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 :

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
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 :

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
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.

[plaineR]

Ok, mais si tu fais :

Code :

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

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 :

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

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.

[NGasparotto]

Ok, mais si tu fais :

Code :

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

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 :

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
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.

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.

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.

[plaineR]

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.

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.

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.

Allez, restons cool

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

[NGasparotto]

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.

[ypicot]

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 :

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

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




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

Yvan

[plaineR]

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.

[plaineR]

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 :

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

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

ou

Code :

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

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 :

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

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.

[NGasparotto]

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.

[McM]

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 :

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

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 :

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

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 :

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

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 :

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

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

Ceci 2

Code :

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

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