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

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

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 ?

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

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

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

[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

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

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