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."

A few but important restrictions are missing from this list, and in the "Data Warehousing Guide - Restrictions on Parallel DML" we find this more complete list, which also covers a restriction that I assume is not so well known: Deferrable constraints.

"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 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:

SQL>ִ
SQL>ִselectִ*ִfromִv$version;

BANNER
----------------------------------------------------------------
OracleִDatabaseִ10gִEnterpriseִEditionִReleaseִ10.2.0.4.0ִ-ִProd
PL/SQLִReleaseִ10.2.0.4.0ִ-ִProduction
COREִִ10.2.0.4.0ִִProduction
TNSִforִ32-bitִWindows:ִVersionִ10.2.0.4.0ִ-ִProduction
NLSRTLִVersionִ10.2.0.4.0ִ-ִProduction

SQL>ִ
SQL>ִdropִtableִdirect_path_testִcascadeִconstraints;

Tableִdropped.

SQL>ִ
SQL>ִcreateִtableִdirect_path_testִas
ִִ2ִִselect
ִִ3ִִִִִִִִrownumִִִִִִִִid,
ִִ4ִִִִִִִִmod(rownum,300)ִִִִִִִmodded,
ִִ5ִִִִִִִִlpad(rownum,1000)ִִִִִִִpadding
ִִ6ִִfrom
ִִ7ִִִִִִִִall_objects
ִִ8ִִwhere
ִִ9ִִִִִִִִrownumִ<=ִ10000
ִ10ִִ;

Tableִcreated.

SQL>ִ
SQL>ִ--ִdemonstrateִerrorִORA-12839
SQL>ִalterִsessionִenableִparallelִdml;

Sessionִaltered.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִworksִfine
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִthrowsִerrorִORA-12839
SQL>ִinsertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa;
insertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa
ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12839:ִcannotִmodifyִanִobjectִinִparallelִafterִmodifyingִitִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִ--ִdemonstrateִdeferrableִconstraintִeffect
SQL>ִcreateִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id)
ִִ4ִִdeferrableִinitiallyִdeferred;

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִsinceִtheִconstraintִisִdeferred
SQL>ִ--ִbutִitִwon'tִuseִdirectִpathִmodeִalthoughִrequested
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִselectִcount(*)ִfromִdirect_path_test;

ִִCOUNT(*)
----------
ִִִִִ20000

SQL>ִ
SQL>ִ--ִconstraintִisִviolated
SQL>ִsetִconstraintִallִimmediate;
setִconstraintִallִimmediate
*
ERRORִatִlineִ1:
ORA-00001:ִuniqueִconstraintִ(CBO_TEST.DIRECT_PATH_TEST_PK)ִviolatedִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִ--ִnowִweִcreateִaִnon-deferrableִprimaryִkeyִconstraint
SQL>ִ--ִusingִaִnon-uniqueִindex
SQL>ִ--ִwhichִisִalsoִsupported
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִ--ִbutִitִdoesn't
SQL>ִ--ִprobablyִbecauseִtheִprimaryִkeyִis
SQL>ִ--ִenforcedִusingִaִnon-uniqueִindex
SQL>ִ--ִNotִdocumentedִasִfarִasִIִknow
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִ--ִbutִitִworks,ִsoִnoִdirectִpathִinsertִwasִused
SQL>ִselectִcount(*)ִfromִdirect_path_test;

ִִCOUNT(*)
----------
ִִִִִ20000

SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִdropִindexִdirect_path_test_pk;

Indexִdropped.

SQL>ִ
SQL>ִcreateִuniqueִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִ--ִORA-12838
SQL>ִselectִcount(*)ִfromִdirect_path_test;
selectִcount(*)ִfromִdirect_path_test
ִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12838:ִcannotִread/modifyִanִobjectִafterִmodifyingִitִinִparallelִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִspoolִoff

And here is the result from 11.1.0.6:

SQL>ִ
SQL>ִselectִ*ִfromִv$version;

BANNER
--------------------------------------------------------------------------------
OracleִDatabaseִ11gִEnterpriseִEditionִReleaseִ11.1.0.6.0ִ-ִProduction
PL/SQLִReleaseִ11.1.0.6.0ִ-ִProduction
COREִ11.1.0.6.0ִProduction
TNSִforִ32-bitִWindows:ִVersionִ11.1.0.6.0ִ-ִProduction
NLSRTLִVersionִ11.1.0.6.0ִ-ִProduction

SQL>ִ
SQL>ִdropִtableִdirect_path_testִcascadeִconstraints;

Tableִdropped.

SQL>ִ
SQL>ִcreateִtableִdirect_path_testִas
ִִ2ִִselect
ִִ3ִִִִִִִִrownumִִִִִִִִid,
ִִ4ִִִִִִִִmod(rownum,300)ִִִִִִִmodded,
ִִ5ִִִִִִִִlpad(rownum,1000)ִִִִִִִpadding
ִִ6ִִfrom
ִִ7ִִִִִִִִall_objects
ִִ8ִִwhere
ִִ9ִִִִִִִִrownumִ<=ִ10000
ִ10ִִ;

Tableִcreated.

SQL>ִ
SQL>ִ--ִdemonstrateִerrorִORA-12839
SQL>ִalterִsessionִenableִparallelִdml;

Sessionִaltered.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִworksִfine
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִthrowsִerrorִORA-12839
SQL>ִinsertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa;
insertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa
ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12839:ִcannotִmodifyִanִobjectִinִparallelִafterִmodifyingִitִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִ--ִdemonstrateִdeferrableִconstraintִeffect
SQL>ִcreateִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id)
ִִ4ִִdeferrableִinitiallyִdeferred;

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִsinceִtheִconstraintִisִdeferred
SQL>ִ--ִbutִitִwon'tִuseִdirectִpathִmodeִalthoughִrequested
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִselectִcount(*)ִfromִdirect_path_test;

ִִCOUNT(*)
----------
ִִִִִ20000

SQL>ִ
SQL>ִ--ִconstraintִisִviolated
SQL>ִsetִconstraintִallִimmediate;
setִconstraintִallִimmediate
*
ERRORִatִlineִ1:
ORA-00001:ִuniqueִconstraintִ(CBO_TEST.DIRECT_PATH_TEST_PK)ִviolatedִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִ--ִnowִweִcreateִaִnon-deferrableִprimaryִkeyִconstraint
SQL>ִ--ִusingִaִnon-uniqueִindex
SQL>ִ--ִwhichִisִalsoִsupported
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִ--ִandִhereִinִ11gR1ִitִactuallyִworks
SQL>ִ--ִwhereasִinִ10.2.0.4ִitִdidn'tִwork
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִselectִcount(*)ִfromִdirect_path_test;
selectִcount(*)ִfromִdirect_path_test
ִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12838:ִcannotִread/modifyִanִobjectִafterִmodifyingִitִinִparallelִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִdropִindexִdirect_path_test_pk;

Indexִdropped.

SQL>ִ
SQL>ִcreateִuniqueִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִ--ִORA-12838
SQL>ִselectִcount(*)ִfromִdirect_path_test;
selectִcount(*)ִfromִdirect_path_test
ִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12838:ִcannotִread/modifyִanִobjectִafterִmodifyingִitִinִparallelִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִspoolִoff

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.