Pourquoi mon index n'est pas utilisé ?

Lors de l'analyse de la lenteur de certains traitements, une question revient souvent : pourquoi mon index n'est-il pas utilisé ?

A celle-ci, plusieurs réponses sont possibles. Aussi, détaillons les différents cas :

- index invalide
Vous pouvez le vérifier simplement par la requête :

select index_name, status from user_indexes where index_name='IX1';

L'erreur ORA-01502 est un symptôme très courant dans ce genre de cas. Quoiqu'il en soit, il faut reconstruire l'index :

alter index IX1 rebuild;


Les causes :
* alter index IX1 unusable;
* Déplacement physique des données de la table

- Problème de statistiques avec le mode CBO
Dans le cas où l'optimiseur Oracle est en mode CBO (paramètre optimizer_mode), il se base donc sur les statistiques pour connaître les différentes volumétries et ainsi utiliser le plan d'exécution dont le coût est le plus faible. Si les statistiques ne sont pas à jour, il évalue de manière erronée le plan le moins coûteux.
Pour connaître la date de dernière mise à jour des statistiques respectivement de la table T1 et de l'index I1 :

select table_name, last_analyzed from user_tables where table_name = 'T1';

select index_name, last_analyzed from user_indexes where index_name = 'I1';


Dans ce cas, il faut alors mettre à jour les statistiques Oracle.

- Table trop peu volumineuse
Lorsque Oracle juge (via la collecte des statistiques) que la table est suffisamment peu volumineuse par rapport à l'index, il va utiliser celle-ci ; celà signifie que certaines tables peuvent tout à fait être en full scan table et ne nécessite pas d'index du fait de leur faible volumétrie.


- hint incompatible
Dans le cadre d'une optimisation ou de test, si votre requête spécifie un hint, Oracle le prendra en compte et reconstruira son plan d'exécution en prenant en compte cette contrainte supplémentaire. Ainsi si un hint full(ma_table) ou no_index(ma_table, mon_index) a été demandé, l'index ne sera pas utilisé (c'est d'ailleurs le but de ces hints) :

select /*+ full(t1) */ t1.c1 from t1;
devra, pour forcer l'utilisation de l'index, être remplacé par :

select /*+ index(t1 i1) */ t1.c1 from t1;


- Si votre index est composé de plusieurs colonnes, sachez qu'Oracle n'a introduit le plan index skip scan qu'à partir de la 9i.

  • Le plan s'appuyant sur index skip scan peut être trop gourmand car les combinaisons des colonnes précédentes sont trop nombreuses (là encore, seules les statistiques permettent de le définir) ; c'est pourquoi lors de l'indexation de plusieurs colonnes, vérifier l'ordre afin de placer les catégories par exemple (car elles sont définies et bornées) en premier.
  • Toujours dans le cas ou l'index concerne plusieurs colonnes, le "order by" est incompatible avec index skip scan.


- Utilisation d'une fonction
Lorsque la requête est construite à partir d'une fonction sur la colonne (upper, ...), l'index est inutilisable ; l'utilisation d'un like '%XX' donne le même résultat.
Il faut alors adapter l'index à la fonction (ou revoir le stockage).


- La table est partitionnée, l'index est inadapté
Si l'index créé est global au lieu de local (ou l'inverse) sur une table partitionnée, selon la requête il peut ne pas apporter de gain significatif ; il ne sera donc pas utilisé.


- Comparaison avec NULL
Comme vu dans l'article concernant les index B-tree, NULL n'est pas une valeur indexée. Une requête y faisant référence ne pourra s'appuyer sur un index de ce type (BTREE) :

select c1 from t1 where c1 is not null;

Une astuce consiste alors à utiliser un index de fonction (nvl en l'occurence).


- Cardinalité proportionnellement trop importante
Si la colonne indexée présente une cardicalité de la valeur requéttée trop importante, alors Oracle jugera préférable de lire la table entiérement (full scan).
A contrario, lorsque vous utilisez des bind variables, Oracle établit le plan d'exécution sans en conaître la valeur, il peut donc utiliser une valeur de cardinalité complètement différente de la réalité dans le cas où les cardinalités de chaque valeur de la(les) colonne(s) sont très disparates. Il choisira alors plutôt tel index ou tel autre.
Voir à ce sujet les histogrammes associés aux statistiques.


Voir aussi :
- Les statistiques Oracle
- Les index B-tree
- Les index bitmap
- Améliorer les performances d'une vue
- Créer un index de fonction
- Comparer l'exécution d'une requête avec 2 index différents
- Hint oracle et choix du plan d'exécution




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

Catégories