DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: index rebuilds

  1. #1
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995

    Question index rebuilds

    Guys,
    At some point on here there was a discussion about index rebuilds good vs bad. If I remember correctly Slimdave at one point said that normally indexes shouldn't be rebuilt for anything other than moves and such. I've also read on Toms site that some indexes perform better "fat". which I totally agree from experience

    I've come in contact with someone who seems to think that indexes need to be rebuilt on a monthy basis no matter what and I'm trying to gather up documentation to prove to this person that this is not a good idea. My experience and findings mean nothing to this "know it all" and I want to show this person that Oracle has evolved and the DBA community out there has proven that this is not a good idea.

    I personally use Richard Niemiec's advice using binary hieght as a means to gauge "fragmentation"*. If the b-level goes over 4 I rebuild em.

    * The amount of I/O that needs to be peformed to return the rowid to the user process.

    let the gates open for discussion!
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    What more proof could he want other than Ask tom.com?
    Even "know it all's" need a good beatin' from time to time.
    Slap that beotch up yo!!

    MH
    I remember when this place was cool.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It would be good to be able to prove that the rebuilds harm performance while the indexes re-grow to their natural size, and maybe you could do that through monitoring some wait statistics ... index block split is the main culprit i think ... http://download-west.oracle.com/docs...pa5.htm#972184

    You could also just point him to TK's thread on the subject ... http://asktom.oracle.com/pls/ask/f?p...:6601312252730
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Hee Hee thanks guys I knew you'd come through for me.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Oh yes, as TK says you could monitor the rate of redo log generation also.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    what link did i give ?? i think i need a cofee... any one intrested...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    aah i was looking for this thread
    http://www.dbasupport.com/forums/sho...=index+rebuild
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  9. #9
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Take a look at this comp.database.oracle.server thread. The link to a presentation posted by Richard Foote that he wrote is well worth a read (as is the thread as a whole).

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    OracleDoc,

    If my memory is good, then i think you had in your systems to rebuild the indexes every week?...

    You also said that the practice of it was a ** "history" **..

    Well in this thread Index Rebuild are some points when index rebuild may be benificial..

    One more case when index rebuild can help is

    * Index with first column populated by sequence and the application is such that data is deleted for old generated sequence numbers ..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width