Re: index rebuilding...

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 Received on Thu Jan 30 2003 - 21:21:08 CST