Rebuilding Oracle 11g Indexes Online

By: Richard Niemiec


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:

 

0128_002

 

 

 

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

 

Rebuild Coalesce
Quickly move index to another tablespace Can’t move index to another tablespace
Requires more disk space Requires much less disk space than rebuild
Creates new index tree and shrinks heights Coalesces leaf blocks that are in the same branch
Change storage/tablespace without dropping Quickly frees index leaf blocks for use
TIP
You can use the REBUILD ONLINE option to allow DML operations on the table or partition during the index rebuild. You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints. 

TIP

Rebuilding indexes online during high levels of DML activity in 11g is much faster than it was in 10g.

 

 

Comments

  1. What’s up,I check your blogs named “Rebuilding Oracle 11g Indexes Online” like every week.Your writing style is awesome, keep up the good work! And you can look our website about love spells.

Leave a Reply