par Guillaume Lelarge.

Indexer ASC, DESC et NULLS FIRST/LAST


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);

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 modi­ficateurs 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 2017 ni par MySQL 5.7. 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 12c. 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.

Figure 6.5 Matrice bases de données/fonctionnalités

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Livre de Markus

Couverture du livre « SQL : Au cœur des performances »

L'essence de SQL tuning dans 200 pages.

Acheter de Markus
(Livre de poche et PDF)

Achetez chez Amazon
(Seulement en poche)

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license