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:
Note that rebuilding an index is not the same as coalescing an index. A nice comparison is shown here:
|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|
Rebuilding indexes online during high levels of DML activity in 11g is much faster than it was in 10g.