Re: DB2 HADB

From: Mark Townsend <markbtownsend_at_comcast.net>
Date: Sun, 27 Nov 2005 15:44:44 -0800
Message-ID: <438A44EC.4040600@comcast.net>

Mark A wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1133128332.435719_at_yasure...
>

>>Speak on something you supposedly know ... DB2. This is absolutely
>>untrue for Oracle.
>>-- 
>>Daniel A. Morgan

>
>
> Are saying that because Oracle can reorg the indexes automatically, or an
> Oracle index never benefits from a reorg/rebuild? If you are saying the
> later, then you are an incompetent fool.
>
>

Here we go.

Mark, let me cut this short before the hole becomes too deep.

There is a lot of evidence to suggest that rebuilding/reorg indexes in Oracle may have either no benefit, or at most some immediate short term benefit, but no real long term benefit.

Most people don't know why they are rebuilding indexes, they just do it. They also regularly schedule them, for no apparent reason. This is a per example of a DBA just keeping themselves busy.

And in the rare case where an index will benefit from re-org, then typically the data still suffers from the same usage pattern after the reorg anyhow, making the gain transient. So if you don't solve the root problem you are sort of needlessly rearranging deck chairs on the Titanic.

Tom Kyte has a perfect analogy - some people are fat, some aren't. If you are not fat, then you don't need to go on a diet. If you are fat, then a short term diet may get you into the new set of jeans, but long term it is not really going to help - some time later, the diet ends, and you are fat again. I know this from bitter experience, not having seen my feet for a decade.

Here's some useful links you may want to follow - http://tinyurl.com/b5cmj
http://tinyurl.com/bohzv Received on Sun Nov 27 2005 - 17:44:44 CST