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 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 flexible control over the SQL Transpiler's activation status. For more information about this variable, see SQL_TRANSPILER

Conversion mechanism

The SQL Transpiler focuses on PL/SQL functions frequently called in SQL queries. By analyzing the function logic, the system determines if it can be directly converted into SQL operations. For example, if the function contains simple arithmetic or string manipulation, the SQL Transpiler converts it into the corresponding SQL expression. This reduces performance overhead from context switching and simplifies the query plan, making it easier for the database optimizer to generate 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 verification results.

   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 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 are parameter placeholders). This is the core manifestation of the SQL Transpiler in action.

6. Verify the optimized query results.

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