Direct path inserts, nologging option and index cost

This is something confuses lots of us, a direct-path insert will only produce minimum redo(not no redo) if you are using a database in NOARCHIVELOG mode or you are doing this operation on a table which is marked as NOLOGGING.

Some other important points are;
– “with all insert statements append hint will produce minimum redo”, this is not true and actually only with bulk inserts(insert into select from) this feature works.
– only one session can at a time can direct path insert into a table.
– with append you can NOT use the free space below your high water mark.
– during direct load if there are indexes on the table, if you set the indexes as UNUSABLE and rebuild them after the load, redo produced minimizes dramatically.

Folowing is a demostration of these behaviours with autotrace feature;

Code listing 62 : Direct inserts with append hint and Nologging option

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
Effective Oracle by Design by Thomas Kyte Chapter 2-Your performance toolkit, Page 116