Le blog de SQLpro

Un article de SQLpro    23 Commentaires

Le terme anglais « SARGABLE » indique si une expression de filtrage (prédicat) peut utiliser ou non une recherche dans un index. Si c’est le cas, le prédicat est dit sargable, l’index est utilisé pour une recherche et le temps de réponse sera excellent. Sinon, le temps de réponse sera mauvais car il faudra balayer toutes les lignes de la table pour satisfaire la dempande… Mais quelles sont les expressions « sargable », peut t-on transformer une expression non sargable en expression sargable ? Voici quelques questions et les réponses associées, avec des exemples sous MS SQL Server…

Le terme « SARGABLE » vient de la contraction de l’expression anglaise « Search ARGument Able » c’est à dire « capable d’utiliser un argument de recherche ».

1 – RÈGLE GÉNÉRALE

Sont sargable :
les recherches qui portent sur le même sens tri que celui effectué pour la structuration de l’index (alphabétique pour des chaînes de caractères, numérique pour des nombres, temporels pour des dates…).
Ne sont pas sargable :
Le NOT n’est en principe jamais sargable, tout comme le OR et le IN avec plusieurs valeurs.
De même une colonne à laquelle on applique une opération, tout comme une colonne « noyée » dans une fonction n’est en principe jamais sargable. À ce sujet, méfiez vous des transtypages implicites !

2 – DÉTAILS

Nous disposons d’une table avec les colonnes suivantes :

Sont sargable :

Ne sont pas sargable :

NOTA : bien que certains prédicats de cette liste soient présentés comme non sargable, certains optimiseurs arrivent à récrire la requête pour rendre le prédicat sargable (algébrisation).

Non sargable, rendu sargable :

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'                   | ennoloC LIKE 'ruelav%' 

-----------------------------------------|------------------------------------- 

Colonne LIKE 'val%eur'                   | Colonne LIKE 'val%' 

                                         | INTERSECT 

                                         | ennoloC LIKE 'rue%'  

-----------------------------------------|------------------------------------- 

Colonne IN (valeur1, valeur2)            | Colonne = valeur1 

                                         | UNION [ALL] 

                                         | Colonne = valeur2

NOTA : ennoloC étant le contenu de la colonne « Colonne » inversé (par exemple à l’aide de la fonction REVERSE) et stocké soit en colonne calculée persistante (MS SQL Server), soit sous forme d’index sur expression (PostGreSQL)

À SAVOIR : Il est possible de rendre la recherche de : Colonne LIKE ‘%valeur%’, sargable, en utilisant une technique d’index rotatif que les SGBDR n’ont pas encore intégrés actuellement mais qu’il est possible de simuler dans une table SQL.

3 – TRANSFORMATION SPÉCIFIQUES

Quelques problématiques plus subtiles…

Avec MS 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                | COL_DATE >= '2008-09-01' AND  

MONTH(COL_DATE) = 9                      | COL_DATE < '2009-01-30'  

-----------------------------------------|----------------------------------------- 

DATEDIFF(yy, COL_DATE, GETDATE()) > 18   | COL_DATE <= DATEADD(yy, 18, GETDATE()) 

-----------------------------------------|-----------------------------------------  

UPPER(COL_CHAINE) = 'MARTIN'             | COL_CHAINE = 'MARTIN'  

                                         | COLLATE French_CI_AS 

-----------------------------------------|-----------------------------------------  

COL_CHAINE = 75001                       | COL_CHAINE = '75001'  

-----------------------------------------|-----------------------------------------  

COL_NOMBRE = '1999'                      | COL_NOMBRE = 1999 

-----------------------------------------|-----------------------------------------

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *