Les index invisibles de la 11g

Dans un post précédent « Index ou presque« , j’illustrais comment utiliser un index sans le créer grâce à la clause NOSEGMENT. 11g offre la capacité contraire à savoir de créer un index sans que celui-ci soit utilisé par l’optimiseur à moins que vous ne spécifiez que la session peut utiliser ce type d’index. On a tous, un jour ou l’autre, créé un index qui a dégradé les performances d’une de nos requêtes.

L’exemple qui suit illustre comment manipuler un index invisible et, peut-être, comment éviter un prochain problème. D’abord créez une table et ajoutez quelques lignes :

create table gark(id number);

begin
for i in 1..10000 loop
insert into gark values(i);
end loop;
commit;
end;
/

Vous pouvez interroger la table à l’aide de la requête qui suit :

select id
from gark
where id=10;

select *
from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4wctq8rj3ryva, child number 1
-------------------------------------
select id from gark where id=10

Plan hash value: 2884726894
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 |
|* 1 | TABLE ACCESS FULL| GARK | 1 | 13 | 7 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=10)

Note
-----
- dynamic sampling used for this statement

Maintenant créez l’index avec la clause INVISIBLE :

create index gark_idx
on gark(id) invisible;

Index created.

Si vous exécutez la requête à nouveau l’index n’est pas utilisé, malgré sa pertinence pour la requête :

select id
from gark
where id=10;

select *
from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID 4wctq8rj3ryva, child number 1
-------------------------------------
select id from gark where id=10

Plan hash value: 2884726894
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 |
|* 1 | TABLE ACCESS FULL| GARK | 1 | 13 | 7 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=10)

Note
-----
- dynamic sampling used for this statement

Si par contre vous positionnez la valeur du paramètre OPTIMIZER_USE_INVISIBLE_INDEXES à true au niveau de la session et que vous ré-exécutez la requête l’index est utilisé :

alter session set
optimizer_use_invisible_indexes=true;

select id
from gark
where id=10;

select *
from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4wctq8rj3ryva, child number 1
-------------------------------------
select id from gark where id=10

Plan hash value: 280029042
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost|
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 |
|* 1 | INDEX RANGE SCAN| GARK_IDX | 1 | 13 | 1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=10)
Note
-----
- dynamic sampling used for this statement

Une requête pour vérifiez si l’index est visible :

select index_name, visibility
from user_indexes
where index_name='GARK_IDX';

INDEX_NAME VISIBILITY
---------- ----------
GARK_IDX INVISIBLE

Vous pouvez rentre un index visible avec ALTER INDEX :

alter index gark_idx visible;

select index_name, visibility
from user_indexes
where index_name='GARK_IDX';

INDEX_NAME VISIBILITY
---------- ----------
GARK_IDX VISIBLE

Pour terminer, supprimez la table et l’index de l’exemple :

drop table gark
cascade constraints
purge;

Pour en savoir plus sur les index invisibles, référez-vous à la section correspondante de Administrator’s Guide.