|
Saturday, April 20, 2024
Doing PL/SQL from SQL with Correctness and Performance
Bryn Llewellyn, Distinguished Product Manager
Oracle Corp.
Oracle Database 12c brought 'pragma UDF', and the equivalent possibility to declare a function in a subquery's 'with' clause, to speed up the SQL to PL/SQL round trip. But, earlier, all you had was caching: by using package global collections; by labeling a function 'deterministic'; by invoking the function in a scalar subquery; or by using the PL/SQL function result cache. It would seem, at first, that caching should be used with caution because of its potential semantic effect. This session shows that a function must have certain properties to be safe even without caching, and that such a function will always be safe to cache. You don't any longer rely on 'group by' to do ordering. So don't rely on the ordering of function invocation.
Bryn Llewellyn is a Distinguished Product Manager at Oracle Corporation, responsible for PL/SQL and Edition-Based Redefinition. Bryn has been with Oracle for over 25 years and is currently based at their Headquarters in Redwood Shores, CA. With a background in Experimental Physics and Mathematics, Bryn is not known for his brevity, but he is known for the precision with which he expresses his thoughts. Steven Feuerstein, often regarded as the ultimate PL/SQL guru, says on his blog, "Bryn surely has the most thorough and clear understanding of the PL/SQL language of anyone I have met (definitely including me)."
|
|
 |
