Tuesday, April 16, 2013

Direct Path insert -- Reused Space and Referential Integrity

1) With Insert append, free space is not reused.  Data will be inserted in append mode. (above the HWM) After the commit; HWM is updated to the new value .. -->  requires more space than conventional-path 
2) Refrential integrity constraint can not be maintained..

It seems these 2 things makes direct load inserts generate less redo and undo than conventional path inserts..

Ref: docs.oracle

During direct-path INSERT, you can disable the logging of redo and undo entries. Conventional insert operations, in contrast, must always log such entries, because those operations reuse free space and maintain referential integrity.

In addition, It seems "insert /*+append*/ " can be ignored by oracle , if the underlying table has referential integrity or a trigger.

Ref: Ask Tom.

what it seems to neglect to say in that old documentation is that.... 
insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger...

sqlldr can and does ignore triggers/referential integrity and even uniqueness during a direct path load, insert /*+ append */ does not - you are not using direct path when they (constraints) exist. 

parallel is always a direct path, if you go parallel, you will be appending. 

No comments :

Post a Comment

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category.
I will support you from there.