par Guillaume Lelarge.

Recherche insensible à la casse en utilisant UPPER ou LOWER


Ignorer la casse dans une clause where est très simple. Par exemple, vous pouvez convertir en majuscule les deux côtés de la comparaison :

SELECT prenom, nom, numero_telephone
  FROM employes
 WHERE UPPER(nom) = UPPER('winand')

Quelle que soit la capitalisation utilisée dans le terme de la recherche et dans la colonne NOM, la fonction UPPER les fait correspondre.

Remarque

Une autre façon de faire des recherches insensibles à la casse est d'utiliser une « collation » différente. Les collations par défaut utilisées par SQL Server et MySQL ne distinguent pas entre lettres minuscules et majuscules. Elles sont donc insensibles à la casse par défaut.

La logique de cette requête est parfaitement raisonnable mais le plan de l'exécution ne l'est pas :

Oracle

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |   10 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYES  |   10 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("NOM")='WINAND')

PostgreSQL

                     QUERY PLAN
------------------------------------------------------
 Seq Scan on employes
   (cost=0.00..1722.00 rows=50 width=17)
   Filter: (upper((nom)::text) = 'WINAND'::text)

C'est de nouveau le parcours complet de table. Bien qu'il existe un index sur NOM, il est inutilisable parce que la recherche n'est pas sur NOM mais sur UPPER(NOM). De la perspective de la base de données, cela est complètement différent.

Ce piège est très fréquent. Nous faisons immédiatement la relation entre NOM et UPPER(NOM) et nous nous attendons à ce que la base de données fasse de même. En fait, l'optimiseur voit plutôt ceci :

SELECT prenom, nom, numero_telephone
  FROM employes
 WHERE BOITENOIRE(...) = 'WINAND';

La fonction UPPER est tout simplement une boîte noire. Les paramètres de cette fonction n'ont pas d'importance car il n'y a aucune relation entre les paramètres de la fonction et son résultat.

Astuce

Remplacez le nom de la fonction par BOITENOIRE pour comprendre le point de vue de l'optimiseur.

Évaluation du temps de compilation

L'optimiseur peut évaluer l'expression sur le côté droit lors de la compilation parce qu'il a tous les paramètres en entrée. Du coup, le plan d'exécution Oracle (section « Predicate Information ») affiche seulement la notation majuscule du terme de recherche. Le comportement est très similaire à celui d'un compilateur qui évalue les expressions constantes au moment de la compilation.

Pour que l'index puisse être utilisé sur cette requête, nous avons besoin d'un index qui couvre l'expression recherchée. Cela signifie que nous avons besoin d'un index sur UPPER(NOM) et non pas sur NOM :

CREATE INDEX nom_maj_emp
    ON employes (UPPER(nom));;

Un index dont la définition contient des fonctions ou des expressions est appelé un index fonctionnel (function based index (FBI), en anglais). Au lieu de copier directement les données de la colonne dans l'index, un index fonctionnel applique tout d'abord la fonction, puis place le résultat dans l'index. Le résultat est un index qui contient tous les noms en majuscule.

La base de données peut utiliser un index fonctionnel si l'expression exacte de la définition d'index apparaît dans une requête SQL, comme dans l'exemple ci-dessus. Le plan d'exécution confirme cela :

Oracle

--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |  100 |   41 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYES    |  100 |   41 |
|*2 |  INDEX RANGE SCAN          | NOM_MAJ_EMP |   40 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("NOM")='WINAND')

PostgreSQL

                       QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on employes
  (cost=4.65..178.65 rows=50 width=17)
  Recheck Cond: (upper((nom)::text) = 'WINAND'::text)
  -> Bitmap Index Scan on nom_maj_emp
     (cost=0.00..4.64 rows=50 width=0)
     Index Cond: (upper((nom)::text) = 'WINAND'::text)

Il s'agit d'une opération INDEX RANGE SCAN standard, décrite dans le Chapitre 1. La base de données parcourt l'index B-tree et suit la chaîne de nœuds feuilles. Il n'y a pas d'opérations dédiées ou de mots clés pour les index fonctionnels.

Attention

Quelques fois, les ORM utilisent UPPER et LOWER sans que le déve­loppeur en soit conscient. Par exemple, Hibernate injecte un LOWER implicite pour faire des recherches insensibles à la casse.

