Prédicats sargables : réécrire les filtres pour utiliser un index
Règles de sargabilité et réécritures courantes de filtres SQL Server pour qu'ils utilisent un index.
Un prédicat est sargable (de l'anglais Search ARGument Able) s'il peut utiliser un index pour la recherche. Sinon, le moteur doit souvent parcourir toutes les lignes de la table. Cet article résume les règles et les réécritures courantes sous SQL Server, d'après un article de SQLpro (lien source).
Règles générales
- Un prédicat est sargable lorsque la colonne est comparée à une valeur, sans transformation, dans le sens de tri de l'index.
- Les opérateurs
NOT,<>,ORetINavec plusieurs valeurs ne sont en principe pas sargables. - Une colonne placée dans un calcul ou dans une fonction n'est en principe pas sargable. Attention aussi aux conversions implicites de type.
- Un
LIKEdont le motif commence par un caractère générique ('%valeur','%valeur%') ne peut pas utiliser un index classique.
Certains optimiseurs réécrivent eux-mêmes ces prédicats. Le tableau ci-dessous est donc un guide, à vérifier dans le plan d'exécution.
Réécritures générales
| Non sargable | Réécriture sargable |
|---|---|
Colonne OPERATEUR valeur2 = valeur1 |
Colonne = valeur1 OPERATEUR valeur2 |
Colonne1 = valeur1 OR Colonne2 = valeur2 |
Colonne1 = valeur1 UNION [ALL] Colonne2 = valeur2 |
NOT Colonne < valeur |
Colonne >= valeur |
NOT Colonne BETWEEN valeur1 AND valeur2 |
Colonne < valeur1 UNION [ALL] Colonne > valeur2 |
Colonne LIKE '%valeur' |
Recherche sur la colonne inversée : ennoloC LIKE 'ruelav%' |
Colonne LIKE 'val%eur' |
Colonne LIKE 'val%' INTERSECT ennoloC LIKE 'rue%' |
Colonne IN (valeur1, valeur2) |
Colonne = valeur1 UNION [ALL] Colonne = valeur2 |
ennoloC est la colonne inversée (fonction REVERSE), stockée dans une colonne calculée persistante et indexée.
Un LIKE '%valeur%' peut être rendu sargable avec une technique d'index rotatif, que les SGBD n'intègrent pas encore nativement mais qui peut être simulée dans une table.
Cas particuliers sous SQL Server
| Non sargable | Réécriture sargable |
|---|---|
YEAR(COL_DATE) = 2008 |
COL_DATE >= '2008-01-01' AND COL_DATE < '2009-01-01' |
YEAR(COL_DATE) = 2008 AND MONTH(COL_DATE) = 9 |
COL_DATE >= '2008-09-01' AND COL_DATE < '2008-10-01' |
DATEDIFF(yy, COL_DATE, GETDATE()) > 18 |
COL_DATE < DATEFROMPARTS(YEAR(GETDATE()) - 18, 1, 1) |
UPPER(COL_CHAINE) = 'MARTIN' |
COL_CHAINE = 'MARTIN' COLLATE French_CI_AS |
COL_CHAINE = 75001 |
COL_CHAINE = '75001' |
COL_NOMBRE = '1999' |
COL_NOMBRE = 1999 |
La réécriture de UPPER suppose une collation insensible à la casse, ici French_CI_AS. Dans les deux derniers cas, le littéral est aligné sur le type de la colonne : sinon SQL Server convertit la colonne, ce qui empêche l'usage de l'index.