Les bases de données peuvent lire les index dans les deux sens. Cela
signifie qu'un order by en pipeline est aussi possible
sur l'intervalle parcouru de l'index dans l'ordre opposé à celui spécifié
dans la clause order by. Bien que les modificateurs
ASC et DESC de la clause order
by puissent empêcher une exécution en pipeline, la plupart des
bases de données propose un moyen simple de changer l'ordre de l'index
pour qu'un index soit quand même utilisable.
L'exemple suivant utilise un index dans l'ordre inverse. Il fournit
les ventes depuis hier, ordonnées par date descendante et par
ID_PRODUIT descendant.
SELECT date_vente, id_produit, quantite
FROM ventes
WHERE date_vente >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY date_vente DESC, id_produit DESC;Le plan d'exécution montre que la base de données lit l'index en sens inverse.
-----------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
-----------------------------------------------------------------
| 0 |SELECT STATEMENT | | 320 | 300 |
| 1 | TABLE ACCESS BY INDEX ROWID | VENTES | 320 | 300 |
|*2 | INDEX RANGE SCAN DESCENDING| DATE_VENTE_PR | 320 | 4 |
-----------------------------------------------------------------Dans ce cas, la base de données utilise l'arbre de l'index pour trouver la dernière entrée correspondante. À partir de là, il suit la chaîne de nœuds feuilles en remontant comme indiqué dans la Figure 6.2. Cela explique pourquoi la base de données utilise une liste doublement chaînée pour construire la chaîne des nœuds feuilles.
Figure 6.2 Parcours d'index inverse
Bien sûr, il est crucial que l'intervalle parcouru de l'index soit
dans l'ordre inverse exact, autrement dit dans le même sens que celui
indiqué par la clause order by.
Important
Les bases de données peuvent lire les index dans les deux sens.
L'exemple suivant ne remplit pas cette condition car il mixe les
modificateurs ASC et DESC dans la clause
order by :
SELECT date_vente, id_produit, quantite
FROM ventes
WHERE date_vente >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY date_vente ASC, id_produit DESC;La requête doit tout d'abord récupérer les ventes d'hier ordonnées
par la colonne ID_PRODUIT en sens inverse, puis les ventes du
jour ordonnées là-aussi par la colonne ID_PRODUIT en sens
inverse. La Figure 6.3
illustre ce travail. Pour obtenir les ventes dans l'ordre demandé, la base
de données devrait sauter à un autre emplacement pendant le parcours de
l'index.
Figure 6.3 Clause order by inutilisable dans un
pipeline
Néanmoins, l'index n'a pas de lien permettant d'aller des ventes
d'hier ayant le plus petit ID_PRODUIT aux ventes
d'aujourd'hui ayant le plus grand ID_PRODUIT. Du coup, la
base de données ne peut pas utiliser cet index pour éviter une opération
explicite de tri.
Pour ce genre de cas, la plupart des bases de données propose une
méthode simple pour ajuster l'ordre de l'index à la clause order
by. Concrètement, cela signifie que vous pouvez utiliser les
modificateurs ASC et DESC dans la déclaration de
l'index :
DROP INDEX date_vente_pr;
CREATE INDEX date_vente_pr
ON ventes (date_vente ASC, id_produit DESC);Attention
Avant la version 8.0, la base de données MySQL ignorait
les modificateurs ASC et DESC dans la définition
de l'index. MariaDB accepte la clause DESC dans les index uniquement depuis la version 10.8.
Maintenant, l'ordre de l'index correspond à celui de la clause
order by, donc la base de données peut éviter
l'opération de tri :
-----------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 320 | 301 |
| 1 | TABLE ACCESS BY INDEX ROWID| VENTES | 320 | 301 |
|*2 | INDEX RANGE SCAN | DATE_VENTE_PR | 320 | 4 |
-----------------------------------------------------------------La Figure 6.4 montre le nouvel ordre de l'index. Le changement du sens du tri pour la deuxième condition a d'une certaine façon changé la direction des flèches du graphique précédent. Ainsi, la première flèche se termine là où la seconde flèche commence, ce qui fait que l'index dispose des lignes dans le bon ordre.
Important
Lors de l'utilisation de modificateurs ASC et
DESC mixés dans la clause order by, vous
devez définir l'index dans le même ordre pour pouvoir l'utiliser en
pipeline avec un order by.
Ceci n'affecte pas l'intérêt de l'index pour la clause
where.
Figure 6.4 Index en ordre mixé
L'indexation ASC/DESC est seulement
nécessaire pour trier les colonnes individuelles en sens inverse. Il n'est
pas nécessaire d'inverser l'ordre de toutes les colonnes car la base de
données peut toujours lire l'index dans l'ordre inverse si besoin est, les
index secondaires sur les tables organisées en index
étant la seule exception. Les index secondaires ajoutent implicitement la
clé de regroupement à l'index sans fournir la possibilité de spécifier
l'ordre de tri. Si vous avez besoin de trier la clé de regroupement en
ordre inverse, vous n'avez pas d'autres options que de trier toutes les
autres colonnes en sens inverse. Alors la base de données peut lire
l'index en sens inverse pour obtenir l'ordre souhaité.
En dehors des modificateurs ASC et
DESC, le standard SQL définit deux modificateurs moins
connus de la clause order by : NULLS FIRST
et NULLS LAST. Le contrôle explicite sur le tri des valeurs
NULL a été « récemment » introduit comme extension
optionnelle du standard SQL:2003. En conséquence, le
support de ce modificateur est peu fréquent. C'est assez inquiétant car le
standard ne définit pas exactement l'ordre de tri des valeurs
NULL. Il indique seulement que toutes les valeurs
NULL doivent apparaître ensemble après le tri mais il ne
spécifie pas si elles doivent apparaître avant ou après les autres
valeurs. Vous aurez besoin de spécifier le tri des valeurs
NULL pour toutes les colonnes qui peuvent être NULL dans la
clause order by pour obtenir un comportement bien
défini.
Cependant, l'extension optionnelle n'est implémentée ni par SQL
Server 2019 ni par MySQL 8.0. La base de données Oracle propose le tri des
valeurs NULLS avant qu'il ait été introduit dans le standard
mais il ne l'accepte dans la définition des index que depuis la version
19c. La base de données Oracle peut ne pas faire un
tri en pipeline pour une clause order by lors d'un tri
avec NULLS FIRST. Seule la base de données PostgreSQL (depuis
la version 8.3) supporte le modificateur NULLS dans la clause
order by et dans la définition de la clause.
L'aperçu suivant résume les fonctionnalités fournies par les différentes bases de données.
