Oracle – Performance – Créer une fonction renvoyant une pseudo-table


Dans des cas particuliers de requêtes complexes, il est parfois utile de réécrire ces requêtes dans des fonctions stockées afin d’en améliorer les performances. Lorsqu’une requête devient particulièrement complexe, l’optimiseur Oracle atteint ses limites en terme de plans d’exécution et de parcours des données et peut ne plus être en mesure de renvoyer le résultat dans un temps raisonnable.

Par le passé, il m’est arrivé à quatre reprises d’avoir été confrontée à des requêtes tellement complexes que l’optimiseur statistique d’Oracle, même aidé avec des Hints et des SQL Profile ne soit pas en mesure de les exécuter dans un temps raisonnable.

Il devient alors nécessaire de trouver une méthode permettant de rétablir la performance de la requête posant problème.

Préambule

Les fonctions renvoyant des pseudo-tables sont particulièrement utiles pour résoudre ces problèmes de performances, à condition d’avoir une idée précise des optimisations faisables en décomposant ce type de requêtes sans en modifier bien entendu le résultat.

Requête lente

Pour le besoin de l’exemple, nous considérerons la requête ci-dessous comme lente et ne pouvant plus être optimisée ni par des Hints, ni par des SQL Profile :

Eléments de réécriture de la requête sous forme de fonction

Utilisation de tables temporaires

Les tables temporaires vont permettre de stocker les résultats intermédiaires de la requête globale. L’utilisation des tables temporaires permet une optimisation très importante des performances dans ce type de cas.

Fonction retournant une pseudo-table

Pour pouvoir faire un SELECT sur une fonction, celle-ci doit retourner une pseudo-table.

Le retour d’une pseudo-table en résultat d’une fonction s’appuie sur l’utilisation des types object et table pour la constitution du résultat.

La fonction retournera une table d’objets.

Cas particulier de l’opérateur UNION

L’opérateur UNION dans une requête retourne uniquement les lignes différentes des requêtes mises en union. L’opérateur UNION ne retourne qu’une et une seule ligne en doublon provenant des requêtes qu’il traite.

De ce fait cet opérateur peut s’avérer très gourmand sur de grands volumes de données comportant de nombreux doublons.

Dans une fonction PL/SQL, l’opérateur UNION peut être remplacé très avantageusement par l’instruction MERGE puisqu’il est question de décomposer la requête initiale en plusieurs requêtes plus faciles à traiter unitairement.

Requête réécrite sous forme de fonction

Création des tables temporaires

Les deux tables temporaires ci-dessous permettent d’une part de stocker le résultat de la clause WITH de la requête initiale, et d’autre part de stocker le résultat de la requête pas à pas pour la deuxième :

Création des types OBJECT et TABLE

Les deux types créés ont pour vocation de permettre de constituer la pseudo-table en résultat de l’exécution de la fonction. Le type de type OBJECT décrit une ligne de la pseudo-table. Le type de type TABLE d’OBJECT permet la création d’une table résultat de l’exécution de la fonction :

Création de la fonction

La fonction en elle-même utilise plusieurs concepts :

  • PRAGMA AUTONOMOUS_TRANSACTION :
    La fonction effectuant des opérations d’effacement, d’insertion, de fusion (MERGE) ne peut être exécutée au sein de la transaction principale. Elle doit s’exécuter en transaction autonome. Ceci n’est pas un souci, les tables manipulées étant des tables temporaires, donc ne laissant aucune trace en fin de transaction ;
  • MERGE :
    L’utilisation de cette instruction de fusion permet ici de résoudre le travail fait initialement par l’opérateur UNION.
    De l’instruction MERGE n’est utilisée que l’opérateur WHEN NOT MATCHED permettant l’insertion uniquement des lignes non-présentes dans la table en construction, ce que nous voulons pour résoudre l’opérateur UNION filtrant ;
  • <variable>.EXTEND :
    La procédure EXTEND pour une variable de type TABLE permet d’ajouter un élément à la fin de cette variable, construisant ainsi ligne après ligne la pseudo-table à renvoyer en résultat de la fonction.

La fonction est ainsi constituée :

Interrogation de la fonction

L’interrogation de la fonction se fait très simplement :

Gains de performances obtenus en environnement réel

Cette méthode de réécriture de requête sous forme de fonction a permis des gains de performances assez importants, permettant de diviser par 100 à plus de 1000 fois le temps d’exécution par rapport au temps d’exécution de la requête initiale.

L’utilisation combinée des tables temporaires, des types de données personnalisés et de l’instruction MERGE contribue fortement à ces gains de performances.