Determine an index needs to be rebuilt

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

SQL> analyze index <username>.<index_name> validate structure;

Index analyzed.

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.

SQL> select del_lf_rows,lf_rows,height,lf_rows,lf_blks from sys.index_stats;

DEL_LF_ROWS    LF_ROWS    HEIGHT    LF_ROWS LF_BLKS

----------- ---------- ---------- ---------- --------------------------------------------------

  842     41356545           3       41356545 109441

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.

SQL> alter index <username>.<index_name> rebuild online;

Index altered.