Re: Excessive Logical and Physical I/O

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Fri, 26 Mar 2004 12:08:04 +0100
Message-ID: <c412ul$1a8$1@news.BelWue.DE>

Howard J. Rogers wrote:
> "Holger Baer" <holger.baer_at_science-computing.de> wrote in message
> news:c40rqb$kjp$1_at_news.BelWue.DE...
>

>>Brian Peasland wrote:
>>
>>>>>If I need to be corrected, then I need to be corrected.
>>>>
>>>>Oh well, in that case... do something about that paper showing 50%
>>>>improvements in speed because of an index rebuild!!! ;-)
>>>
>>>
>>>I was waiting for someone to take me to task for that paper....I wrote
>>>that paper many moons ago, so the information is dated. And I don't go
>>>back and update my papers every time I learn something new. I had been
>>>meaning to add some more information to that paper, but just havn't
>>>gotten around to it yet. Your comments prompted me into action today. If
>>>you go to my website (http://www.peasland.net), you will see that I did
>>>add an addendum to that white paper.
>>>
>>
>>However, Howard let you get away with this:
>>
>><quote>
>>Contrary to some widely accepted beliefs, indexes in Oracle are not

>
> self-balancing. After a table
>

>>experiences a large number of inserts, updates, or deletes, the index can

>
> become unbalanced and
>

>>fragmented
>></quote>
>>
>>Lucky bastard ;-)
>>
>>Cheers
>>Holger

>
>
>
> ;-o Am I really that bad??

Ahem, er ... no
It's just that some people can't stand being corrected. Especially when they tend to get much things wrong or at least inaccurate. You'd never let them get away with such a phrase. (Hey, I'm not mentioning a single name either) ;-)

> I agree that statement is not well-phrased, but Brian explains why he can't
> do anything about it. What I would want to point out however is:
>
> 1. He has left "the information in this paper for prosperity sake". On the
> grounds that Brian probably isn't make fat wadges of cash from his papers, I
> think that should read "posterity's".
>
> 2. More seriously, he gives the example of an index of a monotonically
> incrementing sequence number that is subject to periodic deletes ("your
> company decides to...remove all of last year's invoices"). He suggests this
> will result in deletes from the left-hand side of the index, populated leaf
> nodes on the right-hand side, and that such an index "should be rebuilt".
> That is of course entirely not the case, since the bulk delete of all of
> last year's invoice will (probably) leave behind a huge swathe of totally
> empty leaf nodes... and totally empty leaf nodes can be re-used by future
> inserts, even for a monotonically incrementing sequence number. The greater
> concern is an index on a table that is subject to periodic *and sparse*
> deletes, none of which quite empty leaf nodes.
>
> The language of "unbalanced" indexes pervades that example, and it's wrong.
> It pervades a lot of people's thoughts of course (no names, no packdrill, no
> Burlesons). Doesn't make it right of course.

Agreed. In the context of the full text (if I understood the paper correctly), unbalanced was not meant to indicate that there was a different height on one side of the index than on the other, but possibly a large number of (in search for a better word) unnecessary leaf blocks. He also explains correctly how a block split gets propagated up the b*tree, so I'm sure he knows better.

>
> Not having a go, Brian!

Nor am I.

>
> Regards
> HJR
>

Cheers

Holger Received on Fri Mar 26 2004 - 05:08:04 CST