How Pragma UDF works?

Question

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?

Answer 1

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)