SQL statements can contain user-defined functions written in PL/SQL, in Java, or as external procedures in C that can appear as part of the SELECT list, SET clause, or WHERE clause. When the SQL statement is parallelized, these functions are executed on a per-row basis by the parallel execution server process. Any PL/SQL package variables or Java static attributes used by the function are entirely private to each individual parallel execution process and are newly initialized when each row is processed, rather than being copied from the original session. Because of this process, not all functions generate correct results if executed in parallel.
User-written table functions can appear in the statement's FROM list. These functions act like source tables in that they produce row output. Table functions are initialized once during the statement at the start of each parallel execution process. All variables are entirely private to the parallel execution process.
This section contains the following topics:
In a SELECT statement or a subquery in a DML or DDL statement, a user-written function may be executed in parallel in any of the following cases:
If it has been declared with the PARALLEL_ENABLE keyword
If it is declared in a package or type and has a PRAGMA RESTRICT_REFERENCES clause that indicates all of WNDS, RNPS, and WNPS
If it is declared with CREATE FUNCTION and the system can analyze the body of the PL/SQL code and determine that the code neither writes to the database nor reads or modifies package variables
Other parts of a query or subquery can sometimes execute in parallel even if a given function execution must remain serial.
Refer to Oracle Database Development Guide for information about the PRAGMA RESTRICT_REFERENCES clause and Oracle Database SQL Language Reference for information about the CREATE FUNCTION statement.
In a parallel DML or DDL statement, as in a parallel query, a user-written function may be executed in parallel in any of the following cases:
If it has been declared with the PARALLEL_ENABLE keyword
If it is declared in a package or type and has a PRAGMA RESTRICT_REFERENCES clause that indicates all of RNDS, WNDS, RNPS, and WNPS
If it is declared with the CREATE FUNCTION statement and the system can analyze the body of the PL/SQL code and determine that the code neither reads nor writes to the database or reads or modifies package variables
For a parallel DML statement, any function call that cannot be executed in parallel causes the entire DML statement to be executed serially. For an INSERT SELECT or CREATE TABLE AS SELECT statement, function calls in the query portion are parallelized according to the parallel query rules described in this section. The query may be parallelized even if the remainder of the statement must execute serially, or vice versa.