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