Oracle - Function Based Index : NVL

Oracle | Function Based Index : NVL

Notes
Les considérations à prendre pour les valeurs NULL lors de la création d'un indexe.

Notes
Points importants (voir les détails dans ce qui suit) :
- Si on crée un indexe avec la fonction NVL, alors il faut utiliser cette fonction dans les conditions SQL, sinon l'indexe ne sera pas utilisé.
- La création d'un indexe avec la fonction NVL doit prendre en considération le pourcentage des valeurs NULL d'une colonne dans toute la table ainsi que la fréquence des requêtes avec l'agrégat IS NULL sur cette colonne.
- Oracle peut ignorer de ne pas utiliser un indexe et utiliser un full scan de la table si l'optimiseur juge que c'est la façon optimale de sélectionner des données (c'est le cas si les blocs de données sont déjà chargées en mémoire).
This table (and its data) is used in the examples bellow:
CREATE TABLE table_nvl ( id NUMBER(10,0) NOT NULL , refid NUMBER(10,0) );
DECLARE indexLoop NUMBER(5,0); BEGIN FOR indexLoop IN 1..50000 LOOP IF MOD(indexLoop,2) = 0 THEN INSERT INTO table_nvl (id, refid) VALUES (DBMS_RANDOM.VALUE(1,50000), indexLoop); ELSE INSERT INTO table_nvl (id, refid) VALUES (DBMS_RANDOM.VALUE(1,50000), NULL); END IF; END LOOP; END; /
Les considérations à prendre pour les valeurs NULL lors de la création d'un indexe.
1. Créer un indexe sur la colonne ID.
  CREATE INDEX ix_table_nvl_id ON table_nvl (id); ANALYZE INDEX ix_table_nvl_id COMPUTE STATISTICS; ANALYZE INDEX ix_table_nvl_id VALIDATE STRUCTURE;
  SELECT name, lf_rows FROM index_stats;
  Résultat de la requête : | NAME | LF_ROWS | +-----------------+---------+ | IX_TABLE_NVL_ID | 50000 |
2. Créer un indexe sur la colonne REFID.
  CREATE INDEX ix_table_nvl_refid ON table_nvl (refid); ANALYZE INDEX ix_table_nvl_refid COMPUTE STATISTICS; ANALYZE INDEX ix_table_nvl_refid VALIDATE STRUCTURE;
  SELECT name, lf_rows FROM index_stats;
  Résultat de la requête : | NAME | LF_ROWS | +--------------------+---------+ | IX_TABLE_NVL_REFID | 25000 |
  Il faut remarquer que seulement 25000 lignes ont été insérées pour l'indexe alors que la table contient réellement 50000 lignes.
  
  Un des points importants à comprendre est la gestion des valeurs NULL dans un indexe :
  Un indexe conventionnel (B*Tree) n'insère pas d'entrées pour les valeurs NULL.
  Les colonnes dont les valeurs sont NULL n'ont été pas indexées.
  
  Ce qui est important à savoir aussi est qu'il y aura un full scan de la table si on effectue une requête pour remonter toutes les lignes dont la valeur de la colonne REFID est NULL ou NOT NULL.
  - IS NULL : SELECT * FROM table_nvl WHERE refid IS NULL;
    Plan d'exécution : | OBJECT_NAME | OPTIONS | +-------------+---------+ | TABLE_NVL | FULL |
  - IS NOT NULL : SELECT * FROM table_nvl WHERE refid IS NOT NULL;
    Plan d'exécution : | OBJECT_NAME | OPTIONS | +-------------+---------+ | TABLE_NVL | FULL |
  Une des solutions possibles est d'ajouter un hint pour forcer l'utilisation de l'indexe :
  - IS NULL : SELECT /*+ index(table_nvl ix_table_nvl_refid)*/ * FROM table_nvl WHERE refid IS NULL;
    Plan d'exécution : | OBJECT_NAME | OPTIONS | +-------------+---------+ | TABLE_NVL | FULL |
  - IS NOT NULL : SELECT /*+ index(table_nvl ix_table_nvl_refid)*/ * FROM table_nvl WHERE refid IS NOT NULL;
    Plan d'exécution : | OBJECT_NAME | OPTIONS | +--------------------+----------------+ | TABLE_NVL | BY INDEX ROWID | | IX_TABLE_NVL_REFID | FULL SCAN |
  Sinon : SELECT * FROM table_nvl WHERE refid > 0;
  Plan d'exécution : | OBJECT_NAME | OPTIONS | +--------------------+----------------+ | TABLE_NVL | BY INDEX ROWID | | IX_TABLE_NVL_REFID | RANGE SCAN |
3. Créer un indexe-fonction (NVL) sur la colonne REFID.
  DROP INDEX ix_table_nvl_refid; CREATE INDEX ix_table_nvl_refid ON table_nvl (NVL(refid,0)); ANALYZE INDEX ix_table_nvl_refid COMPUTE STATISTICS; ANALYZE INDEX ix_table_nvl_refid VALIDATE STRUCTURE;
  SELECT name, lf_rows FROM index_stats;
  Résultat de la requête : | NAME | LF_ROWS | +--------------------+---------+ | IX_TABLE_NVL_REFID | 50000 |
  Il faut remarquer qu'il y maintenant 50000 lignes insérées pour l'indexe.
  
  Ce qui est important à savoir est qu'il y aura un full scan de la table si on effectue une requête pour remonter toutes les lignes dont la valeur de la colonne REFID est égale à une valeur précise, NULL, ou NOT NULL.
  SELECT * FROM table_nvl WHERE refid = 10; SELECT * FROM table_nvl WHERE refid IS NULL; SELECT * FROM table_nvl WHERE refid IS NOT NULL; SELECT * FROM table_nvl WHERE refid > 0; -- IS NOT NULL
  Plan d'exécution : | OBJECT_NAME | OPTIONS | +-------------+---------+ | TABLE_NVL | FULL |
  La solution est d'utiliser la fonction NVL sur la colonne REFID : SELECT * FROM table_nvl WHERE nvl(refid,0) = 10; SELECT /*+ index(table_nvl ix_table_nvl_refid)*/ * FROM table_nvl WHERE nvl(refid,0) = 10; SELECT * FROM table_nvl WHERE nvl(refid,0) = 0; -- IS NULL SELECT /*+ index(table_nvl ix_table_nvl_refid)*/ * FROM table_nvl WHERE nvl(refid,0) = 0; -- IS NULL SELECT * FROM table_nvl WHERE nvl(refid,0) > 0; -- IS NOT NULL SELECT /*+ index(table_nvl ix_table_nvl_refid)*/ * FROM table_nvl WHERE nvl(refid,0) > 0; -- IS NOT NULL
  Plan d'exécution : | OBJECT_NAME | OPTIONS | +--------------------+----------------+ | TABLE_NVL | BY INDEX ROWID | | IX_TABLE_NVL_REFID | RANGE SCAN |