novembre
2012
Un article de ora_home
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 :
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.
2 select * from t1 where object_type='WINDOW';
Explicité.
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 :
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é :
Session modifiée.
Maintenant, le CBO voit l’index et décide de le prendre en compte dans le plan :
2 select * from t1 where object_type='WINDOW';
Explicité.
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:
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.
Catégories
| L | Ma | Me | J | V | S | D |
|---|---|---|---|---|---|---|
| « oct | ||||||
| 1 | ||||||
| 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 9 | 10 | 11 | 12 | 13 | 14 | 15 |
| 16 | 17 | 18 | 19 | 20 | 21 | 22 |
| 23 | 24 | 25 | 26 | 27 | 28 | 29 |
| 30 | 31 | |||||