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: