Richard Foote wrote:
> "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 ?
Sort of, though I smell a case of having it both ways! If the table is
subject to FTS, then an index suffering from HWM-blow-out syndrome isn't
going to be too much of a worry, since the liklihood of you doing fast full
index scans seems small. And if you do a lot of fast full index scans,
HWM-blow-out on the table shouldn't be an issue.
Yes, I know you can envisage situations where both happen. So I'll buy the
argument.
Sort of. ;-)
HJR
>
> Cheers
>
> Richard
Received on Sun Sep 28 2003 - 15:23:30 CDT
