par Guillaume Lelarge.

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

Prudence

Les bases de données PostgreSQL et Oracle font confiance aux déclarations DETERMINISTIC ou IMMUTABLE. Cela signifie qu'elles font confiance au développeur.

Vous pouvez déclarer la fonction OBTIENT_AGE comme étant déter­ministe et l'utiliser dans une définition d'index. Quelle que soit la déclaration, cela ne fonctionnera pas comme attendu car l'âge enregistré dans l'index ne sera pas mis à jour au fil des années ; les employés ne deviendront pas plus vieux, tout du moins pas dans l'index.

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.

Astuce

Comment pouvez-vous utiliser un index pour optimiser une requête sur les employés de 42 ans ?

À 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