Les index virtuels

Lorsque vous vous demandez si le fait de créer un index peut améliorer votre requête, ce qui vous freine souvent c’est le fait d’avoir à créer cet index pour effectuer votre test.
Le fait de créer un index sur une table volumineuse peut prendre énormément de temps (CPU+IO) et va consommer de la place sur votre disque.

Oracle offre la possibilité de créer un index sans lui associer de segment. Cela revient à dire qu’on a la possibilité de créer un index virtuel et ainsi savoir si l’optimiseur prendrait en compte l’index s’il existait réellement.

Voici un exemple pour bien comprendre comment profiter des index virtuels.

Tout d’abord créons une table volumineuse :

SQL> create table t1 as select * from all_objects;

Table créée.

Lorsque je veux récupérer les données de T1 dont la colonne OBJECT_TYPE équivaut à « WINDOW », je constate que l’optimiseur effectue un Full Table Scan (FTS) sur ma table.

SQL>  explain plan for
  2  select * from t1 where object_type='WINDOW';

Explicité.

SQL> select * FROM TABLE
  2  (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------
Plan hash value: 3617692013

-----------------------------------------------
| Id  | Operation         | Name | Cost (%CPU)|
-----------------------------------------------
|   0 | SELECT STATEMENT  |      |   367   (1)|
|   1 |  TABLE ACCESS FULL| T1   |   367   (1)|
-----------------------------------------------

Maintenant je me demande la chose suivante : si j’avais un index sur la colonne OBJECT_TYPE, est-ce que l’optimiseur l’utiliserait ?

J’aimerais avoir une réponse à cette question mais sans avoir à créer réellement ma structure d'index.
Pour cela je crée un index virtuel :

SQL> create index idx_t1 on t1(object_type) NOSEGMENT;

Index créé.

La clause NOSEGMENT indique que mon index est virtuel.

A ce stade l’index n’est toujours pas visible par le CBO. Pour le rendre visible il faut modifier un paramètre caché :

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session modifiée.

Maintenant, le CBO voit l’index et décide de le prendre en compte dans le plan :

SQL> explain plan for
  2  select * from t1 where object_type='WINDOW';

Explicité.

SQL> select * FROM TABLE
  2  (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 50753647

-----------------------------------------------------------
| Id  | Operation                   | Name   | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     5   (0)|
|   2 |   INDEX RANGE SCAN          | IDX_T1 |     1   (0)|
-----------------------------------------------------------

Notez que la création de l’index est intéressante car il fait chuter le COST du plan de 367 à 5.

Pour vous prouver que l’index n’existe pas réellement :

SQL> Select * from user_indexes where table_name='T1' ;

aucune ligne sélectionnée

L’index n’est pas référencé en tant qu’un index dans USER_INDEXES mais est bien défini en tant qu’objet:

SQL> select object_name from user_objects where object_name='IDX_T1';

OBJECT_NAME
-----------------
IDX_T1

Maintenant que j’ai validé que mon index est vraiment intéressant à créer je peux dropper mon index virtuel et créer un véritable index à la place.