Améliorer les performances d'une vue

Soit une vue fréquemment utilisée basée sur une jointure de deux tables :

create view jt1t2 as
select * from t1 inner join t2 on (t1.idA = t2.idA)
where t1.colA = 'Val1' and t2.colB = 'Val2'


A priori, pour améliorer les performances de cette vue, un index sur chacune des colonnes clés des tables a été créé, un index sur colA et un index sur colB.

Si celà ne suffit pas (et en gardant à l'esprit que toute modification d'index doit faire l'objet d'une étude d'impact sur les autres objets et requêtes), on peut utiliser un seul index par table : ix1 sur t1.idA et t1.colA et ix2 sur t2.idA et t2.colB.

Imaginons maintenant, que cette requête soit spécifique et que la valeur val1 dans colA et val2 dans colB soient rares parmi des millions de tuples, nous nous retrouvons à indexer un grand nombre de valeur pour rien.
Nous savons que la création d'un index (B-tree) ne prend pas en compte les valeurs nulles.
Le tuning de la requête SQL que nous allons mettre en place s'appuie sur cette particularité : créer une fonction particulière à cette vue, renvoyant null lorsque le tuple n'intervient pas dans la requête et indexer ce résultat.

Création des fonctions :

create funtion fct1_jt1t2 (vt1 varchar2(250)) return number deterministic is
begin
if (vt1='Val1') then
return true
else
return null
end if;
end;
/


create funtion fct2_jt1t2 (vt2 varchar2(250)) return number deterministic is
begin
if (vt2='Val2') then
return true
else
return null
end if;
end;
/


Création des index :

create index ix1_t1_jt2 (fct1_jt1t2(colA));
create index ix2_t2_jt1 (fct2_jt1t2(colB));


Recréation de la vue :

create or replace view jt1t2 as
select /*+ index(t1 ix1_t1_jt2) index(t2 ix2_t2_jt1) */
t1 inner join t2 on (t1.idA = t2.idA)
where fct1_jt1t2(colA) and fct2_jt1t2(colB);


L'utilisation du hint est facultative si les statistiques sont correctement calculées (voir "Pourquoi mon index n'est pas utilisé ?").

Il faut toujours garder à l'esprit que la création d'index permet un gain de performance à la sélection mais une perte de performance lors d'insertions/modifications/suppressions dans les tables concernées (ici t1 et t2).




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

Catégories