Tester ses requêtes avec des indexes virtuels

Lorsqu’un traitement en particulier est long, la première règle est de vérifier si la requête SQL utilise des index judicieusement créés.



Si ce n’est pas le cas, il faut alors créer ces index ; cependant, la création de l’index et les effets de bords peuvent être important. Oracle 11g permet de faire une première analyse grâce aux indexes virtuels. Ces index seront en fait déclarer dans le dictionnaire mais n’existeront pas réellement, ils permettent ainsi d’estimer leur pertinence en étudiant les plans d’exécution.



Pour cela, une simple clause NOSEGMENT lors de la création de l’index permet de le distinguer d’un index réel :



CREATE INDEX I_PERS_PRENOM ON personne (prenom) NOSEGMENT;



Ensuite, il faut spécifier au niveau de la session que l’on veut analyser les impacts des index virtuels :



ALTER SESSION SET "_use_nosegment_indexes" = TRUE;



ainsi la requête



SQL> set autot trace exp
SQL> select * from personne where prenom = 'Jean';

Execution Plan
----------------------------------------------------------
Plan hash value: 7654321

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 100 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| PERSONNE | 10 | 100 | 2 (0
|* 2 | INDEX RANGE SCAN | I_PERS_PRENOM | 10 | | 1 (0
--------------------------------------------------------------------------------


Attention à ne pas se baser sur le temps de réponse car l’index n’existe pas, il ne peut donc pas être utilisé lors de la recherche des tuples.



alors que sans l’index :


ALTER SESSION SET "_use_nosegment_indexes" = False;



SQL> set autot trace exp
SQL> select * from personne where prenom = 'Jean';

Execution Plan
----------------------------------------------------------
Plan hash value: 1234567

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 1050 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSONNE | 105 | 1050 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------



On voit ainsi que l’index est réellement bénéfique, on peut alors l’activer pour de bon :

Pour cela, on supprime l'index virtuel

drop index I_PERS_PRENOM ;

Pour créer un index classique

CREATE INDEX I_PERS_PRENOM ON personne (prenom);





Note : SQL tuning pack utilise cette technique afin de vous proposer les index à créer sur vos tables.




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories