Fonctions définies par l'utilisateur

L'indexation fonctionnelle est une approche très générique. En dehors de fonctions comme UPPER, vous pouvez aussi indexer des expressions comme A + B et même utiliser des fonctions utilisateurs dans la définition de l'index.

Il existe cependant une exception importante. Par exemple, il n'est pas possible de faire référence à l'heure actuelle dans une définition d'index, que ce soit directement ou indirectement. En voici un exemple :

CREATE FUNCTION obtient_age(date_de_naissance DATE) 
RETURN NUMBER
AS
BEGIN
  RETURN 
    TRUNC(MONTHS_BETWEEN(SYSDATE, date_de_naissance)/12);
END;
/

La fonction OBTIENT_AGE utilise la date actuelle (avec SYSDATE) pour calculer l'âge en se basant sur la date de naissance fournie. Vous pouvez utiliser cette fonction dans toute requête, par exemple dans les clauses select et where :

SELECT prenom, nom, obtient_age(date_de_naissance)
  FROM employes
 WHERE obtient_age(date_de_naissance) = 42;

La requête obtient la liste des employés âgés de 42 ans. Utiliser un index fonctionnel est une idée évidente pour optimiser cette requête mais vous ne pouvez pas utiliser la fonction OBTIENT_AGE dans une définition d'index car cette fonction n'est pas déterministe. Cela signifie que le résultat de cette fonction n'est pas seulement déterminé par ses paramètres. Seules les fonctions qui renvoient toujours le même résultat pour les mêmes paramètres (les fonctions déterministes) peuvent être indexées.

La raison derrière cette limitation est simple. Lors de l'insertion d'une nouvelle ligne, la base de données appelle la fonction et stocke le résultat dans l'index où il restera inchangé. Aucun processus périodique ne met à jour l'index. La base de données ne met à jour l'âge indexé que si la date de naissance est modifiée par une requête update. Après le prochain anniversaire, l'âge stocké dans l'index sera mauvais.

En dehors d'être déterministe, les bases de données PostgreSQL et Oracle exigent que les fonctions soient déclarées comme étant déterministes lorsqu'elles sont utilisées dans un index. Vous devez utiliser le mot clé DETERMINISTIC (Oracle) ou IMMUTABLE (PostgreSQL).

D'autres exemples de fonctions ne pouvant pas être indexés sont les générateurs de nombres aléatoires et les fonctions qui dépendent de variables d'environnement.