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_at_gmail.com> 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_at_gmail.com> wrote:

>
--
http://www.freelists.org/webpage/oracle-l