Direct path operations on Reference partitioned Table

Direct path operation is the fastest way to load a huge set of data into an Oracle database, direct path operation

• Will be done implicitly during Parallel DML operations
• Explicitly requested using APPEND hints in sql’s
• By setting the parameter direct=Y in sql* loader 

That being said, each of the following is a benefit in some cases, each of the following could be a disaster in other cases.

If you direct path load,

• The transaction that did the direct path load CANNOT query that segment - but other transactions can, they just cannot see the newly loaded data
• You never use any existing free space, it always writes above the high water mark
• Bypass UNDO on the table - only the table - modifications (datadictionary updates)
• Will maintain indexes - we build mini indexes on the newly loaded data and merge them into the 'real' indexes in bulk. A direct path load of large amounts of data will maintain indexes very efficiently.
• Can bypass redo on the TABLE in archivelog mode, if the database is set up to allow nologging and you have the segment set to nologging
• Direct path loading bypasses the buffer cache, you write directly to the datafiles
• Direct path loading is only appropriate for the first load of a segment or an increment load of lots of data - or an increment load into a table that never has any deletes (so there is no free space to consider)

Transactional systems – probably won’t use it

Warehousing system – tool, we will use a lot.

However – direct path loading is not possible (turned off silently), if the table to load the data has

• Either a row level or statement level trigger in “ENABLED” status
• If foreign key is defined on the table, direct path will go-away.

demo@ORA12C> create table t1(x int primary key, y int);

Table created.

demo@ORA12C> create table t2(x int);

Table created.

demo@ORA12C> insert into t1(x,y) values(1,1);

1 row created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> insert /*+ append */ into t2(x)

2  select 1 from dual;

1 row created.

demo@ORA12C> select * from t2;

select * from t2

              *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

That shows we did a direct path operation, definitely.  You cannot read from a table in the sametransaction that direct path loads it... Now

demo@ORA12C> rollback;

Rollback complete.

demo@ORA12C> alter table t2 add constraint t2_fk

2  foreign key(x)

3  references t1;

Table altered.

demo@ORA12C> insert /*+ append */ into t2(x)

2  select 1 from dual;

1 row created.

demo@ORA12C> select * from t2;

         X

----------

         1

1 row selected.

demo@ORA12C>

That shows the append was ignored, referential integrity makes append "go away".

But in case of reference partitioned Table, it goes like this.

demo@ORA12C> create table t1(x int primary key, y int)

2  partition by hash(y)

3  partitions 4;

Table created.

demo@ORA12C> create table t2(x int not null , constraint t2_fk

  2             foreign key(x) references t1)

3  partition by reference(t2_fk);

Table created.

demo@ORA12C>

demo@ORA12C> insert into t1(x,y) values(1,1);

1 row created.

demo@ORA12C> insert /*+ append */ into t2(x)

2  select 1 from dual;

1 row created.

demo@ORA12C> select * from t2;

select * from t2

              *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

demo@ORA12C>

So with the presence of foreign key constraint, direct path operation is still possible only in reference partitioned Tables.