Discussion :

[elitost]

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

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

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

[SheikYerbouti]

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

[elitost]

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

Effectivement je me suis mal expliqué (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 ?

[SheikYerbouti]

Code :

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

1
2
3
SELECT *
FROM matable
WHERE coldate IS NULL;

[elitost]

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

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

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

Si matable contient les données suivantes :

Code :

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

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

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

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 ?

[DAB.cz]

Code :

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

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

[elitost]

J'essaierai demain.

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

[DAB.cz]

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.

[elitost]

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

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

select * from matable

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

[laurentschneider]

Code :

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

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.

[laurentschneider]

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

Code :

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

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 :

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

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

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

[elitost]

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.

[DAB.cz]

Code :

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

create index i on matable(coldate,1)

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

[mnitu]

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

[laurentschneider]

bitmap index sur une date? mauvais plan

[mnitu]

bitmap index sur une date? mauvais plan

Peut tu expliquer, STP ?

[laurentschneider]

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.

[mnitu]

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,

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

[laurentschneider]

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

[mnitu]

Merci Laurent pour ta patience.

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

Code :

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

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

Et

Code :

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

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

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