This discussion is archived
1 2 3 5 Previous Next 73 Replies Latest reply: Nov 8, 2007 9:47 PM by damorgan RSS

When to rebuild indexes

605373 Newbie
Currently Being Moderated
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
    438698 Newbie
    Currently Being Moderated
    Check this link out at Tom's web site..


    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112
  • 2. Re: When to rebuild indexes
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    There is really a bunch of threads in this forum, a quick search against the forum side may help you.

    Nicolas.
  • 3. Re: When to rebuild indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    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.
    Aman....
  • 4. Re: When to rebuild indexes
    591186 Guru
    Currently Being Moderated
    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
    311441 Employee ACE
    Currently Being Moderated
    There's an updated (and somewhat larger) version of the presentation here:

    http://www.miracleas.dk/index.asp?page=169&page2=276&page3=179

    Cheers

    Richard
  • 6. Re: When to rebuild indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    thanks sir :).
  • 7. Re: When to rebuild indexes
    matt_glover Guru
    Currently Being Moderated
    Excellent document Richard, very informative. It's like Oracle Mythbusters, I love it!
  • 8. Re: When to rebuild indexes
    311441 Employee ACE
    Currently Being Moderated
    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.

    Cheers

    Richard
  • 9. Re: When to rebuild indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    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
    Aman....
  • 10. Re: When to rebuild indexes
    Maran Viswarayar Pro
    Currently Being Moderated
    You can download from the link posted by Richard foote
  • 11. Re: When to rebuild indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    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 :).
    Aman....
  • 12. Re: When to rebuild indexes
    605373 Newbie
    Currently Being Moderated
    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
    311441 Employee ACE
    Currently Being Moderated
    Hi Aman

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

    Cheers

    Richard Foote
  • 14. Re: When to rebuild indexes
    108476 Journeyer
    Currently Being Moderated
    Hi,

    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:

    http://www.dba-oracle.com/oracle_tips_proof_table_index_rebuilding.htm


    Hope this helps. . .

    Don Burleson
    Oracle Press author
    Author of “Oracle Tuning: The Definitive Reference”
    http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
1 2 3 5 Previous Next