Performance - IS NULL - Indexes

Hello,
Il nous est tous arrivé (enfin en tout cas à moi) de tomber sur des requêtes avec des clauses "IS NULL". Et avec ce sentiment injuste de ne pas pouvoir faire grand chose sauf à revoir la logique du schema ou le fonctionnel.
Un petit exemple pour illustrer cela. Je vais créer une table lamidba sur la base de DBA_OBJETCS. Etant sur une base sans réel schema, je vais même pousser à l'inserer plusieurs fois.

SQL> create table lamidba as
(select * from dba_objects
union all
select * from dba_objects
union all
select * from dba_objects
union all
select * from dba_objects
union all
select * from dba_objects
union all
select * from dba_objects
union all
select * from dba_objects); 

Table created.

Elapsed: 00:00:21.23



SQL>
SQL>
SQL> select count(*) from lamidba;

  COUNT(*)
----------
   4072768

Elapsed: 00:00:09.64
SQL>

Nous voila avec une table de 4 millions de lignes.

Pas de surprise, nous avons un full scan... Logique, il n'y a aucun index sur ma table ;)
Je sais que cela ne changera rien mais pour les sceptiques, créeons un index sur la colonne namespace.

SQL>
SQL> create index idx_01 on lamidba(namespace);

Index created.

Elapsed: 00:01:48.12
SQL>

Au passage, je me suis rendu compte que j'avais oublié de collecter les stats sur ma table.

SQL> exec dbms_stats.gather_table_stats('LAO','LAMIDBA',cascade=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:31.80
SQL>

Rejouons notre requête.

Pas de miracle... Toujours un FULL SCAN, un grand nombre d'I/O logiques & physiques.
Et oui lors de la création d'un index B-Tree, les valeurs null ne sont pas prises en compte, et donc l'index ne sera pas utilisé. Ce qui est dommage car ma requête ne renvoie que 56 lignes sur plus de 4 millions.
Alors que fait-on ? On abandonne... Never Give Up ! Abandonner une fois, c'est s'habituer à l'échec (M. Jordan, je crois)
Première possibilité : Créer un index bitmap. L'objet de l'article n'est pas de traiter de l'index bitmap (un prochain article ;)), mais à savoir qu'il faut faire (très) attention au moment de le créer.. (nombre de valeur distincte, ajout de nouvelles valeures qui peut engendrer des dégradations,...). Ce sont plutôt des indexes qu'on trouve dans des environnements DataWareHouse.

SQL> drop index idx_01;

Index dropped.

SQL>
SQL> create bitmap index idx01 on lamidba(namespace);

Index created.

Elapsed: 00:00:09.82

Et on re-teste.

Et magique ! L'index Bitmap prend en compte les valeurs NULL. Et que ce soit en COST, I/O, ou temps d'execution on y gagne très fortement.
Cependant comme indiqué plus haut, l'index BitMap n'est pas approprié à tout type d'environnement.
Remarque : Autre point génant... l'index BitMap est uniquement disponible en version Enterprise...
On abandonne.. Jamais !
Les valeurs null ne sont pas incluses dans un indexe B-Tree qui porte sur une colonne ! Qu'est ce qui m'empêche de créer un index sur plusieurs colonnes, ou si le metier ne s'y porte pas, on peut tout simplement créer un index fonction de type (ma_colonne,1). Le 1 est ici totalement arbitraire, mais le fait de créer un index composé va m'être utile.
La preuve par l'exemple.

SQL> drop index idx01;

Index dropped.

Elapsed: 00:00:00.29
SQL>
SQL> create index idx01 on lamidba(namespace,1);

Index created.

Elapsed: 00:01:02.89
SQL>

Roulements de tambours..

Moins de 0,1 seconde, très peu d'I/O logiques & physiques ! Nous sommes sauvés ;)
Enjoy !