Restrictions on Parallel DML

The following restrictions apply to parallel DML (including direct-path INSERT):

• Intra-partition parallelism for UPDATE, MERGE, and DELETE operations require that the COMPATIBLE initialization parameter be set to 9.2 or greater.

• The INSERT VALUES statement is never executed in parallel.

• A transaction can contain multiple parallel DML statements that modify different tables, but after a parallel DML statement modifies a table, no subsequent serial or parallel statement (DML or query) can access the same table again in that transaction.

  • This restriction also exists after a serial direct-path INSERT statement: no subsequent SQL statement (DML or query) can access the modified table during that transaction.

  • Queries that access the same table are allowed before a parallel DML or direct-path INSERT statement, but not after.

  • Any serial or parallel statements attempting to access a table that has been modified by a parallel UPDATE, DELETE, or MERGE, or a direct-path INSERT during the same transaction are rejected with an error message.

• Parallel DML operations cannot be done on tables with triggers.

• Replication functionality is not supported for parallel DML.

• Parallel DML cannot occur in the presence of certain constraints: self-referential integrity, delete cascade, and deferred integrity. In addition, for direct-path INSERT, there is no support for any referential integrity.

• Parallel DML can be done on tables with object columns provided the object columns are not accessed.

• Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

  For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

• A DML operation cannot be executed in parallel if it is in a distributed transaction or if the DML or the query operation is on a remote object.

• Clustered tables are not supported.

• Parallel UPDATE, DELETE, and MERGE operations are not supported for temporary tables.

• Parallel DML is not supported on a table with bitmap indexes if the table is not partitioned.

Violations of these restrictions cause the statement to execute serially without warnings or error messages (except for the restriction on statements accessing the same table in a transaction, which can cause error messages).