Introduction
"Oracle n'indexe pas la valeur NULL" : combien de fois j'ai lu cela... il faudrait ajouter, pour être plus précis, "sauf sous certaines conditions", que nous allons voir de suite :-)



 

Points d'attention
NA.


 

Base de tests
N'importe quelle base Oracle; tests sur une 12.1.

 


 

Exemples
On crée une table de test basique, avec 10 000 enregistrements.
     SQL> create table TEST_NULL (ID NUMBER, NAME VARCHAR2(30
CHAR));
     Table created.

     SQL> begin
          for i in 1..10000
          loop
              insert into test_null values (i, 'test' || to_char(i));
          end loop;
          end;
          /
     PL/SQL procedure successfully completed.

     SQL> commit;
     Commit complete.

     SQL> select count(*) from test_null;
       COUNT(*)
     ----------
          10000

On update la table en mettant à NULL la colonne NAME tous les 100 enregistrements : de la sorte on aura 1% de la table avec NULL; cette cardinalité et cette sélectivité décideront le CBO à utiliser un index si possible.
     SQL> update test_null set name = NULL where mod(id, 100) = 0;
     100 rows updated.

     SQL> commit;
     Commit complete.
    
     SQL> select count(*) from test_null where name is null;
       COUNT(*)
     ----------
            100

On crée un index sur la colonne NAME puis les stats sont calculées pour la table et son index associé.
     SQL> create index idx_test_null_name on test_null(name);

     SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'TEST_NULL', CASCADE=>true);
     PL/SQL procedure successfully completed.

OK, voyons le plan d'exécution si on fait une recherche sur NAME IS NULL. Ah, l'index n'est pas utilisé, comme on s'y attendait puisque Oracle n'indexe pas la valeur NULL. On notera la remarque "
filter("NAME" IS NULL)".
     SQL> set autotrace traceonly

     SQL> select name from test_null where name is null;
     100 rows selected.

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 1897709643

     -------------------------------------------------------------------------------
     | Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |          |   100 |   900 |     9    (0)| 00:00:01 |
     |*  1 |  TABLE ACCESS FULL| TEST_NULL |   100 |   900 |     9    (0)| 00:00:01 |
     -------------------------------------------------------------------------------

     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - filter("NAME" IS NULL)

On crée maintenant un index composite sur le même champ mais, attention, avec en deuxième position une constante. De la sorte tous les enregistrements de la table seront bien indexés puisque "NULL, 1" n'est plus NULL :-)  On aurait pu indexer sur NAME et ID si je l'avais mis en NOT NULL mais, avec cette constante, on augmente la taille de l'index d'un seul octet par row.
     SQL> create index idx_test_null_name_compo on test_null(name, 1);
     Index created.

     SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'TEST_NULL', CASCADE=>true);
     PL/SQL procedure successfully completed.

Bingo, les 100 lignes ayant NULL pour NAME sont cette fois indexées.
     SQL> select index_name, num_rows from user_indexes where table_name = 'TEST_NULL' order by 1;
     INDEX_NAME                    NUM_ROWS
     ---------------------------------------
     IDX_TEST_NULL_NAME            9900
     IDX_TEST_NULL_NAME_COMPO     10000

Et cette fois le nouvel index est bien utilisé avec la clause NAME IS NULL! On note d'ailleurs après le plan que l'index est accédé via NULL :
"access ("NAME" IS NULL)".
     SQL> select name from test_null where name is null;
     100 rows selected.

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 1219616549
     ---------------------------------------------------------------------------------------------
     | Id  | Operation     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    
    
---------------------------------------------------------------------------------------------

     |   0 | SELECT STATEMENT |                |    100 |    900 |      2   (0)| 00:00:01 |
     |*  1 |  INDEX RANGE SCAN| IDX_TEST_NULL_NAME_COMPO |    100 |    900 |      2   (0)| 00:00:01 |     ---------------------------------------------------------------------------------------------
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("NAME" IS NULL)