Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 20, 2007

Direct path inserts, nologging option and index cost

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 12:46 pm

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

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: