Exchange partition and the SIZE AUTO option of DBMS_STATS column statistics in 10g

Oracle's exchange partition feature is a handy method for loading mass data. Data is usually being loaded into an "exchange" table that mirrors the attributes of a single partition or subpartition of the actual table.

Before 10g it probably didn't matter if you gathered statistics on the exchange table before performing the exchange or afterwards on the actual table, because the "SIZE AUTO" option of the method_opt parameter that was already in 9i was rarely used.

But starting with Oracle 10g the "SIZE AUTO" option is used as default when gathering table resp. column statistics. "SIZE AUTO" works in a two-level approach: In the first step it uses "column usage monitoring", another feature introduced in Oracle 9i to gather the information which columns potentially could benefit from histograms as they are used in WHERE clauses with certain kind of predicates, e.g. equal comparisons. In the next step a histogram is generated to detect if the column values are skewed or have significant gaps (which raised an interesting question about what are "gaps" in VARCHAR based data), and if they do, the histogram is stored in the statistics (this part corresponds to the "SIZE SKEWONLY" option behaviour).

So if you want to take advantage of new default "SIZE AUTO" feature, it is crucial that the statistics are gathered on the actual table because otherwise the "column usage monitoring" won't work as expected. It needs the workload of the actual table, not the one of the exchange table.

As already mentioned, from 10g on the SIZE AUTO option is the default option if you haven't changed it using DBMS_STATS.SET_PARAM resp. DBMS_STATS.SET_*_PREFS in 11g.

This small script shall demonstrate the issue:

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 size_auto_test purge;

Table dropped.

SQL>
SQL> create table size_auto_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_1 values less than (2)
10 (
11 subpartition pkey_1_001 values ('001'),
12 subpartition pkey_1_101 values ('101')
13 )
14 );

Table created.

SQL>
SQL> -- "data1" is skewed
SQL> insert into size_auto_test (x_pkey, x_slice, data1)
2 select 1, '001', trunc(sqrt(seq-1)) as data1
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> -- "data1" is skewed
SQL> insert into size_auto_test (x_pkey, x_slice, data1)
2 select 1, '101', trunc(sqrt(seq-1)) as data1
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- Gather initial statistics
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'SIZE_AUTO_TEST',
4 granularity=>'ALL',
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Check the column usage monitoring
SQL> select * from sys.col_usage$ where obj# in (
2 select object_id from dba_objects
3 where object_name = 'SIZE_AUTO_TEST'
4 --and subobject_name = 'PKEY_1_101'
5 );

no rows selected

SQL>
SQL> drop table exchange_test purge;

Table dropped.

SQL>
SQL> create table exchange_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 );

Table created.

SQL>
SQL> -- mirror one subpartition in exchange table
SQL> insert into exchange_test
2 select * from size_auto_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'EXCHANGE_TEST',
4 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- So this is our current situation
SQL> select table_name, column_name, num_buckets, histogram
2 from user_tab_col_statistics
3 where table_name = 'EXCHANGE_TEST';

TABLE_NAME COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- ----------- ---------------
EXCHANGE_TEST X_PKEY 1 NONE
EXCHANGE_TEST X_SLICE 1 NONE
EXCHANGE_TEST DATA1 1 NONE

SQL>
SQL> select table_name, subpartition_name, column_name, num_buckets, histogram
2 from user_subpart_col_statistics
3 where table_name = 'SIZE_AUTO_TEST';

TABLE_NAME SUBPARTITION_NA COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- --------------- ----------- ---------------
SIZE_AUTO_TEST PKEY_1_001 DATA1 1 NONE
SIZE_AUTO_TEST PKEY_1_001 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_001 X_PKEY 1 NONE
SIZE_AUTO_TEST PKEY_1_101 DATA1 1 NONE
SIZE_AUTO_TEST PKEY_1_101 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_101 X_PKEY 1 NONE

6 rows selected.

SQL>
SQL> -- Now perform an exchange
SQL> alter table size_auto_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> select subpartition_name, column_name, num_buckets, histogram
2 from user_subpart_col_statistics
3 where table_name = 'SIZE_AUTO_TEST';

