Tuesday, July 1, 2008

Deferrable constraints and direct path inserts resp. parallel DML

If you check the official Oracle documentation regarding the restrictions of direct path inserts you'll find the following information (taken from the "SQL Language Reference - INSERT" 11gR1 manuals):

"Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.

Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.

If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.

The target table cannot be index organized or part of a cluster.

The target table cannot contain object type columns.

The target table cannot have any triggers or referential integrity constraints defined on it.

The target table cannot be replicated.

A transaction containing a direct-path INSERT statement cannot be or become distributed."

"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 INSERTVALUES statement is never parallelized.

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 already 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 you are not touching the object columns.

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

A transaction involved in a parallel DML operation cannot be or become a distributed transaction.

Clustered tables are not supported.

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

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). For example, an update is serialized if it is on a nonpartitioned table."

I think there is still at least one restriction missing from the list, which will throw this error message:

ORA-12839: cannot modify an object in parallel after modifying it

Cause: Within the same transaction, an attempt was made to perform parallel modification operations on a table after it had been modified. This is not permitted.

Action: Rewrite the transaction or break it up into two transactions: one containing the parallel modification and the second containing the initial modification operation.

This means that you are not allowed to perform parallel DML on an object that has already been modified within the same transaction (by serial DML). Probably this can be explained by the fact that the parallel slave sessions are not able to see the modifications already applied to the object in the main session.

Interestingly this restriction does not apply to serial direct path inserts, these can be performed after an object has been modified by serial non-direct path DML.

Thinking about the fact that an object that has been modified by direct-path or parallel DML can not be read/modified afterwards within the same transaction it is quite obvious that a deferrable constraint can not be enforced in this mode, because I assume that in order to check the constraint the object has to be re-read and that is not possible before a commit has taken place.

So if there are any deferrable constraints enabled on the object to be modified no direct path or parallel dml operation will be performed, they will silently fall back to serial conventional dml.

So this small script shall demonstrate the latter two points. First the results from 10.2.0.4:

It's quite interesting to note that in 10.2.0.4 the direct path insert didn't work when the primary key was enforced using a non-unique index, although the constraint was not deferrable, whereas in 11.1.0.6 the direct path insert mode was used in this case, which suggests that the 10.2.0.4 behaviour wasn't actually a feature.

3 comments:

Yesterday some new fact came to my knowledge about deferrable constraints . They are checked at the same time as parent statement executing. Only an exception raised on commit or set constraints immediate, if need.

interesting point and I can confirm this using the simple test case below. If you check the autotrace statistics or the trace file generated it can be seen that the delete statement triggers a recursive sql that checks the foreign key constraint although it is deferred. The statistics of the delete statement include the full tablescan (done twice in this case) performed on the child table.

In case the foreign key is dropped the delete statement accounts only the I/O required for the actual delete, not the recursive sql.