SQL Transpiler

The SQL Transpiler feature aims to reduce the overhead of function calls in SQL queries by automatically converting PL/SQL functions into SQL expressions. This allows the database to more efficiently utilize the underlying execution engine when processing complex queries, thereby improving query execution efficiency.

Enable the SQL Transpiler feature

To enable the SQL Transpiler feature, you need to set the SQL_TRANSPILER variable. This variable can be set at the global or session level, allowing for flexible control over the SQL Transpiler's activation status. For more information about this variable, please refer to SQL_TRANSPILER

Conversion mechanism

The SQL Transpiler primarily targets PL/SQL functions that are frequently called in SQL queries. By analyzing the internal logic of these functions, the system determines whether they can be directly converted into SQL operations. For example, if a function contains only simple arithmetic operations or string processing, the SQL Transpiler will convert it into the corresponding SQL expression. This not only reduces performance losses caused by context switching but also makes the query plan more concise and clear, facilitating the database optimizer in generating more efficient execution plans.

Examples

1. Execute the following command to create a function named add_numbers.

   obclient> create or replace function add_numbers(a in number, b in number)
   return number
   is
   begin
   return a + b;
   end;
   /
   

2. Execute the following command to view the execution plan without optimization.

   obclient> EXPLAIN SELECT add_numbers(1, 2) FROM dual;
   Query Plan
   ==========================================
   |ID|OPERATOR  |NAME|EST.ROWS|EST.TIME(us)|
   ------------------------------------------
   |0 |EXPRESSION|    |1       |1           |
   ==========================================
   Outputs & filters:
   -------------------------------------
   0 - output([ADD_NUMBERS(1, 2)]), filter(nil)
       values({ADD_NUMBERS(1, 2)})
   

3. Query the results.

   obclient> SELECT add_numbers(1, 2) FROM dual;
   +------------------+
   | ADD_NUMBERS(1,2) |
   +------------------+
   |                3 |
   +------------------+
   

4. Enable SQL Transpiler optimization.

   obclient> ALTER session SET sql_transpiler = 'on';
   

5. View the execution plan after optimization.

   obclient> EXPLAIN SELECT add_numbers(1, 2) FROM dual;
   Query Plan
   ==========================================
   |ID|OPERATOR  |NAME|EST.ROWS|EST.TIME(us)|
   ------------------------------------------
   |0 |EXPRESSION|    |1       |1           |
   ==========================================
   Outputs & filters:
   -------------------------------------
   0 - output([:0 + :1]), filter(nil)
       values({:0 + :1})
   

   After optimization, the execution plan output becomes output([:0 + :1]). This is a significant change. The database's SQL Transpiler has successfully identified the logic (a + b) within the add_numbers function and converted it into the equivalent SQL addition expression :0 + :1 (where :0 and :1 are placeholder bind parameters corresponding to the input values 1 and 2). This means that during actual execution, the database will no longer call the PL/SQL function but will instead handle it like a simple 1+2 expression, significantly reducing execution overhead.

6. Verify the query results after optimization.

   obclient> SELECT add_numbers(1, 2) FROM dual;
   +------------------+
   | ADD_NUMBERS(1,2) |
   +------------------+
   |                3 |
   +------------------+