Collection of stored procedure information and its impact on parallel execution of SQL statements

During compilation, OceanBase Database collects information about whether a stored procedure contains factors such as DML statements and package variable reads/writes. The information is mainly used to determine whether a DML statement with a user-defined function (UDF) or trigger requires parallel execution in nested scenarios.

OceanBase Database collects the following information about a stored procedure:

• Whether the stored procedure contains read/write SQL statements, including data manipulation language (DML), data definition language (DDL), and transaction control language (TCL) statements
• Whether the stored procedure contains read/write package variables
• Whether the stored procedure has an internal sequence
• Other stored procedures accessed by this stored procedure

Considerations for parallel execution of a DML statement associated with a UDF or trigger

When a DML statement associated with a UDF or trigger is executed in parallel, if the corresponding stored procedure involves a factor that affects parallel execution, parallel execution is disabled and serial execution is adopted. At the same time, the parallel_enable clause is provided to force parallel execution for the UDF. If the parallel_enable clause is specified when you create the UDF, a PDML statement associated with the UDF ignores whether the UDF involves a factor that affects parallel execution and forces parallel execution.

Parallel execution of a UDF or trigger that is triggered by a PDML statement is changed to serial execution in the following common scenarios:

• The UDF or trigger that is triggered by the PDML statement contains a DML statement.

• The UDF or trigger that is triggered by the PDML statement contains a SELECT statement.

• The UDF or trigger that is triggered by the PDML statement has a read/write package variable, or needs to access an external stored procedure or a global object.

  Notice

  If a UDF associated with a parallel query statement contains only SELECT statements, parallel execution is allowed.

The following limitations apply to a UDF or trigger that is triggered by an SQL statement:

• If the outer SQL statement is a query or DML statement, the UDF cannot end the transaction, create or roll back to savepoints, or execute ALTER SYSTEM or ALTER SESSION.
• If the outer SQL statement is a DML statement, the UDF cannot read data from or write data to the table manipulated by this statement.

The preceding limitations apply no matter whether the UDF or trigger is called from PL, directly embedded in the function body, or executed by using the EXECUTE IMMEDIATE statement or the DBMS_SQL package.