SUBPARTITION_NA COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- ----------- ---------------
PKEY_1_001 DATA1 1 NONE
PKEY_1_001 X_SLICE 1 NONE
PKEY_1_001 X_PKEY 1 NONE
PKEY_1_101 DATA1 1 NONE
PKEY_1_101 X_SLICE 1 NONE
PKEY_1_101 X_PKEY 1 NONE

6 rows selected.

SQL>
SQL> -- Undo the exchange
SQL> alter table size_auto_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Perform some queries that could benefit from histograms
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 0;

COUNT(*)
----------
1

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 11;

COUNT(*)
----------
23

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 13;

COUNT(*)
----------
27

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 20;

COUNT(*)
----------
41

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 30;

COUNT(*)
----------
61

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 31;

COUNT(*)
----------
39

SQL>
SQL> -- flush the monitoring info, so we can see it in col_usage$
SQL> -- otherwise it may take up to 15-20 minutes to see the latest changes
SQL> -- DBMS_STATS.GATHER_*_STATS will flush this as well
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

PL/SQL procedure successfully completed.

SQL>
SQL> -- Check the column usage monitoring
SQL> select * from sys.col_usage$ where obj# in (
2 select object_id from dba_objects
3 where object_name = 'EXCHANGE_TEST'
4 );

no rows selected

SQL>
SQL> -- Check the column usage monitoring
SQL> select * from sys.col_usage$ where obj# in (
2 select object_id from dba_objects
3 where object_name = 'SIZE_AUTO_TEST'
4 --and subobject_name = 'PKEY_1_101'
5 );

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAM
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- --------
59457 3 1 0 0 0 0 0 04.08.08

SQL>
SQL> -- Obviously the exchange table does not have
SQL> -- any usage recorded
SQL> -- So gathering stats again does not make any difference
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'EXCHANGE_TEST',
4 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Still the same
SQL> select table_name, column_name, num_buckets, histogram
2 from user_tab_col_statistics
3 where table_name = 'EXCHANGE_TEST';

TABLE_NAME COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- ----------- ---------------
EXCHANGE_TEST X_PKEY 1 NONE
EXCHANGE_TEST X_SLICE 1 NONE
EXCHANGE_TEST DATA1 1 NONE

SQL>
SQL> -- So if you exchange now you won't benefit
SQL> -- from any histograms potentially being generated
SQL> -- due to column usage and skewness
SQL> alter table size_auto_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- But now gathering on the actual table
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'SIZE_AUTO_TEST',
4 granularity=>'ALL',
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Histograms being generated on the skewed column
SQL> select table_name, subpartition_name, column_name, num_buckets, histogram
2 from user_subpart_col_statistics
3 where table_name = 'SIZE_AUTO_TEST';

TABLE_NAME SUBPARTITION_NA COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- --------------- ----------- ---------------
SIZE_AUTO_TEST PKEY_1_001 DATA1 4 FREQUENCY
SIZE_AUTO_TEST PKEY_1_001 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_001 X_PKEY 1 NONE
SIZE_AUTO_TEST PKEY_1_101 DATA1 32 FREQUENCY
SIZE_AUTO_TEST PKEY_1_101 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_101 X_PKEY 1 NONE

6 rows selected.

SQL>
SQL> spool off

Note that the test case shows the same result on 11.1.0.6.

So it can be seen from this sample that gathering statistics on the exchange table uses the workload of the exchange table and therefore the SIZE AUTO option doesn't work as expected. Gathering statistics on the actual partitioned table makes use the column workload information and generates histograms based on both workload and skewness.

Another interesting observation is that the column usage in sys.col_usage$ is maintained on table level rather on partition or subpartition level. Although I've explicitly queried on subpartition level the monitoring info refers to the table object and not to the (sub-)partition subobjects. This means that if your data in the partitions is used differently by queries depending on the way you gather statistics you might end up with histograms on particular partitions that are unnecessary. This can have an impact on both the time it takes to gather the statistics as for each histogram a separate gather step resp. query is required and the execution plans and cardinality estimates can be influenced by the existing histograms.

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.