Re: Suggestions for first exam ...

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3f75a8ff$0$9828$afc38c87_at_news.optusnet.com.au...

> Mass deletes mean leaf nodes probably become available
> for fresh inserts (ie, a leaf node with entries for Adam, Bob and Charles
> in it, where Adam and Bob get deleted, is still the 'A-B-C-ish' leaf node,
> and hence Wilma can't find a home there. But delete Charles as well, and
> you have a totally empty leaf node that, irrespective of its physical
> location, is perfectly capable of accepting a new entry for Wilma, Xerxes
> or Zebedee. Truly bulk deletes are not normally a problem requiring a
> rebuild).
>

Hi Howard,

Your description above is of course correct but the point I was trying to make regarding bulk deletes is this. If one performs a bulk delete, whether or not you should perform an index rebuild depends on the "timeframe" in which the equivalent volume of data is to be re-inserted. Because although the index "structure" could very well be quite efficient and compact as you describe, we still have 2 potentially significant problems, both in relation to the HWM.

Firstly, the HWM of the index is "out there" with a significant amount of unused space below the HWM. If this unused space resides with the index structure (meaning those index blocks currently containing data) then the various types of index range scans could be impacted. However if as you suggest much of this used space may not actually be within the index structure, the performance of fast full index scans would still be impacted which may be an issue.

Secondly and perhaps more importantly, the table itself has much unused space below it's HWM as a result of the bulk delete. Therefore full table scans are going to be impacted which again may not be desirable (hopefully, we can avoid the myth that FTS are always bad :)

So what I was suggesting was that if a bulk delete has occurred on a table and the deleted volume of data was not to be re-inserted within an acceptable timeframe, then the table should really be rebuilt. And by rebuilding the table, you must by default rebuild it's associated indexes even though the index structure(s) could still be quite efficient.

Make sense ?

Cheers

Richard Received on Sun Sep 28 2003 - 07:51:06 CDT