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

During compilation, OceanBase Database collects factors such as whether a stored procedure contains DML statements or read/write package variables. 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.

The database collects the following information about a stored procedure:

• Whether the stored procedure contains read/write SQL statements, including DML, DDL, and 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 has a parallel impact factor, parallel execution is disabled and serial execution is adopted. At the same time, the parallel_enable clause is provided to force parallel execution for a UDF. If the parallel_enable clause is specified when you create a UDF, a PDML statement associated with the UDF ignores whether the UDF has a parallel impact factor 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.