Prédicats de filtre utilisés intentionnellement sur des index

Très souvent, des prédicats de filtre d'index indiquent une mauvaise utilisation d'un index causé par un ordre incorrect des colonnes dans un index concaténé. Toutefois, les prédicats de filtre d'index peuvent aussi être utilisés pour une bonne raison : pas pour améliorer les performances d'un parcours d'intervalle, mais pour regrouper des données accédées consécutivement.

Les prédicats de la clause where qui ne peuvent pas servir en tant que prédicat d'accès sont de bons candidats pour cette technique :

SELECT prenom, nom, id_supplementaire, numero_telephone
  FROM employes
 WHERE id_supplementaire = ?
   AND UPPER(nom) LIKE '%INA%';

Rappelez-vous que les expressions LIKE avec des caractères joker en début ne peuvent pas utiliser d'index. Cela signifie que l'indexation de NOM ne peut pas réduire l'intervalle parcouru dans l'index, peu importe que vous indexiez nom ou UPPER(nom). Du coup, cette condition n'est pas un bon candidat pour l'indexation.

Néanmoins, la condition sur ID_SUPPLEMENTAIRE est tout à fait convenable pour l'indexation. Nous n'avons même pas besoin d'ajouter un nouvel index car la colonne ID_SUPPLEMENTAIRE est déjà la première colonne dans l'index de la clé primaire.

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   17 |  230 |
|*1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYES    |   17 |  230 |
|*2 |   INDEX RANGE SCAN          | EMPLOYES_PK |  333 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("NOM") LIKE '%INA%')
   2 - access("ID_SUPPLEMENTAIRE"=TO_NUMBER(:A))

Dans le plan d'exécution ci-dessus, le coût est multiplié par 100 de l'opération INDEX RANGE SCAN à l'opération TABLE ACCESS BY INDEX ROWID. Autrement dit, l'accès à la table est la cause du plus gros travail. C'est un modèle courant et n'est pas un problème en soi. Néanmoins, c'est le contributeur le plus significatif à la durée d'exécution globale de cette requête.

L'accès à la table n'est pas nécessairement un goulet d'étranglement si les lignes accédées sont enregistrées dans le même bloc de table car la base de données peut récupérer toutes les lignes en une seule opération de lecture. Si les mêmes lignes sont réparties sur de nombreux blocs, en contraste, l'accès à la table peut devenir un sérieux problème de performance parce que la base de données doit récupérer plusieurs blocs pour retrouver toutes les lignes. Cela signifie que les performances dépendent de la distribution physique des lignes accédées... autrement dit, elles dépendent du regroupement des lignes.

En fait, il est possible d'améliorer les performances des requêtes en réordonnant les lignes dans la table pour qu'elles correspondent à l'ordre des enregistrements dans l'index. Néanmoins, cette méthode est peu souvent applicable car vous pouvez seulement enregistrer les lignes de la table en une séquence. Cela signifie que vous ne pouvez optimiser la table que pour un seul index. Même en choisissant un seul index, l'exercice reste difficile car la plupart des bases de données n'offrent que des outils rudimentaires pour réaliser cette tâche. Le séquençage des lignes n'est qu'une approche peu pratique.

C'est exactement là que survient la deuxième puissance de l'indexation, le regroupement des données. Vous pouvez ajouter de nombreuses colonnes à un index pour qu'elles soient automatiquement enregistrées dans un ordre bien défini. Cela fait de l'index un outil puissant bien que simple pour le regroupement des données.

Pour appliquer ce concept à la requête ci-dessus, nous devons étendre l'index pour qu'il couvre toutes les colonnes provenant de la clause where, même s'ils ne réduisent pas l'intervalle parcouru dans l'index :

CREATE INDEX empsousnommaj ON employes
       (id_supplementaire, UPPER(nom));

La colonne ID_SUPPLEMENTAIRE est la première colonne de l'index pour qu'elle puisse être utilisée comme prédicat d'accès. L'expression UPPER(nom) couvre le filtre LIKE comme un prédicat de filtre de l'index. Indexer la représentation en majuscule aide à diminuer l'utilisation du processeur à l'exécution, mais un simple index sur NOM fonctionnerait tout aussi bien. Vous en apprendrez plus sur cette technique dans la prochaine section.

----------------------------------------------------------------
|Id | Operation                   | Name         | Rows | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT            |              |   17 |   20 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYES     |   17 |   20 |
|*2 |   INDEX RANGE SCAN          | EMPSOUSNOMMAJ|   17 |    3 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID_SUPPLEMENTAIRE"=TO_NUMBER(:A))
       filter(UPPER("NOM") LIKE '%INA%')

Le nouveau plan d'exécution montre les mêmes opérations qu'auparavant. Néanmoins, le coût a baissé fortement. Les informations de prédicat montrent que le filtre LIKE est déjà appliqué lors de l'opération INDEX RANGE SCAN. Les lignes qui ne satisfont pas le filtre LIKE sont immédiatement ignorées. L'accès à la table n'a plus de prédicat de filtre. Cela signifie qu'il n'a plus besoin de charger des lignes qui ne satisfont pas la clause where.

La différence entre les deux plans d'exécutions est clairement visible dans la colonne « Rows ». Suivant les estimations de l'optimiseur, la requête récupère à la fin 17 enregistrements. Le parcours d'index dans le premier plan d'exécution en récupère 333. La base de données doit alors charger ces 333 lignes de la table pour leur appliquer le filtre LIKE qui en réduit le nombre à 17. Dans le deuxième plan d'exécution, l'accès à l'index ne doit pas délivrer ces lignes car la base de données doit exécuter l'opération TABLE ACCESS BY INDEX ROWID 17 fois seulement.

Notez aussi que le coût de l'opération INDEX RANGE SCAN augmente de deux à trois à cause de la colonne supplémentaire qui rend l'index plus gros. Au vue du gain de performances, c'est un compromis acceptable.

L'animation suivante démontre la différence entre les deux plans d'exécution :

Figure 5.1 Index conçu pour les prédicats de filtre

Cet exemple trivial semble confirmer la sagesse commune qui revient à indexer chaque colonne comprise dans une clause where. Néanmoins, cette « sagesse » ignore l'importance de l'ordre des colonnes qui détermine les conditions pouvant être utilisées comme prédicats d'accès et donc qui peut avoir un impact important sur les performances. La décision sur l'ordre des colonnes ne devrait jamais être laissée au hasard.

L'index grossit avec le nombre de colonnes, tout spécialement avec les colonnes de texte. Bien sûr, la performance n'est pas meilleure avec un index plus gros bien que la scalabilité logarithmique limite considérablement l'impact. Vous ne devez en aucun cas ajouter dans l'index toutes les colonnes mentionnées dans la clause where. À la place, utilisez seulement les prédicats de filtres pour réduire le volume de données lors de l'étape d'exécution précédente.