Utilisation des index (2)

logique OU n’est pas sargeable. Mais vous pouvez rendre sargeable la requête en la décomposant en deux requêtes réunies par l’opérateur ensembliste UNION.

Exemple: SELECT … FROM CLIENT AS C INNER JOIN EMPLOYE AS E ON C.CLI_ID = E.CLI_ID WHERE CLI_NOM = ‘DUPONT’ OR EMP_NOM = ‘DUPONT’
N’est pas sargeable, même si des index ont été placés sur les noms des clients et des employés… En revanche, sa petite soeur est parfaitement sargeable : SELECT … FROM CLIENT WHERE CLI_NOM = ‘DUPONT’ UNION SELECT … FROM EMPLOYE WHERE EMP_NOM = ‘DUPONT’
Ne sont généralement pas sargeables:

• les LIKE en début de mot (sauf à inverser)
• les LIKE en plein mot (sauf construction particulière8 )
• les expressions logiques contenant des OR
• les expressions calculées dont la colonne faite partie de l’expression
• les colonnes qui sont utilisées par une fonction
• des fourchettes d’exclusion
• le NOT
• … Le pire vient sans doute d’expressions telles que celle-ci : WHERE COL1 = COL2

A priori une expression sargeable… Et bien dans certains cas… Non ! En effet, si les types SQL utilisés par COL1 et COl2 ne sont pas strictement les mêmes, le moteur SQL devra transformer les données d’une colonne dans l’autre à l’aide d’une fonction et de ce fait l’index ne pourra être utilisé !

Par exemple, si COL1 est indexé et de type INTEGER et COL2 de type FLOAT, alors l’index ne sera pas utilisé car l’expression s’écrit en fait : WHERE CAST(COL1 AS FLOAT) = COL2 Attention aussi aux index composés de plusieurs colonnes. Seule la vectorisation des colonnes9 est sargeable. De plus la collecte des statistiques ne se fait que sur la première colonne. Veillez donc à ce qu’elle soit la plus discriminante, la plus sélective possible.

Par exemple l’index suivant : CREATE INDEX X_PRS ON T_PERSONNE (PRS_CIVILITE, PRS_PRENOM, PRS_NOM) … ne sera jamais utilisé dans les clauses suivantes : WHERE PRS_PRENOM = ‘Alain’ WHERE PRS_NOM = ‘DUPONT’ WHERE PRS_PRENOM = ‘Alain’ AND PRS_NOM = ‘Dupont’ Il sera faiblement exploité si le prédicat de recherche contient la colonne PRS_CIVILITE, car les valeurs de cette colonne ont une distribution très faible (M, Mme, Mlle). A l’évidence, cet index aurait dû être constitué comme ceci : CREATE INDEX X_PRS ON T_PERSONNE (PRS_NOM, PRS_PRENOM, PRS_CIVILITE) …au moins les deux dernières requêtes en auraient bénéficié.

Depuis la version 2005, SQL Server permet de rajouter des colonnes d’information à l’index. Ces colonnes ne font pas partie de l’index (et ne sont donc pas triées avec les données de l’index) mais peuvent servir à mieux couvrir une requête. Un index est dit couvrant si sa seule lecture permet de traiter entièrement la requête. A titre d’exemple intéressons-nous à la table suivante : CREATE TABLE T_EMPLOYE_EMP (EMP_ID INT NOT NULL IDENTITY PRIMARY KEY, EMP_NOM CHAR(32) NOT NULL, EMP_PRENOM VARCHAR(25), EMP_MATRICULE CHAR(8) NOT NULL UNIQUE, EMP_CIVILITE CHAR(4), EMP_DATE_NAISS DATETIME) Cette table contient en fait deux index, et tous deux ont été créés de manière sous jacente aux contraintes. Le premier index est de type "cluster". C’est celui de la clef primaire. Il s’agit en fait de la table toute entière triée sur la clef. SQL Server par défaut créé des index cluster pour chaque PRIMARY KEY.

Le second est un index secondaire, c’est-à-dire qu’il consiste en une copie triée des données indexées avec en sus la référence à la ligne originale, c’est-à-dire la clef de la table (à défaut de clef, la référence à la ligne de table est composée de trois éléments : le n° du fichier de stockage des données, le n° de la page dans le fichier, la position de la ligne dans la page…). Créons maintenant un index de toute pièce comme ceci : CREATE X_EMP1 ON T_EMPLOYE_EMP (EMP_NOM, EMP_PRENOM) La requête suivante : SELECT EMP_ID, EMP_PRENOM, EMP_NOM FROM T_EMPLOYE_EMP WHERE EMP_NOM LIKE ‘M%’ … est non seulement sargeable, mais l’index utilisé est couvrant.

En effet, il contient bien le nom, mais aussi le prénom et la référence à la ligne qui n’est autre que la clef, en l’occurrence la colonne EMP_ID. Point n’est alors besoin pour le moteur SQL d’aller lire en sus des données complémentaire dans la table. Mais si vous ne cherchez jamais sur la combinaison nom + prénom, alors préférez une couverture plus douce en utilisant l’option INCLUDE. Cela induira moins d’effort au moteur SQL lors des mises à jour : CREATE X_EMP2 ON T_EMPLOYE_EMP (EMP_NOM) INCLUDE (EMP_PRENOM)

Avec cette nouvelle syntaxe, la colonne EMP_PRENOM sera stockée de manière purement informationnelle et conduira au même effet à moindre coût ! Je ne vous ai pas encore tout dit sur l’indexation. Il reste encore beaucoup de choses à évoquer, comme les index sur les colonnes calculées ou les vues indexées qui sont en fait une forme élégante de dénormalisation. Il reste aussi beaucoup de choses à élaborer dans les labos de recherche des universités et des éditeurs informatiques.

[8] Nous nous sommes amusés à rendre cette expression sargeable pour des besoins de manipulation de grands textes, mais cela nécessite une construction particulière à base d’indexation textuelle et d’index rotatifs.
[9] Cette vectorisation des colonnes fait référence à la technique du Row Value Constructor… En effet, dans une liste de données dont chaque élément précise le précédent, la recherche d’un élément situé au milieu du vecteur n’a pas de sens. Par exemple, dans le cadre d’une donnée temporelle dont la précision va de l’heure à la seconde, la recherche d’un minutage précis n’a pas de sens…