Les requêtes SQL disposant d'une clause order by
n'ont pas besoin de trier les résultats explicitement si l'index adéquat
renvoie déjà les lignes dans l'ordre requis. Cela signifie que le même
index peut être utilisé pour la clause where
et pour la
clause order by
.
Prenons comme exemple la requête suivante qui sélectionne les ventes d'hier ordonnées par la date de la vente et l'identifiant du produit :
SELECT date_vente, id_produit, quantite
FROM ventes
WHERE date_vente = TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY date_vente, id_produit;
Il existe déjà un index sur DATE_VENTE
qui peut être
utilisé pour la clause where
. Néanmoins, la base de
données doit réaliser une opération de tri explicite pour satisfaire la
clause order by
:
---------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 320 | 18 |
| 1 | SORT ORDER BY | | 320 | 18 |
| 2 | TABLE ACCESS BY INDEX ROWID| VENTES | 320 | 17 |
|*3 | INDEX RANGE SCAN | DATE_VENTE | 320 | 3 |
---------------------------------------------------------------
Un INDEX RANGE SCAN
renvoie les lignes dans l'ordre de
l'index de toute façon. Pour maximiser cet avantage, nous avons juste
besoin d'étendre la définition de l'index pour qu'il corresponde à la
clause de l'order by
:
DROP INDEX date_ventes;
CREATE INDEX date_ventes_pr ON ventes (date_vente, id_produit);
SELECT date_vente, id_produit, quantite
FROM ventes
WHERE date_vente = TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY date_vente, id_produit;
------------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 320 | 300 |
| 1 | TABLE ACCESS BY INDEX ROWID| VENTES | 320 | 300 |
|*2 | INDEX RANGE SCAN | DATE_VENTES_PR | 320 | 4 |
------------------------------------------------------------------
L'opération de tri SORT ORDER BY
a disparu du plan
d'exécution même si la requête a toujours une clause order
by
. La base de données exploite l'ordre de l'index et n'a plus
besoin d'une opération de tri explicite.
Important
Si l'ordre de l'index correspond à la clause order
by
, la base de données peut omettre l'opération de tri
explicite.
Même si le nouveau plan d'exécution contient moins d'opérations, le coût a augmenté considérablement parce que le facteur de regroupement du nouvel index est bien pire (voir « Facteur de regroupement automatiquement optimisé »). Il faut simplement noter que le coût n'est pas toujours un bon indicateur de l'effort à l'exécution.
Pour cette optimisation, il est suffisant que l'intervalle parcouru
de l'index soit trié suivant la clause order
by
. Du coup, l'optimisation fonctionne
aussi pour cet exemple particulier lors du tri de ID_PRODUIT
seul :
SELECT date_vente, id_produit, quantite
FROM ventes
WHERE date_vente = TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY id_produit;
Dans la , nous voyons que la colonne
ID_PRODUIT
est le seul critère de tri dans l'intervalle
parcouru de l'index. Du coup, l'ordre de l'index correspond à la clause
order by
dans cet intervalle
d'index. La base de données peut donc omettre l'opération de
tri.
Figure 6.1 Ordre de tri dans l'intervalle adéquat de l'index

Cette optimisation peut causer un comportement inattendu lors de l'agrandissement de l'intervalle parcouru de l'index :
SELECT date_vente, id_produit, quantite
FROM ventes
WHERE date_vente >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY id_produit;
Cette requête ne récupère pas les ventes d'hier
mais toutes les ventes depuis hier. Cela signifie
qu'elle couvre plusieurs jours et parcourt un intervalle qui n'est plus
exclusivement trié par la colonne ID_PRODUIT
. Si nous
regardons encore la et étendons l'intervalle parcouru de
l'index jusqu'au bout, nous voyons qu'il existe encore des valeurs plus
petites de ID_PRODUIT
. Du coup, la base de données doit
utiliser une opération explicite de tri pour satisfaire la clause
order by
.
-----------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
-----------------------------------------------------------------
| 0 |SELECT STATEMENT | | 320 | 301 |
| 1 | SORT ORDER BY | | 320 | 301 |
| 2 | TABLE ACCESS BY INDEX ROWID| VENTES | 320 | 300 |
|*3 | INDEX RANGE SCAN | DATE_VENTE_PR | 320 | 4 |
-----------------------------------------------------------------
Si la base de données utilise une opération de tri alors que vous
attendiez une exécution en pipeline, cela peut avoir deux raisons : le
plan d'exécution avec une opération de tri explicite a un meilleur coût ;
l'ordre de l'index dans l'intervalle parcouru ne correspond pas à la
clause order by
.
Une façon simple de séparer les deux cas est d'utiliser la
définition complète de l'index dans la clause order by
,
autrement dit en ajustant la requête d'après l'index pour éliminer la
deuxième cause. Si la base de données utilise toujours une opération
explicite de tri, l'optimiseur préfère le plan à cause de son coût : sinon
la base de données ne peut pas utiliser l'index pour la clause
order by
originale.
Astuce
Utilisez la définition complète de l'index
dans la clause order by
pour trouver la raison
d'une opération explicite de tri.
Dans les deux cas, vous pourriez vous demander si et comment il est
possible d'avoir une exécution en pipeline d'un order
by
. Pour cela, vous pouvez exécuter la requête avec la
définition complète de l'index dans la clause order by
,
puis vous inspectez le résultat. Vous réaliserez souvent que vous avez une
mauvaise perception de l'index et que l'ordre de l'index n'est en fait pas
requis par la clause originale de l'order by
. La base
de données ne peut donc pas utiliser l'index pour éviter une opération de
tri.
Si l'optimiseur préfère une opération de tri explicite grâce à son
coût, c'est généralement dû au fait que l'optimiseur prend le meilleur
plan d'exécution pour une exécution complète de la
requête. En d'autres termes, l'optimiseur choisit le plan d'exécution qui
est le plus rapide pour obtenir la dernière ligne. Si la base de données
détecte que l'application ne récupère que les quelques premières lignes,
elle pourrait préférer exécuter l'order by
avec un
index. Le Chapitre 7, « Résultats partiels » explique les méthodes
d'optimisation correspondantes.