Requête à optimiser

Tiens au passage, j'ai un truc qui m'énerve pas mal :

Code:

---

1 2 3 4

SELECT * FROM t WHERE col1 IS NULL

---

=> l'index est utilisé

Code:

---

1 2 3 4

SELECT * FROM t WHERE col1 = 2

---

=> l'index est utilisé

Code:

---

1 2 3 4 5 6 7 8

SELECT * FROM t WHERE col1 = 2 UNION ALL SELECT * FROM t WHERE col1 IS NULL

---

=> l'index est utilisé

Code:

---

1 2 3 4 5 6 7 8

SELECT * FROM t WHERE col1 IS NULL or col1 = 2;   SELECT * FROM t WHERE coalesce(col1, 2) = 2

---

=> l'index n'est pas utilisé...

Quelqu'un sait pourquoi ?

quand t'applique une fonction à ta colonne indexé => l'index est inhibé
Pour l'utilisation des OR sur des champs indexés vaut mieux utiliser des indexes Bitmap => Le FTS est moins couteux que de faire un Full index scan et d'accéder à chaque ligne de la table via le rowid

Hmmm, j'aurais espéré une exception sur COALESCE...

Par contre pour l'histoire des OR, je suis le seul à trouver ça débile ??

J'ai fait des petits supplémentaires, juste pour déconner :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

explain plan for select /*+use_concat*/ * from testnull a where nb = 2 or nb = 1 --where nb between 1 and 2 ; select * from table(dbms_xplan.display) ;   -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 150 | 4426 | | 1 | CONCATENATION | | | | | |* 2 | TABLE ACCESS FULL | TESTNULL | 1 | 75 | 2213 | |* 3 | TABLE ACCESS FULL | TESTNULL | 1 | 75 | 2213 | --------------------------------------------------------------------

---

C'est quand même magique qu'il s'obstine à pas prendre l'index !
Par ailleurs, en utilisant between 1 AND 2, l'index est pris.

Enfin bref, si je veux sélectionner plusieurs valeurs d'un index, je dois donc faire des UNION ALL pour chaque valeur...
(j'ai envie de me pendre)

Citation:

---

Envoyé par pacmann

...
Quelqu'un sait pourquoi ?

---

Salut pacmann. Peux tu ajouter le script de la création de la table t et de l'index ?

Salut Mnitu !

Code:

---

1 2 3 4 5 6 7 8 9 10 11

create table testnull as select case level when 1 then null else level end as nb, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' as fic from dual connect by level < 1000000;   create unique index iitestnull on testnull(nb, 1) ; create index iitestnull2 on testnull(nb, 5) ;   exec dbms_stats.gather_table_stats(NULL, 'TESTNULL');

---

J'ai ajouté le unique pour voir si ça changeait quelque chose... mais non.
Au passage : Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

Ah tiens, j'avais raté ta réponse sur la première page, j'en profite :

Citation:

---

J'avais pensé plutôt aux contraintes fonctionnelles (inconnues) qu'à l'exécution de la requête.

---

En fait, je ne vois pas ce que peut changer le ALL quand on utilise IN... et je suis assez traumatisé par le changement de plan de ton exemple !

Salut pacmann,

Il semble que dans ce cas il faut employer un index basée plutôt sur la fonction nvl.

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

mni@DIANA> create index ix on testnull (nvl(nb,-1)) 2 /   Index crÚÚ. mni@DIANA> select * 2 from testnull 3 where nvl(nb,-1) = -1 or nvl(nb,-1) = 2 4 ;   NB FIC ---------- --------------------------------------------------------------------- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa     Plan d'exÚcution ---------------------------------------------------------- Plan hash value: 722505985   ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 760 | 3 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| TESTNULL | 10 | 760 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IX | 4 | | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   3 - access(NVL("NB",(-1))=(-1) OR NVL("NB",(-1))=2)     Statistiques ---------------------------------------------------------- 1 recursive calls 0 db block gets 7 consistent gets 4 physical reads 0 redo size 580 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed

---

T'as remarque est juste mais, j'utiliserais toujours UNION ALL si je sais que fonctionnement les ensembles résultats sont disjointes sinon j'utiliserais UNION

Citation:

---

Envoyé par mnitu

j'utiliserais toujours UNION ALL si je sais que fonctionnement les ensembles résultats sont disjointes sinon j'utiliserais UNION

---

Vu que c'est pour remplacer IN, je pense que quoi il arrive, c'est bien UNION ALL ;)