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 enabled state. For more information about this variable, 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 the performance overhead 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 function add_numbers(a int, b int)
   returns int
   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(cast(1, INT(11, 0)), cast(2, INT(11, 0)))]), filter(nil)
         values({add_numbers(cast(1, INT(11, 0)), cast(2, INT(11, 0)))})
   

3. Query the results to verify.

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

4. Enable SQL Transpiler optimization.

   obclient> 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([column_conv(INT,PS:(11,0),NULL,cast(:0, INT(-1, 0))) + column_conv(INT,PS:(11,0),NULL,cast(:1, INT(-1, 0)))]), filter(nil)
         values({column_conv(INT,PS:(11,0),NULL,cast(:0, INT(-1, 0))) + column_conv(INT,PS:(11,0),NULL,cast(:1, INT(-1, 0)))})
   

   After enabling the optimization, the execution plan output becomes output([column_conv(INT...) + column_conv(INT...)]). The optimizer's output clearly indicates that the original add_numbers(1, 2) function call has been replaced with the direct addition expression :0 + :1 (where :0 and :1 represent parameter placeholders). This is the core manifestation of the SQL Transpiler in action.

6. Verify the results of the optimized query.

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