Indexer NULL

La base de données Oracle n'inclut pas les lignes dans un index si toutes les colonnes indexées sont NULL. Cela signifie que chaque index est un index partiel, comme si la clause where suivante était ajoutée :

CREATE INDEX idx
          ON tbl (A, B, C, ...)
       WHERE A IS NOT NULL
          OR B IS NOT NULL
          OR C IS NOT NULL
             ...;         

Considérez l'index EMP_DDN. Il a une seule colonne : DATE_DE_NAISSANCE. Une ligne qui n'a pas de valeur pour la colonne DATE_DE_NAISSANCE n'est pas ajoutée dans cet index.

INSERT INTO employes ( id_supplementaire, id_employe
                     , prenom           , nom
                     , numero_telephone)
              VALUES ( ?, ?, ?, ?, ? );   

La requête insert n'initialise pas la valeur de la colonne DATE_DE_NAISSANCE, donc par défaut elle vaut NULL. De ce fait, l'enregistrement n'est pas ajouté dans l'index EMP_DDN. En conséquence, l'index ne peut pas supporter une requête qui recherche les enregistrements pour lesquels la colonne DATE_DE_NAISSANCE est NULL :

SELECT prenom, nom
  FROM employes
 WHERE date_de_naissance IS NULL

Néanmoins, l'enregistrement est inséré dans un index concaténé si au moins une colonne de l'index n'est pas NULL :

CREATE INDEX demo_null
          ON employes (id_supplementaire, date_de_naissance);

La ligne créée ci-dessus est ajoutée dans l'index car ID_SUPPLEMENTAIRE n'est pas NULL. Du coup, cet index peut supporter une requête récupérant les lignes pour lesquelles tous les employés sont d'une société spécifique mais n'ont pas de valeur sur DATE_DE_NAISSANCE :

SELECT prenom, nom
  FROM employes
 WHERE id_supplementaire = ?
   AND date_de_naissance IS NULL

Notez que l'index couvre complètement la clause where ; tous les filtres sont utilisés comme prédicats d'accès lors de l'opération INDEX RANGE SCAN.

Nous pouvons étendre ce concept à la requête originale pour trouver tous les enregistrements où DATE_DE_NAISSANCE est NULL. Pour cela, la colonne DATE_DE_NAISSANCE doit être la colonne la plus à gauche dans la définition de l'index pour qu'elle puisse être utilisée comme prédicat d'accès. Bien que nous n'ayons pas besoin d'une deuxième colonne dans l'index pour cette requête, nous ajoutons une autre colonne qui ne peut jamais être NULL pour s'assurer que l'index a toutes les lignes. Nous pouvons utiliser toute colonne qui a une contrainte NOT NULL, comme ID_SUPPLEMENTAIRE.

Autrement, nous pouvons utiliser une expression constante qui ne peut jamais être NULL. Cela nous assure que l'index a toutes les lignes, même si DATE_DE_NAISSANCE est NULL.

DROP   INDEX emp_ddn;
CREATE INDEX emp_ddn ON employes (date_de_naissance, 'X');

Techniquement, cet index est un index fonctionnel. De plus, cet exemple démontre que le mythe selon lequel la base de données Oracle ne peut pas indexer les NULL est erroné.