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.
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éveloppeur 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 »).
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 Scanoperation.
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.
