Database Reference
In-Depth Information
Parallel DML Statements
The following DML statements can be executed in parallel:
DELETE
•
INSERT
with a subquery (
INSERT
statements with the
VALUES
clause can't be parallelized)
•
MERGE
•
UPDATE
•
■
INSERT
statements and
MERGE
statements (for the part inserting data) executed in parallel use direct-path
inserts. Therefore, they're subject not only to the pros and cons of direct-path inserts, but also to their restrictions.
I describe them in the “direct-path Insert” section later in this chapter.
Note
DML statements can't be executed in parallel when:
•
a table has a trigger;
•
a table has either a foreign key constraint referencing itself, a foreign key constraint with delete
cascade, or a deferred constraint;
•
they're executed in a distributed transaction;
•
they reference a remote object;
•
they reference a user-defined function that can't be executed in parallel (in PL/SQL, use the
PARALLEL_ENABLE
clause to mark functions that support parallel processing);
•
an object column is modified; or
•
a clustered or temporary table is modified.
Parallel DML statements are
disabled
by default (be careful, this is the opposite of parallel queries). At the session
level, you can enable and disable them with the following SQL statements:
ALTER SESSION ENABLE PARALLEL DML
ALTER SESSION DISABLE PARALLEL DML
In addition, it's also possible to force the parallel execution to a specific degree of parallelism with the following
SQL statement:
ALTER SESSION FORCE PARALLEL DML PARALLEL 4
In contrast to what happens with parallel queries, hints alone can't enable parallel DML statements. In other
words, parallel processing of DML statements must be absolutely enabled at the session level to take advantage of it.
To check whether parallel DML statements are enabled or disabled at the session level, you can execute a query like
the following (the
pdml_status
column is set to either
ENABLED
,
DISABLED
, or
FORCED
):
SELECT pdml_status
FROM v$session
