This content has been marked as final. Show 73 replies
If you read the excellent presentation of Richard Foote, you will come to know that rebuilding indexes is not usually required.Yes the option is somewhat useful when you see lots of deleted enteries in the leaf blocks as compared to the actual enteries inthe leafs.
you can use this query
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
if the ratio of deleted to actual leaf enteries is more than 20% than you can think about rebuildingthem.
Sure there will be more input form others .Lets wait for it.
I originally wrote the presentation years ago, however based on the number of questions here and elsewhere on the subject of index rebuilds, it's just as relevant now as when it was originally conceived.
The presentation is actually just one section in a 2 day (soon to be 3 day)seminar I have on all things Oracle Indexes which includes discussions on all the other index types and options available in Oracle (Bitmap, Context, Linguistic, Partitioned, IOT, etc etc) , how the CBO processes and costs indexes and index paths, index related tricks and traps, etc etc.
However, the topic of index rebuilds seems to generate the most interest.
Rebuilding an indexes is the subhject of great debate, but it's a myth that Oracle indexes never benefit from a rebuild.
- Index fast full scans may run faster after index reorganization whenever the “density” of the index entries becomes greater. In other words, it takes less time to read 100,000 entries from a 100 block index than reading the entries from a 500 block index.
- Multi-block Index range scans will run faster when the data blocks are arranged in index-key order and when the data blocks have a high number of row entries (as evidenced by clustering_factor in dba_indexes).
- Large-table full-table scans will run faster after reorganization when the table has excessive chained or relocated rows, or low block density after massive DML (updates and deletes).
- Table updates will run faster after reorganizations when the table has unbalanced freelists (with multi-freelist, freelist groups table in the obsolete dictionary-managed tablespaces only).
In an OracleWorld 2003 presentation titled “Oracle Database 10g: The Self-Managing Database” by Sushil Kumar of Oracle Corporation, Kumar states that the new Automatic Maintenance Tasks (AMT) Oracle10g feature will "automatically detect and re-build sub-optimal indexes.“
In a paper titled “Metric Baselines: detecting and explaining performance events in em 10gr2” (Presented at the RMOUG 2005 Training Days), John Beresniewicz of Oracle Corporation notes that the use of "baselines to capture and adapt thresholds to expected time-dependent workload variations" is a core feature of the next release of Oracle.
I have more notes here:
Hope this helps. . .
Oracle Press author
Author of “Oracle Tuning: The Definitive Reference”