Contraintes NOT NULL

Pour indexer une condition IS NULL dans la base de données Oracle, l'index doit avoir une colonne qui ne peut jamais être NULL.

Cela étant dit, il n'est pas suffisant qu'il n'y ait pas d'entrées NULL. La base de données doit être sûre qu'il ne peut jamais y avoir d'entrée NULL. Dans le cas contraire, la base de données va supposer que la table contient des lignes qui ne sont pas dans l'index.

L'index suivant est utilisable sur la requête seulement si la colonne NOM dispose d'une contrainte NOT NULL :

DROP INDEX emp_ddn;
CREATE INDEX emp_ddn_nom
          ON employes (date_de_naissance, nom);

SELECT *
  FROM employes
 WHERE date_de_naissance IS NULL

Supprimer la contrainte NOT NULL rend l'index inutilisable pour cette requête :

ALTER TABLE employes MODIFY nom NULL;

SELECT *
  FROM employes
 WHERE date_de_naissance IS NULL

En dehors des contraintes NOT NULL, la base de données sait aussi que les expressions constantes comme dans la section précédente ne peuvent pas devenir NULL.

Néanmoins, un index sur une fonction définie par un utilisateur n'impose pas une contrainte NOT NULL sur l'expression de l'index :

CREATE OR REPLACE FUNCTION boitenoire(id IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN
   RETURN id;
END;

DROP INDEX emp_ddn_nom;
CREATE INDEX emp_ddn_bn 
    ON employes (date_de_naissance, boitenoire(id_employe));

SELECT *
  FROM employes
 WHERE date_de_naissance IS NULL;

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYES  |    1 |  477 |
----------------------------------------------------

Le nom de la fonction, BOITENOIRE, met l'accent sur le fait que l'optimiseur n'a pas la moindre idée de ce que fait la fonction (voir « Recherche insensible à la casse en utilisant UPPER ou LOWER »). Nous pouvons voir que la fonction passe la valeur en entrée directement, sans transformation, mais pour la base de données c'est juste une fonction qui renvoie une valeur. La propriété NOT NULL du paramètre est perdue. Bien que l'index doive avoir toutes les lignes, la base de données ne le sait pas, donc elle ne peut pas utiliser l'index pour la requête.

Si vous savez que la fonction ne renvoie jamais NULL, comme dans cet exemple, vous pouvez changer la requête pour qu'elle reflète cette information :

SELECT *
  FROM employes
 WHERE date_de_naissance IS NULL
   AND boitenoire(id_employe) IS NOT NULL;

-------------------------------------------------------------
|Id |Operation                   | Name       | Rows | Cost |
-------------------------------------------------------------
| 0 |SELECT STATEMENT            |            |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYES   |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DDN_BN |    1 |    2 |
-------------------------------------------------------------

La condition supplémentaire dans la clause where est toujours vraie et, du coup, ne change pas le résultat. Néanmoins, la base de données Oracle reconnaît que vous pouvez récupérer des lignes qui doivent être dans l'index par définition.

Malheureusement, il n'existe pas de moyens pour indiquer qu'une fonction ne renvoie jamais de NULL mais vous pouvez déplacer l'appel de fonction dans une colonne virtuelle (depuis la version 11g) et placez une contrainte NOT NULL sur cette colonne.

ALTER TABLE employes ADD expression_bn
      GENERATED ALWAYS AS (boitenoire(id_employe)) NOT NULL;

DROP   INDEX emp_ddn_bn;
CREATE INDEX emp_ddn_bn 
    ON employes (date_de_naissance, expression_bn);

SELECT *
  FROM employes
 WHERE date_de_naissance IS NULL;

-------------------------------------------------------------
|Id |Operation                   | Name       | Rows | Cost |
-------------------------------------------------------------
| 0 |SELECT STATEMENT            |            |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYES   |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DDN_BN |    1 |    2 |
-------------------------------------------------------------

La base de données Oracle sait que certaines fonctions internes renvoient seulement NULL si NULL est fourni en entrée.

DROP INDEX emp_ddn_bn;

CREATE INDEX emp_ddn_nommaj
    ON employees (date_de_naissance, upper(nom));

SELECT *
  FROM employes
 WHERE date_de_naissance IS NULL;

----------------------------------------------------------
|Id |Operation                   | Name           | Cost |
----------------------------------------------------------
| 0 |SELECT STATEMENT            |                |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYES       |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DDN_NOMMAJ |    2 |
----------------------------------------------------------

La fonction UPPER conserve la propriété NOT NULL de la colonne NOM. Néanmoins, supprimer cette contrainte rend l'index inutilisable :

ALTER TABLE employes MODIFY nom NULL;

SELECT *
  FROM employes
 WHERE date_de_naissance IS NULL;

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYES  |    1 |  477 |
----------------------------------------------------