Speed up execution of your functions inside SQL statements with UDF pragma

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

Nice, right?

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.

For an excellent, in depth exploration of the performance impact of UDF, check out this blog post from Martin Widlake. Here's the summary in terms of his performance example:

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.