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:
| 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.
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.