Recently, I have read about UDF Pragma optimization method in Oracle Database 12.
I'm very interested in how exactly it works. I've only found very short description in the Oracle documentation.
As I understand, every Pragma in PL/SQL is some kind of compiler directive (I could be wrong here) similar to C++ Pragma Directives.
Maybe someone can explain to me in more details (or provide links :) ) how the internal mechanism of UDF Pragma works?
The internals are not made available to us, but essentially the pragma is an instruction to the compiler to focus on reducing the cost of the PL/SQL => SQL context switch overhead. If I had to hypothesise I would say that it takes advantage of a different 12c feature which allow PL/SQL directly within a CTE (common table expression), e.g.:
WITH
function myfunction(x int) return int as
begin
...
return ...
end;
select myfunction(col) from my_table
PL/SQL functions coded like the above exist solely for the duration of the SQL execution and also execute with improved performance characteristics over that of standalone functions. So I suspect the UDF pragma allows invocation of a standalone function via the same code path as the inline example above.
(But all just hypothesis on my part)