Re: When to rebuild the index?
- From: Jared Still <jkstill@xxxxxxxxx>
- To: chiragdba@xxxxxxxxx
- Date: Tue, 6 Sep 2005 11:46:05 -0700
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:
