Re: Excessive Logical and Physical I/O

"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??

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.

Not having a go, Brian!

Regards
HJR Received on Fri Mar 26 2004 - 04:09:40 CST