How to Speed Up SQL Calls with PRAGMA UDF

Calling a PL/SQL function from a SQL query has a cost for every row: a switch between the SQL engine and the PL/SQL engine. For a short function called on millions of rows, that switch can cost more than the function itself. PRAGMA UDF compiles the function to be called from SQL, which reduces the overhead.

Code for This Guide

The main examples are in the examples/subprograms folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create [or replace] function name (params) return type is
  pragma udf;
begin
  ...
end;

The pragma goes in the declaration section. The function is still callable from PL/SQL, though calls from PL/SQL may become slightly slower.

A Function Optimized for SQL

Example:

create or replace function fare_with_tax (p_fare number) return number is
  pragma udf;                       -- optimized for calls from SQL
begin
  return round(p_fare * 1.05, 2);
end;
/
select ticket_id, fare, fare_with_tax(fare) as with_tax from tickets where ticket_id <= 3;

Output:

Function FARE_WITH_TAX compiled

   TICKET_ID      FARE    WITH_TAX
____________ _________ ___________
           1    513.01      538.66
           2    125.04      131.29
           3    130.69      137.22

FARE_WITH_TAX is called once per ticket from the query; with PRAGMA UDF, each call costs less.

Other Ways to Avoid the Switch

The SQL transpiler is controlled by a parameter, off by default:

Example:

select name, value from v$parameter where name = 'sql_transpiler';

Output:

NAME              VALUE
_________________ ________
sql_transpiler    OFF

Things to Know

• The gain is largest for short functions called from SQL on many rows.
• Measure with your own data: the effect depends on how much work the function does per call.
• PRAGMA UDF does not cache anything; combine it with DETERMINISTIC or RESULT_CACHE where they apply.

Related Guides

• How to Define PL/SQL Functions in the WITH Clause
• How to Cache Function Results with RESULT_CACHE in PL/SQL

Conclusion

PRAGMA UDF compiles a PL/SQL function for calls from SQL, reducing the per-row cost of switching engines. Use it for short functions called from queries on many rows, and consider WITH FUNCTION, SQL macros, or the SQL transpiler as alternatives.