Index fonctionnel

Il est des cas ou la recherche d'une information dans une colonne de table se fait par l'application d'une fonction. Dans l'exemple suivant, on recherche le patronyme “dupont” dans la colonne nom de la table personne en prenant soin de transformer le résultat en minuscule par l'appel de la fonction lower().

SELECT nom FROM personne
  WHERE lower( nom ) = 'dupont' ;

S'il existe un index “classique” sur la colonne nom l'optimiseur ne pourra pas l'utiliser. Il devra faire une recherche séquentielle (full scan) sur toute la table en appliquant la fonction lower(), ce qui n'est pas du tout optimal. Par ailleurs l'index classique sera maintenu inutilement dès lors que toute sélection inclue une fonction.

Une réponse consiste à créer un index fonctionnel sur la colonne nom :

CREATE INDEX lownom ON personne ( lower(nom) ) ;