Re: Index management

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 09 May 2004 12:45:22 +1000
Message-ID: <409d9b3b$0$441$afc38c87@news.optusnet.com.au>

Roger S Gay wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:409d7a18$0$442$afc38c87_at_news.optusnet.com.au...
>
>

>>>Mike Ault wrote:

>
>

>>Well, it appears we are never going to get an explanation of what a
>>dirty base table block is (I've only asked three times, and Daniel's
>>asked too).
>>
>>But whatever this mysterious beastie happens to be, what possible
>>difference can it make to such a ratio to rebuild an index??
>>

>
> Please, please Mike, add me to the list of requestors for this explanation.
> Hopefully a clear definition of "dirty base table block" will add more light
> than heat to this discussion.
>

>>>By definition the order of rows in a relational table is random.
>>
>>Also absolutely true.
>>

>
> Howard, are you sure of this? Relational theorists please correct me if I am
> wrong, but I thought the best we could say about the order of rows is that
> it is indeterminate, which means we can't even make statistical arguments
> based on assumed randomness.

Well, OK.... what do we mean by "random" and what do we mean by "indeterminate"?

Now you maths wonks will tell me that the two are not the same. But for us poor schmucks trying to find a pair of socks to wear of a morning, the fact that the ordering in my clothes closet is not random but merely indeterminate is of no practical consequence. It still takes me excessive minutes to find what I am looking for.

I doubt that Mike was arguing for "randomness" in its mathematically-rigorous incarnation. I certainly wouldn't. But a mess of clothes on the bedroom floor certainly *looks* random enough, and that is all that was being said here, I think.

> My only experience with this was with a third party vendor who slipped a
> weekly "rebuild every index in the system" job into a production system I
> was babysitting without saying a word to anyone (Grrrr!) So I can add a
> note here that index rebuilds of any real size are expensive in both CPU and
> log archive space as well as being almost always ineffective and pointless.

That is a very important point, of course, and one that hasn't been done justice so far, because we are all still trying to work out what on Earth Mike is talking about. But when we do, and when we finally get over the clustering factor as an issue, we will still have to do hand-to-hand combat with the fact that index rebuilds are bloody expensive, so you'd better be pretty certain before you start that the benefits are going to outweigh the costs.

> Thank you folks for an informative and so far entertaining thread. I, for
> one, promise faithfully never ever to rebuild an index (not that I ever
> would or did, mark you) without revisiting this thread and rereading every
> post in it.
>
> Roger S Gay

I think that is a victory of sorts, then, Roger!

Thanks
HJR Received on Sat May 08 2004 - 21:45:22 CDT