When and How to rebuild indexes in Oracle
Why to Rebuild index?
•
Every so often, we need to rebuild indexes in
Oracle, because indexes become fragmented over time.
•
This causes their performance that of your
database queries, to degrade.
•
Hence, rebuilding indexes every now and again
can be quite beneficial.
How to determine whether to rebuild or not?
•
There are two rules of thumb to help
determine if the index needs to be rebuilt:
•
If the index has height greater than four,
rebuild the index.
• The deleted leaf rows should be less than 20%.
How to know the index height and deleted leaf rows ?
•
ANALYZE INDEX <index name> VALIDATE STRUCTURE;
•
SELECT name,
height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
• select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_delete from index_stats;
How to Rebuild ?
• Alter index <indexname> rebuild; # (gets rebuilt in the same tablespace)
• Alter index <indexname> rebuild tablespace <another tablespace>;
• Alter index <indexname> rebuild tablespace <another tablespace> online;
• Alter index <indexname> rebuild tablespace <another tablespace online nologging;
Alter index <indexname> rebuild tablespace <another tablespace online nologging;
This statement is the best one to rebuild indexes, because index get rebuild even if the resource is busy and does not generate redo. So the index gets rebuilt very quickly.