In Oracle databases, over time, indexes are fragmented and cause serious performance issues. We should check the indexes regularly and rebuild them if necessary.

Determine an index needs to be rebuilt

It is possible to determine whether rebuild is required for an index.

First, the relevant index should be analyzed. You can do this with the following command.

The analysis process fills the table “sys.index_stats”. This table contains only one row and therefore only one index can be analyzed at a time. Information about the relevant index can be obtained from the sys.index_stats table in the analyzed session.

After the analysis, according to the data in the “sys.index_stats” table, if any of the following conditions occur you can decide whether rebuild the index or not.

• If the percentage of deleted rows exceeds 30% of the total. So if del_lf_rows / lf_rows> 0.3 in the sys.index_stats table.
• If ‘HEIGHT’ is greater than 4.
• If the number of rows in the index(LF_ROWS) is much less than (LF_BLKS). This indicates that too many records have been deleted from the index.

When one of these conditions occurs you can rebuild the index as follows.