Rebuild Index Online

Rebuilding Indexes Online

You can create or rebuild indexes even when doing DML (INSERT/UPDATE/DELETE) statements on the base table. However, rebuilding during low DML activity is still better. Prior to Oracle 11g, this required an exclusive lock at the beginning and end of the rebuild. This lock could cause DML delays and a performance spike. This lock is no longer required for this operation and rebuilding indexes online is now much faster!. Rebuilding is also faster than a DROP and CREATE index.

Here’s the basic syntax:


CREATE INDEX index_name ON table (col1,...) ONLINE;
Index created.

ALTER INDEX index_name REBUILD ONLINE;
Index altered.

Note that rebuilding an index is not the same as coalescing an index. A nice comparison is shown here:

Advertisements

About qainterviews

Software Geek,QA Expert,Blogger
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

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