Index is one of interesting objects in database, which always attract DBAs and Developers to fine tune their queries.Some time index doesn't show actual expected performance. In those cases we need to check whether index need to be rebuild or not.i went through many document which shows certain criteria for rebuilding index.
:-After a table experiences a large number of inserts, updates, and deletes, the index can become unbalanced and fragmented and can hinder query performance.
when to Rebuild Indexes:-
first you must get idea of the current stats of the index by using the below command:
ANALYZE INDEX VALIDATE STRUCTURE
Example:-
SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;
SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM
INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW
---------------------- ----------- ---------- ---------- ----------------
DX_GAM_ACCT 2 1 3 6
1 row selected.
after execution of above command following are reason to rebuild the index.
1)If the index has height greater than four, rebuild the index.
2)The deleted leaf rows should be less than 20%.
or
sql> select index_name,blevel from dba_indexes;
INDEX_NAME BLEVEL
------------------------------ ----------
WRH$_SYSMETRIC_HISTORY_INDEX 2
after execution of above command following are the reason to rebuild the index.
1)if the blevel index values more than 3.
If it is determined that the index needs to be rebuilt.then use the below command to rebuild the index.
sql>alter index index_name rebuild online;
or
sql> alter index index_name rebuild;
or
sql>drop index index_name;
(create new index same as the drop index structure).
then check your application is running fine or not. hope it give the better performance than past.
