Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: When to rebuild the index?

Re: When to rebuild the index?

From: Chirag DBA <>
Date: Tue, 6 Sep 2005 15:37:03 -0400
Message-ID: <>

 U know some staff and managers in IT will ask this question first when they feel a slight down in performance. Some will see the all kind of ratios and then suggest to create statistics and rebuild the indexes.  OEM halps them a lot to see all these things. Pictorial view, wow...  I need to make them feel that " Oh Yeah, That can work out. Let me try....".
 n believe me, with no change or 1 - 2% change they are so happy to go to bed happily.
 That definitely helps. (politically..)

 On 9/6/05, Goulet, Dick <> wrote:
> Jared,
> I agree. Most indexes require little if any attention. Those that go
> through rapid growth are worth attention & 99% of the time something in the
> application is the root cause. We rebuild indexes periodically to placate
> damagement (read that as Damage Management) so that the end user feels the
> DBA staff is doing something to handle their pain. Sometimes a little
> "false" information (politically correct term for a lie) is a good thing,
> sometimes not. The only good thing I've found comes from a rebuild exercise
> is that you get back some space in your index tablespace, somewhere between
> 1 and 5 percent with a definite bias towards the former. In general it's
> normally an exercise in futility, desperation, or both.
> ------------------------------
> *From:* [mailto:
>] *On Behalf Of *Jared Still
> *Sent:* Tuesday, September 06, 2005 2:46 PM
> *To:*
> *Cc:* Allen, Brandon; Oracle-L Freelists
> *Subject:* Re: When to rebuild the index?
> I don't have time to read the entire article, so I skipped down to the
> summary:
> -
> *Academic approach* - Many Oracle experts claim that indexes rarely
> benefit from rebuilding, yet none has ever proffered empirical evidence that
> this is the case, or what logical I/O conditions arise in those "rare" cases
> where indexes benefit from rebuilding.
> -
> *Pragmatic approach* Many IT managers force their Oracle DBAs to
> periodically rebuild indexes because the end-user community reports faster
> response times following the rebuild. The pragmatists are not interested in
> "proving" anything, they are just happy that the end-users are happy. Even
> if index rebuilding were to be proven as a useless activity, the Placebo
> Effect on the end-users is enough to justify the task.
> Re the Academic approach: much evidence has been supplied regarding this.
> I am not going to attempt to rehash it. Look on AskTom. Search for Richard
> Foote's presenation on index internals.
> Re the pragmatic approach: If the users just need a placebo, then lie to
> them.
> Tell them the indexes were rebuilt and they will be happy. Speed increases
> do to index rebuilding in an OLTP system are only temporary. It is
> entirely
> possible that rebuilding index will hurt performance as the index blocks
> re-split due to inserts.
> Oops, there I go, rehashing.
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> 11+ years of trying to appear to know what I'm doing.
> On 9/6/05, Chirag DBA <> wrote:
> >
> > Hi,
> >
> > Here is the very good document for indexes.
> > I just found that.
> > Regards - Chirag
> >
> > On 9/6/05, Chirag DBA < > wrote:
> >


Received on Tue Sep 06 2005 - 14:40:21 CDT

Original text of this message