Créer un index de fonction

A partir de la table personne (num_secu char(13),
nom varchar2(250),
prenom varchar2(250),
sexe char(1)),
notre traitement à optimiser est la vérification de la clé de sécurité sociale saisie, le choix a été fait de ne pas stocker cette clé, mais de la calculer, par contre la création d'un index sur ce calcul va permettre de garder une persistence de celle-ci.

Voici, en premier lieu, la fonction permettant le calcul de la clé de sécurité sociale :

CREATE OR REPLACE FUNCTION cle_secu(num_secu IN char(13)) RETURN char(2) DETERMINISTIC IS
BEGIN
RETURN (lpad(97-mod(num_secu,97), 2, 0));
EXCEPTION when others then
RETURN (NULL);
END;
/


Le mot clé deterministic est obligatoire, il permet d'indiquer à Oracle que pour une valeur d'entrée, il existe une et une seule valeur de sortie, quelques soient les paramètres extérieurs.
Sans celui-ci, l'erreur ORA-30553 (The function is not deterministic) apparaîtra. Il n'est pas possible de créer un index à partir d'une fonction non déterministe (exemple renvoyer le mois de sysdate ou l'état d'un objet de la base). Si la fonction n'est pas déterministe et qu'on lui spécifie tout de même, Oracle renverra une erreur à la création de la fonction.

A partir de cette fonction, on crée un index de fonction :

create index idx_verif_secu on personne(num_secu, cle_secu(num_secu));

Ainsi lorsque la requête suivante est exécutée,

select nom, prenom from personne where num_secu ='13424623478253' and cle_secu(num_secu) = '80'


voici son plan d'exécution :

SELECT STATEMENT Optimizer Mode=CHOOSE
TABLE ACCESS BY INDEX ROWID personne
INDEX RANGE SCAN idx_verif_secu


Remarque, il est tout à fait possible (et même généralement le cas) de créer un index sur une fonction oracle déjà existante (nvl, ltrim, upper, decode, ...)

Un autre exemple mettant en place l'index de fonction.

A noter l'apparition des colonnes virtuelles en 11g permettant de simplifier le mécanisme décrit ci-dessus.


Voir aussi :
- Pourquoi mon index n'est pas utilisé ?
- Les index B-tree
- Les index bitmap
- Les statistiques Oracle
- Améliorer les performances d'une vue
- Hint oracle et choix du plan d'exécution
- Comparer l'exécution d'une requête avec 2 index différents
- Les colonnes virtuelles en 11g




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories