In which cases , indexes should be rebuild ?

In Oracle databases, indexes become fragmented over time and cause serious performance problems. Indexes must be checked regularly and, if necessary, rebuilt and organized.

It can be determined whether a rebuild operation is required for an index.

First of all, it is necessary to obtain information about the relevant index. For this, the index needs to be analyzed. You can do this with the following command.

SQL> analyze index ADURUOZ.IX_DENEME validate structure;

Index analyzed.

The analysis process populates the “sys.index_stats” table. This table contains only one row and therefore only one index can be analyzed at a time. In the session where the analysis is performed, information about the relevant index can be retrieved from the sys.index_stats table.

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, based on the data in the “sys.index_stats” table, if any of the following situations occur, it can be said that the relevant index needs to be rebuilt or recreated.

• If the percentage of deleted rows exceeds 30% of the total. That is, if del_lf_rows / lf_rows > 0.3 from the values ​​in the sys.index_stats table.
• If ‘HEIGHT’ value is greater than 4.
• If the number of rows in the index, LF_ROWS, is much less than the value of LF_BLKS, this is an indication 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 ADURUOZ.IX_DENEME rebuild online;

Index altered.