Re: DB2 HADB

"Mark Townsend" <markbtownsend_at_comcast.net> wrote in message news:438A44EC.4040600_at_comcast.net...
>
> 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
>

There is no need to cut it short.

I am not arguing that people should frequently reorg indexes in Oracle or DB2. Tom basically says that the index will soon be the way it was before the reorg, and you don't gain much space, so why do it? I would agree that in an OLTP system reorging an index usually has little benefit.

However when you have a lot of index fast full scans (without the b-tree), one can sometimes have improved performance if the indexes reorged (assuming that they need reorging). In other words, it takes less time to read 100,000 entries from a 100 block index than reading the entries from a 500 block index. Also, Multi-block Index range scans will run faster when the data blocks are arranged in index-key order and when the data blocks have a high number of row entries. Admittedly, these events are not as common in a well designed OLTP system compared to a decision support system.

Certainly, reasonable people can disagree about these matters, but it is shameful when people use professional disagreements to launch personal vendettas against other people on this forum, and Daniel Morgan is quite fond of doing. Received on Sun Nov 27 2005 - 18:12:30 CST