This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Aug 23, 2007 1:36 PM by John Spencer RSS

When I should rebuild the index

589266 Newbie
Currently Being Moderated
I have around 800 indexes every 15 days we rebuild them, how do i know the particular indexes to rebuild daily as required.
  • 1. Re: When I should rebuild the index
    561093 Oracle ACE
    Currently Being Moderated
    Hi,

    Tom would be the best person to answer this question. Read

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730
    and
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112

    Regards
  • 2. Re: When I should rebuild the index
    523455 Newbie
    Currently Being Moderated
    You can take indexes for rebuild consideration having BLEVEL>3.
    desc dba_indexes and built your select query

    Cheers
  • 3. Re: When I should rebuild the index
    299178 Newbie
    Currently Being Moderated
    Not quite correct. Please see the links mentioned by Citrus above.

    There is no need to rebuild index especially every 15 days just to get performance benefit. Because you wont get any.
  • 4. Re: When I should rebuild the index
    523455 Newbie
    Currently Being Moderated
    It depends.

    If performance suffers then you have to put your experience rather than DOC's
  • 5. Re: When I should rebuild the index
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    I have around 800 indexes every 15 days we rebuild
    them, how do i know the particular indexes to rebuild
    daily as required.
    Regular index rebuild are very important. They ensure DBAs have something to do.

    However, in general rebuilding indexes regularily to gain performance is the wrong thing to do. It is better to read Tom Kyte's books (see http://oracle.apress.com) or links provided by others to understand why index rebuilds are usually wasted effort. Much better would be to study WHY the performance drops after while.
  • 6. Re: When I should rebuild the index
    293720 Newbie
    Currently Being Moderated
    Go to google, do a search for "rebuilding the truth richard foote".

    -Mark
  • 7. Re: When I should rebuild the index
    299178 Newbie
    Currently Being Moderated
    Exactly. Did anyone gain performance by rebuilding indexes daily? Contrary the performance goes down. This issue has been discussed million times before. Did you go through links from Asktom Citrus gave you?
  • 8. Re: When I should rebuild the index
    523455 Newbie
    Currently Being Moderated
    I am giving suggestion to rebuild daily.

    But if your database need to rebuild the index , then you wait for the time limit .

    Yeh you are true one should read daily but also one should know the nature of database on which he works.

    With heavy DML's you have to rebuild indexes
  • 9. Re: When I should rebuild the index
    528670 Newbie
    Currently Being Moderated
    Hi Rajesh,

    First is my favorite article by Jonathan Lewis:
    How High Can You Go?
    Second is interesting article I just found:
    How to predict Index blevel and when to rebuild.

    How much time do you think rebuild will take for index with blevel >3 supposing we have usual 50-50 index split?

    And you are suggesting to rebuild daily such indexes?

    I'm wondering is this just a suggestion or your practical solution?

    Best Regards,
    Alex
  • 10. Re: When I should rebuild the index
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    There is a bunch of articles which explain that rebuild index has a high cost for a performance result not always expected. Two of them here below, to demystify this RoT :
    http://www.dbazine.com/oracle/or-articles/jlewis14
    http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf
    I am giving suggestion to rebuild daily.
    Why ? Have you some clues about that ?
    With heavy DML's you have to rebuild indexes
    I worked on many huge databases with a high rate of insert/delete without have to rebuild indexes to improve notably performance. There are others ways.
    If performance suffers then you have to put your experience rather than DOC's
    What's the experience if you fall in some Rule of Thumb, which doesn't help at all to solve perf issue.

    Nicolas.
  • 11. Re: When I should rebuild the index
    DTP Explorer
    Currently Being Moderated
    Given your logic... rebuilding index daily is good, so rebuilding indexes hourly MUST be better... I think the database manages indexes just fine. There are cases when you should rebuild indexes, and a capable DBA should be able to identify them.

    For a person to to give the advice to "rebuild your indexes daily" is just ludicrous. Every table and index in a database is unique and has its own characteristics. Why would you rebuild indexes on a table that has static data and hasn't been inserted or updated for months?
  • 12. Re: When I should rebuild the index
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    I am giving suggestion to rebuild daily.
    I know many consultants who like this attitude. Make the DBA waste all his time so consultants MUST be hired to get anything real accomplished. <g>
    With heavy DML's you have to rebuild indexes
    Absolutely correct, once in a while - depending on the pattern of DML. For many, many kinds of DML and for most applications, rebuilding indexes is simply a way to prove to the users that "system's unavailable - we are working on it"
  • 13. Re: When I should rebuild the index
    522552 Newbie
    Currently Being Moderated
    Hi,

    In my experience, after rebuilding the indexes the database always slows down, and after reading Tom's articles, it seems he also doesn't like to rebuild index.

    Thanks
  • 14. Re: When I should rebuild the index
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Absolutely correct, once in a while - depending on the pattern of DML.
    Absolutely correct, depending of the DML, for example and since SELECT is DML, a database with only SELECT shouldn't have the need to rebuild indexes at all...

    Nicolas.
1 2 Previous Next