Oracle Database makes it easy to not only write and execute SQL from within PL/SQL, but also to execute your own user-defined functions inside SQL. Suppose, for example, I have built the following function to return a sub-string between start and end locations:
FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN INTEGER , end_in IN INTEGER ) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END betwnstr;
I can then call it in a SQL statement:
SELECT bewtnstr (last_name, 3, 6) FROM employees
But there’s a catch (well, of course, right? No free lunches.). When the SQL engine encounters the PL/SQL function, it has to switch context
to the PL/SQL engine to execute the function. Before it can do the switch or hand-off, it must also prepare the values to pass as actual arguments to the formal parameters of the function.
All of that takes time. And we’d much rather it didn’t. Since, however, we live in the real world and not a fantasy world, the best we can hope for is that the PL/SQL dev team would do their darnedest to reduce the overhead of that context switch
Introducing (in Oracle Database 12c Release 1) the UDF pragma. Add this statement to your function as follows:
FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN INTEGER , end_in IN INTEGER ) RETURN VARCHAR2 IS PRAGMA UDF; BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END betwnstr;
And you will, in effect, be telling the PL/SQL compiler:
I plan to call this function mostly (or maybe even always) from a SQL statement. So please do some of the work you’d usually do at run-time right now, at compile-time.
And – wonder of wonders! – the PL/SQL compiler listens to your request and does indeed take some steps at compile-time, thereby reducing the runtime overhead of the context switch.
Version Run Time average (secs) Traditional PL/SQL 0.33 PRAGMA UDF PL/SQL 0.08
Nice. Very nice. And with such a small change to your code!
One thing to keep in mind: the performance of the UDF-ied function could actually degrade a bit when run natively in PL/SQL (outside of a SQL statement). So the use of this pragma is best reserved for those cases when you are quite certain the function will almost always be executed from within SQL.