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.