Discussion :

[Beluz]

Bonjour à tous,

Dans le cadre de mon boulot, je suis amené à devoir comparer des attributs (jointure ou historisation SCD2) pouvant contenir la valeur null.

Certaines requêtes pouvants être relativement volumineuses, on a rapidement écarté la solution d'inclure un "or is/not is null"

Exemple (simple):

Je dois comparer C1 et C2 et c'est 2 champs peuvent être nulls.

... Where C1 = C2 or (C1 is null and C2 is null)

La seconde solution testée a été d'utiliser la fonction NVL qui permet de remplacer les nulls par une valeur afin d'effectuer des comparaisons.

Le problème dans ce cas de figure c'est que la valeur de remplacement peut-elle être significative.

Exemple :

Numérique => NVL(C1,0) = NVL(C2,0)
Caractère => NVL(C1, ' ') = NVL(C2,' ')

Cependant le '0' ou le ' ' peuvent représenter quelque chose ce qui créé des conflits ou empêche de tenir compte de certains changements dans le cadre d'une historisation.

Exemple :

Si C1 (numérique) passe de null à 0, il est impossible de déterminer qu'il y a eu un changement car NVL(C1,0) ramène dans les 2 cas 0.

La 3ème et dernières solution a été d'utiliser la fonction :

DBMS_UTILITY.get_hash_value (C1, 1, POWER (2, 16) - 1)

Cette fonction à la base prévue pour du cryptage, permet d'attribuer une valeur numérique à tout valeur rencontrée (y compris les nulls). Je pensais tenir-là la bonne solution mais j'ai vite déschanté lorsque je me suis rendu compte que lors de requêtes assez importantes (requêtes imbriquées, not exist,...) on arrivait avec un temps d'exécution irréaliste (pour les plus connaisseur on arrivait via un plan d'exécution à 41.000.000.000...).

Je sais que ma question est relativement "high-level" mais j'espère quand-même que certains experts pourraient venir apporter leurs contributions...

Merci d'avance

[al1_24]

La solution avec NVL ne peut être utilisée qu'en spécifiant des valeurs non significatives en cas de NULL.
Si toutes les valeurs acceptées par le type de la colonne à tester peuvent être significatives, seule la solution du OR..NULL est valide.
Rien ne t’empêche de créer une fonction pour écrire cette comparaison de manière plus concise

[Mohamed.Houri]

