Re: When to rebuild the index?

Agreed...!!!
 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..)
  - Chirag 

 On 9/6/05, Goulet, Dick <DGoulet@xxxxxxxx> 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:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jared Still
> *Sent:* Tuesday, September 06, 2005 2:46 PM
> *To:* chiragdba@xxxxxxxxx
> *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 <chiragdba@xxxxxxxxx> wrote: 
> > 
> > Hi,
> >  http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm
> >  Here is the very good document for indexes.
> >  I just found that. 
> >   Regards - Chirag
> > 
> >  On 9/6/05, Chirag DBA <chiragdba@xxxxxxxxx > wrote:
> > 
> 
>

Other related posts: