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.
|
1 2 3 |
SQL> analyze index ADURUOZ.IX_DENEME 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.
|
1 2 3 4 5 |
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.
|
1 2 3 |
SQL> alter index ADURUOZ.IX_TEST rebuild online; Index altered. |