Je ne sais pas si j'ai bien compris votre besoin mais j'opterai (avec précaution car je n'ai pas trop bien saisi votre demande) à quelque chose comme ceci

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
 
SQL> select * from emp where
  2  comm = sal;
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 allen      clerk           7902 20100330 00:00:00         25         25         20
 
SQL>
SQL> select * from emp where
  2  comm is null;
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
         3 Mohamed    Oracle          7654 20120808 15:05:19        500                    20
 
SQL>
SQL> select * from emp where
  2  sal is null;
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7788 scott      analyst         7566 19821209 00:00:00                     5         20
 
SQL> select * from emp where lnnvl(comm != sal);
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 allen      clerk           7902 20100330 00:00:00         25         25         20
      7788 scott      analyst         7566 19821209 00:00:00                     5         20
         3 Mohamed    Oracle          7654 20120808 15:05:19        500                    20

[mnitu]

La fonction non documentée SYS_OP_MAP_NONNULL permet ce type de comparaison. Mais, il est fortement déconseillé voir une folie d’utiliser des fonctions non-documentée dans un code pérenne.

[Beluz]

La solution avec NVL ne peut être utilisée qu'en spécifiant des valeurs non significatives en cas de NULL.
Si toutes les valeurs acceptées par le type de la colonne à tester peuvent être significatives, seule la solution du OR..NULL est valide.
Rien ne t’empêche de créer une fonction pour écrire cette comparaison de manière plus concise

Bonjour al1_24,

Avant tout, merci pour ta réactivité.

Une telle fonction serait réalisable mais le fait de repasser par une fonction n'est-ce pas problematique ? Je pensais en effet que le problème de la fonction "get_hash_value" et de sa lenteur était justement lié à l'appel systématique d'une fonction qui ne travaille pas en mémoire. En d'autres mots, je pensais que lors d'une jointure entre des tables de plusieurs millions de records, oracle effectuait un appel à cette fonction à chaque comparaison ce qui provoquait un délai de traitement énorme. Contrairement (si mes souvenirs sont bons) au NVL qui lui traitait ça en mémoire.

Ne vais-je pas rencontrer ce même problème si je passe par une fonction sql autre que celles fournies par défaut ?

[Beluz]

La fonction non documentée SYS_OP_MAP_NONNULL permet ce type de comparaison. Mais, il est fortement déconseillé voir une folie d’utiliser des fonctions non-documentée dans un code pérenne.

Bonjour mnitu,

Merci d'intervenir.
Je me suis effectivement penché sur cette fonction qui semble à priori correspondre à mes attentes (en tout cas après quelques tests) mais malheureusement cette fonction n'étant pas "officielle" je ne peux me permettre de l'implémenter dans un cadre professionnel avec un risque qu'elle ne soit plus supportée dans une version future... ou alors ce serait faire passer la patate chaude au suivant...

[Beluz]

Je ne sais pas si j'ai bien compris votre besoin mais j'opterai (avec précaution car je n'ai pas trop bien saisi votre demande) à quelque chose comme ceci

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
 
SQL> select * from emp where
  2  comm = sal;
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 allen      clerk           7902 20100330 00:00:00         25         25         20
 
SQL>
SQL> select * from emp where
  2  comm is null;
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
         3 Mohamed    Oracle          7654 20120808 15:05:19        500                    20
 
SQL>
SQL> select * from emp where
  2  sal is null;
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7788 scott      analyst         7566 19821209 00:00:00                     5         20
 
SQL> select * from emp where lnnvl(comm != sal);
 
     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 allen      clerk           7902 20100330 00:00:00         25         25         20
      7788 scott      analyst         7566 19821209 00:00:00                     5         20
         3 Mohamed    Oracle          7654 20120808 15:05:19        500                    20

Bonjour Mohamed.Houri,

la fonction LNNVL renvoi une condition inversée, en résumé :

TRUE => FALSE
FALSE => TRUE
UNKNOWN => TRUE

Du coup si on suis cette logique,

Si on compare 'A' avec 'A' il nous retourne FALSE

Si on compare 'A' avec 'B' il nous retourne TRUE

Si on compare null avec null il nous retourne TRUE

Mais du coup, il impossible de distinguer lors d'une historisation un cas de figure ou la comparaison est différente ou bien si il compare 2x la valeur null (qui elle ne nécessite pas d'intervention car les valeurs n'ont pas changées...)

[doubleDu]

Bonjour,

Comme al1_24 l'a dit :

La solution avec NVL ne peut être utilisée qu'en spécifiant des valeurs non significatives en cas de NULL.
Si toutes les valeurs acceptées par le type de la colonne à tester peuvent être significatives, seule la solution du OR..NULL est valide.
Rien ne t’empêche de créer une fonction pour écrire cette comparaison de manière plus concise

J'ajouterai que dans l'exemple que tu as fournit apparemment les valeurs des deux colonnes sont toujours positives, pourquoi ne pas utiliser une valeur négative ?

Autre point, pour des questions de lenteurs, tu peux créer des index sur des fonctions.
exemple :

Code :

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

create index emp_null_sal on emp (nvl(sal,0));

[Beluz]

Ah désolé, c'est vrai que dans l'exemple, tu n'utilise pas une égalité mais un "différent"... ça demande réflexion du coup...

[Beluz]

Bonjour,

Comme al1_24 l'a dit :



J'ajouterai que dans l'exemple que tu as fournit apparemment les valeurs des deux colonnes sont toujours positives, pourquoi ne pas utiliser une valeur négative ?

Autre point, pour des questions de lenteurs, tu peux créer des index sur des fonctions.
exemple :

Code :

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

create index emp_null_sal on emp (nvl(sal,0));

Bonjour doubleDu,

Je travaille pour une assurance et les attributs numériques peuvent dans certains cas représenter des montants qui peuvent être positifs ou négatifs (prime ou prestation)

Les index, eux, sont malheureusement déjà bien en place...

[Beluz]

Bonjour Mohamed.Houri,

la fonction LNNVL renvoi une condition inversée, en résumé :

TRUE => FALSE
FALSE => TRUE
UNKNOWN => TRUE

Du coup si on suis cette logique,

Si on compare 'A' avec 'A' il nous retourne FALSE

Si on compare 'A' avec 'B' il nous retourne TRUE

Si on compare null avec null il nous retourne TRUE

Mais du coup, il impossible de distinguer lors d'une historisation un cas de figure ou la comparaison est différente ou bien si il compare 2x la valeur null (qui elle ne nécessite pas d'intervention car les valeurs n'ont pas changées...)

Après réflexion, cette solution ne sera pas bonne car il est impossible de distinguer une comparaison de type :

A = null (qui retourne la valeur correpsondant à inconnu)
à
null = null (qui retourne la valeur correpsondant à inconnu)

Cependant dans le premier cas de figure il y a une modification qui nécessite une historisation mais pas dans le 2ème...

Quelqu'un aurait-il une corde sous la main ??

[al1_24]

Je travaille pour une assurance et les attributs numériques peuvent dans certains cas représenter des montants qui peuvent être positifs ou négatifs (prime ou prestation)

Est-ce que les valeurs extrêmes de ces colonnes numériques sont souvent utilisées ?
Par exemple -9999 sur un NUMBER(4, 0)

[Beluz]

Il y a toujours un risque vu que cela peut représenter un montant mais je dois avouer que c'est effectivement ma roue de secours, la moins mauvaise des solutions...
Utiliser des valeurs extrêmes numériques et des caractère "spéciaux" (voir si compatibilité des différents code ASCII au niveau des DB) pour essayer de réduire au maximum le risque de conflit.

[al1_24]

Au niveau chaines de caractères, tu as fort peu de chances de rencontrer souvent '§' par exemple.

[doubleDu]

Ou alors, dans le cas extrême, mais attention aux performances... convertir tes numériques en char et utiliser une lettre comme valeur NULL. Mais cette solution n'est valable uniquement pour les numériques.

Code :

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

1
2
3
4
5
6
 
SELECT * 
FROM emp 
WHERE
nvl(to_char(comm), 'A') = nvl(to_char(sal), 'A')
;

Dans le cas des chaînes de caractères, j'utilise en général des # et des @...

[pachot]

Bonjour,

Un alternative pour le NVL: nvl(a,cast('NaN' as binary_double))

Sinon, voici quelques variations pour faire votre choix:

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
select a,b
  ,case when  a=b   then 'true' when not ( a=b ) then 'false' else 'null' end  "a=b"
  ,case when  a=b or a is null and b is null then 'true' when not ( a=b or a is null and b is null ) then 'false' else 'null' end  "a=b or a is null and b is null"
  ,case when  a=b or a is null or b is null then 'true' when not ( a=b or a is null or b is null ) then 'false' else 'null' end  "a=b or a is null or b is null"
  ,case when  not lnnvl(a=b)   then 'true' when not ( not ( lnnvl(a=b) ) ) then 'false' else 'null' end  "not lnnvl(a=b)"
  ,case when  nvl(a,cast('NaN' as binary_double))=nvl(b,cast('NaN' as binary_double))   then 'true' when not ( nvl(a,cast('NaN' as binary_double)) =nvl(b,cast('NaN' as binary_double)) ) then 'false' else 'null' end  "nvl(a,NaN)=nvl(b,NaN)"
  ,case when  sys_op_map_nonnull(a)=sys_op_map_nonnull(b)   then 'true' when not (sys_op_map_nonnull(a)=sys_op_map_nonnull(b)) then 'false' else 'null' end  "sys_op_map_nonnull"
from (
  select 1 a,1 b from dual union all select null,null from dual union all select 1,null from dual union all select 1,2 from dual
)
/
 
A B a=b   a=b or a is null and b is null a=b or a is null or b is null not lnnvl(a=b) nvl(a,NaN)=nvl(b,NaN) sys_op_map_nonnull
- - ----- ------------------------------ ----------------------------- -------------- --------------------- ------------------
1 1 true  true                           true                          true           true                  true               
    null  true                           true                          false          true                  true               
1   null  null                           true                          false          false                 false              
1 2 false false                          false                         false          false                 false

Vous voyez que Where C1 = C2 or (C1 is null and C2 is null) est différent de ce que donne NVL ou LNNVL

Cordialement,
Franck.

[Beluz]

Bonjour,

Un alternative pour le NVL: nvl(a,cast('NaN' as binary_double))

Sinon, voici quelques variations pour faire votre choix:

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
select a,b
  ,case when  a=b   then 'true' when not ( a=b ) then 'false' else 'null' end  "a=b"
  ,case when  a=b or a is null and b is null then 'true' when not ( a=b or a is null and b is null ) then 'false' else 'null' end  "a=b or a is null and b is null"
  ,case when  a=b or a is null or b is null then 'true' when not ( a=b or a is null or b is null ) then 'false' else 'null' end  "a=b or a is null or b is null"
  ,case when  not lnnvl(a=b)   then 'true' when not ( not ( lnnvl(a=b) ) ) then 'false' else 'null' end  "not lnnvl(a=b)"
  ,case when  nvl(a,cast('NaN' as binary_double))=nvl(b,cast('NaN' as binary_double))   then 'true' when not ( nvl(a,cast('NaN' as binary_double)) =nvl(b,cast('NaN' as binary_double)) ) then 'false' else 'null' end  "nvl(a,NaN)=nvl(b,NaN)"
  ,case when  sys_op_map_nonnull(a)=sys_op_map_nonnull(b)   then 'true' when not (sys_op_map_nonnull(a)=sys_op_map_nonnull(b)) then 'false' else 'null' end  "sys_op_map_nonnull"
from (
  select 1 a,1 b from dual union all select null,null from dual union all select 1,null from dual union all select 1,2 from dual
)
/
 
A B a=b   a=b or a is null and b is null a=b or a is null or b is null not lnnvl(a=b) nvl(a,NaN)=nvl(b,NaN) sys_op_map_nonnull
- - ----- ------------------------------ ----------------------------- -------------- --------------------- ------------------
1 1 true  true                           true                          true           true                  true               
    null  true                           true                          false          true                  true               
1   null  null                           true                          false          false                 false              
1 2 false false                          false                         false          false                 false

Vous voyez que Where C1 = C2 or (C1 is null and C2 is null) est différent de ce que donne NVL ou LNNVL

Cordialement,
Franck.

Bonjour Franck,

Merci pour cette réponse.

La solution du nvl(a,cast('NaN' as binary_double)) semble pas mal du tout.

Je pense que je vais me porter sur cette solution, elle reprend les avantages du nvl sans rencontrer les inconvénients des valeurs significatives.

Un grand merci à toi et tout ceux qui sont intervenu sur ce problème .

Bonne journée !!