NULL n'est pas égal à NULL et aucune ligne en résultat

Bonjour,

J'ai une table avec une colonne de type date.

Cette table a été initialisée avec des données.

Dans une procédure stockée, on passe un paramètre de type date pour l'utiliser dans une requête dans le code de la procédure stockée.

La requête est la suivante (volontairement tronquée) :

Code:

---

1 2 3

SELECT * FROM matable WHERE coldate = nvl(:parametredate,coldate)

---

Nous utilisons la fonction NVL pour gérer les cas ou le paramètre de type date de la procédure n'est pas valorisé, donc passé avec la valeur NULL.

Le problème maintenant c'est que NULL n'est pas égal à NULL et dans le cas du paramètre date envoyé à NULL, la requête ne retourne aucun résultat.

Une idée pour palier à ce problème ?

Merci d'avance

Qui vous dit que coldate n'est pas NULL également ?

Citation:

---

Envoyé par SheikYerbouti

Qui vous dit que coldate n'est pas NULL également ?

---

Effectivement je me suis mal expliqué :oops:(après relecture), le problème se pose pour les enregistrements pour lesquels la colonne coldate est valorisée à NULL, je souhaiterais que ma requête SQL me renvoie ces enregistrements.

J'ai bien pensé à tester la nullité du paramètre avant de construire la requête mais je me demandais si il n'y avait pas une autre solution ?

Code:

---

1 2 3

SELECT * FROM matable WHERE coldate IS NULL;

---

Citation:

---

Envoyé par SheikYerbouti

SELECT *
FROM matable
WHERE coldate IS NULL

---

En fait non, en une seule requête je voudrais gérer les cas :
- Le paramètre date est NULL
- Le paramètre date est autre que NULL
- La colonne coldate est à NULL
- La colonne coldate est à autre que NULL

Pour le moment avec ma requête actuelle :

Code:

---

1 2 3

SELECT * FROM matable WHERE coldate = nvl(:parametredate,coldate)

---

Si matable contient les données suivantes :

Code:

---

1 2 3 4 5 6

coldate 12/08/2007 NULL NULL 15/09/2005

---

En passant 15/09/2005 en paramètre, j'obtiens la ligne correspondante (la 4ème).
En passant NULL en paramètre, je n'obtiens aucun résultat.

C'est normal car ma requête revient à faire dans le cas d'un paramètre à NULL :

Code:

---

1 2 3

SELECT * FROM matable WHERE NULL = NULL

---

Je souhaiterais donc pouvoir gérer tous les cas en un seule requête, est ce possible ?

Code:

---

1 2 3

SELECT * FROM matable WHERE (coldate is null and :parametredate is null) or (coldate = :parametredate)

---

J'essaierai demain.

Cette solution est elle très pénalisant pour le temps de réponse ?

Citation:

---

Envoyé par elitost

Cette solution est elle très pénalisant pour le temps de réponse ?

---

Oui, l'index sur coldate ne sera pas utilisé (existe-t-il?) pour la condition "IS NULL", alors: FULL SCAN.

C'est donc peut être mieux de construire le select dynamiquement (Si param data pas NULL alors ajouter le filtre au select Fin Si) et dans le cas du paramètre valorisé à NULL d'avoir uniquement la requête :

Code:

---

select * from matable

---

A moins qu'il y ai d'autres solutions pour arriver à solutionner le souci de cette requête ?

Citation:

---

Envoyé par DAB.cz

Code:

---

1 2 3

SELECT * FROM matable WHERE (coldate is null and :parametredate is null) or (coldate = :parametredate)

---

---

c'est à mon sens la solution la plus élégante !


