SQL Transpiler

One of the flexibilities of Oracle database is that it let’s you to migrate from Third party database with minimal change in the application source code.
 
Let’s say we have application supported on SQL Server database that got queries like this (involving sql-server specific functions “DAY” )
 
select *
from emp
where day(HIREDATE) < 10;
 
as part of application database migration to Oracle database, the above queries will end up with errors
 
demo@PDB1> select *
  2  from emp
  3  where day(HIREDATE) < 10 ;
where day(HIREDATE) < 10
      *
ERROR at line 3:
ORA-00904: "DAY": invalid identifier
 
To fix that error, we will either
 
Re-write the queries using the equivalent function available in Oracle database  to_number(to_char(hiredate,’DD’)), which will require the application code change, followed by testing.
 
In order to support migration with no application changes, most experience professional will create custom function in database like this.
 
demo@PDB1> create or replace function day(p_hiredate date)
  2  return number
  3  as
  4  begin
  5     return to_number(to_char(p_hiredate,’DD’)) ;
  6  end;
  7  /
 
Function created.
 
demo@PDB1> select *
  2  from emp
  3  where day(HIREDATE) < 10;
 
        ID      EMPNO ENAME       JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- ----------- --------- ---------- ----------- ---------- ---------- ----------
         4       7566 JONES       MANAGER         7839 02-APR-1981       2975                    20
         6       7698 BLAKE       MANAGER         7839 01-MAY-1981       2850                    30
         7       7782 CLARK       MANAGER         7839 09-JUN-1981       2450                    10
        10       7844 TURNER      SALESMAN        7698 08-SEP-1981       1500          0         30
        12       7900 JAMES       CLERK           7698 03-DEC-1981        950                    30
        13       7902 FORD        ANALYST         7566 03-DEC-1981       3000                    20
 
6 rows selected.
 
demo@PDB1>
 
However having the function call in the predicates, will lead to context switch between the sql and pl/sql runtime engine and lead to additional performance issue.
 
So how can we avoid that? If we are with Oracle 21c and above we can take the advantage of SQL (scalar) Macro technology
 
demo@PDB1> select *
  2  from emp
  3  where day(HIREDATE) < 10 ;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    41 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    41 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DAY"("HIREDATE")<10)
 
demo@PDB1>
demo@PDB1> create or replace function day(p_hiredate date)
  2  return varchar2
  3  sql_macro(scalar)
  4  as
  5  begin
  6     return q'# to_number(to_char(p_hiredate,’DD’)) #';
  7  end;
  8  /
 
Function created.
 
demo@PDB1> select *
  2  from emp
  3  where day(HIREDATE) < 10 ;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    41 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    41 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'DD'))<10)
 
demo@PDB1>
 
 
with SQL Macro in place, the function call in the predicates got replace with the expression in the function, this will reduce the context switch between sql and pl/sql runtime environments.
 
Of course you could use the same technology in 23c now. However in 23c, Oracle goes one step further and introduces an automatic conversion with the SQL Transpiler even for non-macro functions. You only need to setup and enable the transpiler functionality with the new parameter SQL_TRANSPILER. No further manual interaction is required. The SQL Transpiler is disabled by default. You can enable it with an ALTER SYSTEM or ALTER SESSION command and change the parameter value accordingly. When SQL_TRANSPILER is set to ON, the SQL transpiler feature is enabled and PL/SQL functions are automatically transpiled (converted) into SQL expressions whenever possible. When this parameter is set to OFF, the SQL Transpiler feature is disabled
 
demo@FREEPDB1> create or replace function day(p_hiredate date)
  2  return number
  3  as
  4  begin
  5     return to_number(to_char(p_hiredate,'DD')) ;
  6  end;
  7  /
 
Function created.
 
demo@FREEPDB1> set autotrace traceonly exp
demo@FREEPDB1> select * from emp where day(hiredate) <= 10;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DAY"("HIREDATE")<=10)
 
demo@FREEPDB1> alter session set sql_transpiler = ON;
 
Session altered.
 
demo@FREEPDB1> select * from emp where day(hiredate) <= 10;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'DD'))<=10)
 
demo@FREEPDB1>
 
the function call in the predicates is replace with expression and can be seen in the Predicate Information section, this Indicates that transpilation has occurred.