-
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!
-
-
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
-
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
-
Hee Hee thanks guys I knew you'd come through for me. :D
-
Oh yes, as TK says you could monitor the rate of redo log generation also.
-
what link did i give ?? i think i need a cofee... any one intrested...
-
-
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).
-
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.