Rebuilding an Existing Index

Before rebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes.

When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

The following statement rebuilds the existing index emp_name:

SQL> ALTER INDEX emp_name REBUILD;

The REBUILD clause must immediately follow the index name, and precede any other options. It cannot be used with the DEALLOCATE UNUSED clause.

If have the option of rebuilding the index online. The following statement rebuilds the emp_name index online:

SQL> ALTER INDEX emp_name REBUILD ONLINE;

If you do not have the space required to rebuild an index, you can choose instead to coalesce the index. Coalescing an index can also be done online.