Indexing is very important concept both for DBA & Developers. If used efficiently, it’s very useful. But some time index doesn’t show actual expected performance. In those cases we need to check whether index need to be rebuild or not. The reasons can be:
- As a result of row deletes, the number of index nodes that have been logically deleted. Oracle leaves “dead” index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to rebalance the index tree when rows are deleted.
- As a result of row inserts, the number of levels that are spawned by the index. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows.
- The amount of logical I/O that is required to fetch a row with the index.
So, for rebuilding two approaches are used:
Alter index rebuild: 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. Since, locking occurs as the existing index is read, this command is designed for scheduled downtime periods where there is no DML activity.
Alter index rebuild online: During the online index rebuild, Oracle will make a snapshot log on the target table to hold DML activity, read the table in a full-table scan (read consistent), build the new index and then apply the changes from the snapshot log after the index has been rebuilt. Because the “alter index rebuild online” performs a full-table scan, it can be perform using parallel query (a parallel hint), and the online rebuild can also be performed in NOLOGGING mode.