quand à la performance, on peut imaginer (ce n'est pas le goût de tout le monde) de te pas avoir de null dans la table, mais une date du style 31-DEC-9999. Cela résout le problème de performance.

une autre approche serait d'indexer les nulls et d'utiliser UNION ALL

Code:

---

1 2 3 4 5 6 7 8 9 10

SQL> var parametredate number SQL> create table matable(coldate number, x number); Table created. SQL> insert into matable select nullif(rownum,1),1 from all_objects where rownum<100; 99 rows created. SQL> create index i on matable(coldate,1) compute statistics; Index created. SQL> analyze table matable compute statistics; Table analyzed.

---

Code:

---

1 2 3

select * FROM matable WHERE coldate is null and :parametredate is null union all select * FROM matable WHERE coldate = :parametredate;

---

Code:

---

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

------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 8 | 4 (50)| | 1 | UNION-ALL | | | | | |* 2 | FILTER | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| MATABLE | 1 | 4 | 2 (0)| |* 4 | INDEX RANGE SCAN | I | 1 | | 1 (0)| | 5 | TABLE ACCESS BY INDEX ROWID | MATABLE | 1 | 4 | 2 (0)| |* 6 | INDEX RANGE SCAN | I | 1 | | 1 (0)| ------------------------------------------------------------------------------ 2 - filter(:PARAMETREDATE IS NULL) 4 - access("COLDATE" IS NULL) 6 - access("COLDATE"=TO_NUMBER(:PARAMETREDATE))

---

ou bien sûr de créer 2 selects différents, un pour les null, un pour les non-null

Ok, je vous remercie pour toutes ces informations qui devraient maintenant suffire pour choisir telle ou telle solution.

Merci encore et bonne journée à tous.

Citation:

---

Envoyé par laurentschneider

Code:

---

create index i on matable(coldate,1)

---

---

indexer les nulls, l'idée formidable et utile :king:

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

SQL> create bitmap index b_i on matable (coldate) 2 /   Index crÚÚ.   SQL> SQL> analyze table matable compute statistics;   Table analysÚe.   SQL> /   Table analysÚe.   SQL> SQL> SELECT * 2 FROM matable 3 WHERE (coldate IS NULL AND :parametredate IS NULL) OR (coldate = :parametredate) 4 /   Plan d'exÚcution ---------------------------------------------------------- Plan hash value: 2725285456   ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID | MATABLE | 1 | 4 | 3 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | | 3 | BITMAP OR | | | | | | |* 4 | BITMAP INDEX SINGLE VALUE| B_I | | | | | |* 5 | BITMAP INDEX SINGLE VALUE| B_I | | | | | ----------------------------------------------------------------------------------------

---

Mais ça peut côuter cher.

bitmap index sur une date? mauvais plan :(

Citation:

---

Envoyé par laurentschneider

bitmap index sur une date? mauvais plan :(

---

Peut tu expliquer, STP ?

ben en general une date est une colonne a forte cardinalite, non?

Bon c'est vrai que si c'est CONTRACT_END_DATE et que les contrats se terminent tous au 31 decembre on pourrait l'imaginer.

D’accorde Laurent, j’ai eu peur que c’était une hérésie même de penser à faire un bitmap index sur une colonne de type date.

A propos,

Citation:

---

...
(ce n'est pas le goût de tout le monde) de te pas avoir de null dans la table, mais une date du style 31-DEC-9999. Cela résout le problème de performance.

---

j’ai lu quelque part que ce n’est pas forcement une affaire de goût mais que ça pourrait être un problème de performance d’utiliser cette solution. Puis-je avoir ton point de vue sur ce sujet ?

si tu as beaucoup de NULLs, il n'est peut-etre pas necessaire de les indexer, vu qu'un WHERE X IS NULL te retournera une bonne partie de la table. Cependant si tu utilises des requetes du style WHERE D > SYSDATE OR D IS NULL, alors de remplacer NULL par 9999-12-31 sera benefique.

C'est vrai que d'avoir des valeurs artificielles comme 9999-12-31 c'est pas terrible d'un pont de vue design

Merci Laurent pour ta patience.

En fin de compte y a-t-il une différence entre

Code:

---

1 2	CREATE INDEX i ON matable(coldate,1)

---

Et

Code:

---

1 2	CREATE INDEX i ON matable(Nvl(coldate,1))

---

Ne pense tu pas que c’est plus clair d’utiliser NVL ?