Re: index rebuilding...

Richard Foote wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E39A86C.3143C919_at_exesolutions.com>...
> <Just snipping Howard going on again ;)>
> >
> > You have just brought up an interesting question I've never heard addressed.
> > Tables and indexes are all just segments and a segment is a segment is a
> > segment. With a table, if you empty a table with a delete there is a high water
> > mark that when a full table scan takes place forces a read of every block. What
> > happens with an index scan? Does it also read all of the empty index blocks? My
> > guess is that it must because it has no way to know when to stop.
> >
> > So while an index may begin becoming inefficient again after a rebuild, what
> > doesn't, there is presumably a point at which there is a net benefit to a
> > rebuild. And can we identify some guidlines around where that is?
> >
> > Comments appreciated.
> >
> > Daniel Morgan
>
> Hi Daniel,
>
> It depends on what you mean by an "Index Scan".
>
> If you mean a "Full Index Scan" or an "Index Range Scan", then no,
> these empty blocks have no effect as they're not part of the current
> index structure and are therefore not traversed by Oracle.
>
> If however you mean a "Fast Full Index Scan", then yes we have a
> potential issue as Oracle performs a multiblock read and has no choice
> but to read in these empty blocks until it reaches the HWM of the
> index segment. And just like a table, if we have heaps of empty blocks
> below the HWM, such scans are going to be correspondingly inefficient.
>
> Note however that should such a situation arise, the corresponding
> table is likewise going to be inefficient and benefit from a reorg
> anyway (unless a large insert is imminent).
>
> Cheers
>
> Richard

That is what I was assuming. Thanks for the confirmation.

Daniel Morgan Received on Fri Jan 31 2003 - 01:30:17 CST