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

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


Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s