Oracle 23ai — Automatic SQL Transpiler Feature

As you may know, using a function in an SQL statement causes a context switch between the SQL engine and the PL/SQL engine. Frequent context switches can negatively impact database performance. To improve the execution speed of such queries, Oracle introduced SQL Macros in version 21c. This feature allows the query text containing a function to be rewritten in a simpler form, without using the function. In version 23ai, Oracle has introduced a new feature called Automatic SQL Transpiler. If the sql_transpiler parameter is set to ON, Oracle will automatically (and without user intervention) convert the function used in the SQL statement to a SQL expression when possible, in order to reduce the overhead caused by executing the function within SQL. Consider the following function: create or replace function sal_func(sal_param number) return number is begin return sal_param + 60; end; / Function created. If you use this function in a query like below, by default, no transformation will occur: SQL> select sal from person where sal_func(sal) = 570; However, if you enable the Automatic SQL Transpiler feature, the execution plan will change, and the function sal_func will be transformed into the expression SAL + 60: SQL> alter session set sql_transpiler = 'ON'; Session altered. SQL> select sal from person where sal_func(sal) = 570; This transformation can be verified through query tracing. Note: This feature does not apply to functions defined within a package.

Apr 7, 2025 - 17:00

Oracle 23ai — Automatic SQL Transpiler Feature

As you may know, using a function in an SQL statement causes a context switch between the SQL engine and the PL/SQL engine. Frequent context switches can negatively impact database performance.

To improve the execution speed of such queries, Oracle introduced SQL Macros in version 21c. This feature allows the query text containing a function to be rewritten in a simpler form, without using the function.

In version 23ai, Oracle has introduced a new feature called Automatic SQL Transpiler. If the sql_transpiler parameter is set to ON, Oracle will automatically (and without user intervention) convert the function used in the SQL statement to a SQL expression when possible, in order to reduce the overhead caused by executing the function within SQL.

Consider the following function:

create or replace function sal_func(sal_param number)
  return number
is
begin
  return sal_param + 60;
end;
/
Function created.

If you use this function in a query like below, by default, no transformation will occur:

SQL> select sal from person where sal_func(sal) = 570;

However, if you enable the Automatic SQL Transpiler feature, the execution plan will change, and the function sal_func will be transformed into the expression SAL + 60:

SQL> alter session set sql_transpiler = 'ON';
Session altered.

SQL> select sal from person where sal_func(sal) = 570;

This transformation can be verified through query tracing.

Note: This feature does not apply to functions defined within a package.