Rebuilding indexes

Identify index fragmentation :

To obtain information about an index:

analyze index index_name validate structure;


This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.


An index should be considered for rebuilding under any of the following conditions:

* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.

* If the ‘HEIGHT’ is greater than 4.

* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.




Index should be Rebuild After Finding the Index Fragmentation :


Periodically, and typically after large deletes or inserts, it is worth rebuilding indexes. The SQL for this is:

Alter index index_name rebuild;

Alternatively, the following performs the same, but avoids writing to the redo logs and thus speeds up the index rebuild:

Alter index index_name rebuild unrecoverable;

Note: If performing this under Oracle 7.3 then be sure to specify the destination tablespace, ie:

Alter index index_name rebuild tablespace tablespace_name;

Otherwise the index will be moved to the temporary tablespace.