Calling User-Defined Functions from SQL Queries Got Faster in Oracle 12c
We should be careful with mixing SQL and PL/SQL.
The context switches between both engines can quickly kill performance.
For quite a long time now Oracle supports bulk operations in PL/SQL, minimizing these context switches when executing SQL from PL/SQL.
Version 12c of the database finally brought us similar enhancements for the other way around: the use of PL/SQL (function calls) from within a SQL statement.
Starting with that edition subquery factoring (the with clause) can also include definitions of PL/SQL-functions and (yes, even) -procedures for use inside your query.
And then there is the option to compile functions and procedures in the database/packages for use by the SQL engine, using the udf pragma.
This presentation will show both features and how and when to put them to use.
It will show what the difference in performance will be, compared to each other and to using conventional function calls.
Furthermore it will highlight the pitfalls that can be encountered.