Appel de fonction PL/SQL dans un énoncé SQL

J’ai remarqué que plusieurs cas de lenteur ont été soulevés par le fait que des fonctions PL/SQL sont utilisées dans les clauses WHERE des énoncés SQL d'une vue utilisée par une application développé sous Oracle Application Express (APEX).

Par exemple, lorsque la fonction est appelée de cette façon, l’optimiseur d’Oracle l’exécute à toutes les rangées même si les valeurs passées sont toujours les mêmes :

SELECT empl.nom_empl, empl.prn_empl, clien.ide_clien, mand.num_contt
  FROM gma_mand mand, gcl_clien clien, gem_empl empl
 WHERE mand.num_empl_gestn = empl.num_empl
   AND mand.ide_clien      = clien.ide_clien
   AND pkg_securite.verfc_acces (v('USER')) = 1;

Alors, il est recommandé de l’encapsuler dans une requête SQL (scalar subquery) car cette requête sera exécutée qu’une seul fois et ce, peu importe le nombre de rangées retournées :

SELECT empl.nom_empl, empl.prn_empl, clien.ide_clien, mand.num_contt
  FROM gma_mand mand, gcl_clien clien, gem_empl empl
 WHERE mand.num_empl_gestn = empl.num_empl
   AND mand.ide_clien      = clien.ide_clien
   AND (SELECT pkg_securite.verfc_acces (v('USER')) FROM DUAL) = 1;

L’appel à des stored procedure dans des énoncés SQL peut être très couteux en ressource. Dans le cas rencontré avec la vue, l’exécution de la stored procedure était effectuée pour chaque enregistrement retourné. En l’englobant dans un énoncé SQL (select … from dual), Oracle ne l’exécute qu’une seule fois. Un important gain en performance a été constaté dès que le changement a été mis en place.