Re: Index management

Mike Ault wrote:

> Howard,
>
>

>>[what "it" are we talking about? From the context, it sounds like it 
>>should be "the number of leaf nodes of the index"] 

You weren't in the original context, but making a post to a newsgroup. You might at least make it comprehensible. But whatever. I take it therefore that the original quote should have read

"If the number of dirty blocks in the table is several times the clustering factor, the index might benefit from a rebuild"

Is that right now?

In which case, would you tell me what a "dirty block in a table" is. Dirty buffers, I am familiar with. Blocks I am familiar with. But dirty blocks seems to me to be a contradiction in terms.

> Advice we should all follow, however, delibrately misleading quotes
> that don't take into account supporting material stray in to dangerous
> territory, especially when they are used to damage the reputations of
> others.

Well, did you or did you not write "Deleted space is not reclaimed automatically unless there is an exact match key inserted."?

Let's just start with that one, shall we? How much "supporting material" is actually needed to make that even approximately resemble the actual truth (which happens to be that deleted space is reclaimed whenever an entry that needs it visits a block that contains it, and the entry doesn't have to match at all). That must be some supporting material!

> If people look at the recommendations in total, then they know I only
> advocate rebuilds when it is statistically indicated

I think that's the point, don't you? Your statistics are questionable.

 >or performance
> points at an index that is showing the signs of aging I have
> discussed. In the situations I discuss rebuilds have reduced
> processing times from 5 hours to 2 hours, reduced index sizes from 80
> gig to 20-30 gig and in another case reduced index tablespace
> utilization by 90%. In testing changing the column orders in a
> concatenated index can reduce cluetering factors by an order of
> magnitude.

See, there you go again. You seem to think that dropping an index and re-creating it with a different column order comes under the rubrick of an index *rebuild*, when it is no such thing, but is actually a bit of index *redesign*. It doesn't help clarify an issue if you keep changing your mind on what the issue we're talking about actually is.

Your claims of 3 hour reduction in run times or 90% reduction in space utilization are utterly meaningless if we can't be certain whether you are talking about 'alter index blah rebuild' or some other bit of DML you happen to have fired off at the time.

 >I could go on, but it is probably casting pearls.

Good of you to hold off on the personal abuse for as long as you managed. I realise you have to make an effort in these things.

HJR Received on Thu May 06 2004 - 12:57:09 CDT