1 2 3 Previous Next 73 Replies Latest reply on Nov 9, 2007 5:47 AM by damorgan

    When to rebuild indexes

      Hi DBA Gurus,

      Can somebody tell me when to rebuild indexes.Plz tell me how to find out this or if some query is there please post it.

      Thanks !!!

      Vivek Agarwal
        • 1. Re: When to rebuild indexes
          Check this link out at Tom's web site..

          • 2. Re: When to rebuild indexes
            There is really a bunch of threads in this forum, a quick search against the forum side may help you.

            • 3. Re: When to rebuild indexes
              hi there,
              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
              FROM INDEX_STATS;
              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.
              • 4. Re: When to rebuild indexes
                Take a look at the following forum with nice explanations:

                Re: When I should rebuild the index

                which index rebuild?
                • 5. Re: When to rebuild indexes
                  Richard Foote
                  There's an updated (and somewhat larger) version of the presentation here:



                  • 6. Re: When to rebuild indexes
                    thanks sir :).
                    • 7. Re: When to rebuild indexes
                      Excellent document Richard, very informative. It's like Oracle Mythbusters, I love it!
                      • 8. Re: When to rebuild indexes
                        Richard Foote
                        Thanks Matt

                        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.


                        • 9. Re: When to rebuild indexes
                          Hi sir,
                          Really very nice!I have couple of doubts about the presentation.Where can I ask them here or should I send you a mail?
                          Its one of those presentations which can be called "the best" ones.Really nice!

                          Thanks and best rgards
                          • 10. Re: When to rebuild indexes
                            Maran Viswarayar
                            You can download from the link posted by Richard foote
                            • 11. Re: When to rebuild indexes
                              Hi Maran,
                              I have both the old and new presentations.What I was asking that in that presentation only(content) I have some questions.Where can I ask Richard them?Over here or sending him a direct mail?I guess I shall copyright for being cofusing :).
                              • 12. Re: When to rebuild indexes
                                Thanks everybody...I am new to forum and really i got so many response ...

                                Aman can u please give me ur id ...so that i can contact you in case of any help required....

                                Thanks Again

                                Vivek Agarwal
                                • 13. Re: When to rebuild indexes
                                  Richard Foote
                                  Hi Aman

                                  Ask away. I'm sure others can answer if I'm not around.


                                  Richard Foote
                                  • 14. Re: When to rebuild indexes

                                    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. . .

                                    Don Burleson
                                    Oracle Press author
                                    Author of “Oracle Tuning: The Definitive Reference”
                                    1 2 3 Previous Next