Le plan d'exécution n'est pas le même que dans la section précédente sans le UPPER ; l'estimation du nombre de lignes est trop élevée. Il est tout particulièrement étrange que l'optimiseur s'attende à récupérer plus de lignes de la table que ce que va réellement récupérer l'opération INDEX RANGE SCAN. Comment peut-il récupérer 100 lignes de la table si le parcours d'index précédent n'en renvoie que 40 ? Le fait est que cela n'est pas possible. Des estimations contradictoires indiquent souvent des problèmes de statistiques. Dans ce cas particulier, cela est dû au fait que la base de données Oracle ne met pas à jour les statistiques de la table lors de la création d'un nouvel index (voir aussi « Statistiques Oracle pour les index fonctionnels »).

Statistiques Oracle pour les index fonctionnels

La base de données Oracle maintient l'information du nombre de valeurs distinctes par colonne avec les autres statistiques de la table. Ces informations sont réutilisées si une colonne fait partie de plusieurs index.

Les statistiques étendues et les statistiques multi-colonnes sont aussi conservées au niveau de la table en tant que colonnes virtuelles. Bien que la base de données Oracle récupère les statistiques des index automatiquement pour les nouveaux index (depuis la version 10g), il ne met pas à jour les statistiques de la table. Pour cette raison, la documentation Oracle recommande de mettre à jour les statistiques de la table après création d'un index fonctionnel :

Après création d'un index fonctionnel, récupérez les statistiques sur l'index et sa table en utilisant le package DBMS_STATS. Ces statistiques seront activées par la base de données Oracle pour décider de l'utilisation de l'index.

Oracle Database SQL Language Reference

Ma recommandation personnelle va encore plus loin : après chaque changement d'index, il faut mettre à jour les statistiques de la table et de ses index. Néanmoins, cela peut avoir des effets de bord indésirables. Coordonnez cette activité avec les administrateurs de la base de données et faites une sauvegarde des statistiques avant leur mise à jour.

Après mise à jour des statistiques, l'optimiseur calcule des estimations plus précises :

Oracle

--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYES    |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | NOM_MAJ_EMP |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("NOM")='WINAND')

PostgreSQL

                      QUERY PLAN
----------------------------------------------------------
 Index Scan using nom_maj_emp on employes
   (cost=0.00..8.28 rows=1 width=17)
   Index Cond: (upper((nom)::text) = 'WINAND'::text)

As the row count estimate has decreased—from 50 in the example above down to 1 in this execution plan—the query planner prefers to use the simpler Index Scan operation.

Remarque

Les statistiques pour les index fonctionnels et pour les index multi-colonnes ont été ajoutées dans la version 11g d'Oracle.

Bien que les statistiques mises à jour n'améliorent pas les performances en exécution dans ce cas (le bon index a été correctement utilisé), il est toujours préférable de vérifier les estimations de l'optimiseur. Le nombre de lignes traitées par chaque opération (estimation de cardinalité) est une information particulièrement importante qui est aussi affichée dans les plans d'exécution de SQL Server et PostgreSQL.

Astuce

L'Annexe A, « Plans d'exécution » décrit les estimations du nombre de lignes dans les plans d'exécution pour SQL Server et PostgreSQL.

SQL Server et MySQL ne supportent pas les index fonctionnels comme décrits ci-dessus, mais offrent tous les deux un contournement avec les colonnes calculées ou générées. Pour les utiliser, vous devez tout d'abord ajouter une colonne calculée dans la table qui doit être indexée :

MySQL

À partir de MySQL 5.7, vous pouvez indexer une colonne générée de cette façon :

ALTER TABLE employees
  ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name))
CREATE INDEX emp_up_name ON employees (last_name_up)
SQL Server

ALTER TABLE employes ADD nom_maj AS UPPER(nom)CREATE INDEX nom_maj_emp ON employes (nom_maj)

SQL Server et MySQL sont capables d'utiliser cet index quand l'expression indexée apparaît dans l'instruction. Dans certains cas simples, SQL Server et MySQL peuvent utiliser cet index même si la requête n'est pas modifiée. Parfois néanmoins, la requête doit être modifiée pour faire référence au nom des nouvelles colonnes présentes dans l'index. N'hésitez pas à vérifier le plan d'exécution en cas de doute.

À 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.

“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