Posted by : Unknown
This Post demonstrates to determine if an index needs to be rebuilt.
We can get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.
The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer.
The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only.
SQL> ANALYZE INDEX INDEX1 VALIDATE STRUCTURE;
Statement processed.
SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW
———————- ———– ————– ————- ——————-
INDEX1 2 1 3 6
1 row selected.
There are two rules of thumb to help determine if the index needs to be rebuilt.
1) If the index has height greater than four, rebuild the index.
2) The deleted leaf rows should be less than 20%.
If it is determined that the index needs to be rebuilt, this can easily be done by the following command
ALTER INDEX <INDEX_NAME> REBUILD | REBULID ONLINE command.
It is not recommended to execute this command during normal operating hours.
Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.
In this example, the HEIGHT column is clearly showing the value 2. This is not a good candidate for rebuilding.
