Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index rebuilding

RE: Index rebuilding

From: Cary Millsap <>
Date: Sun, 14 Nov 2004 10:46:21 -0600
Message-ID: <002f01c4ca69$7bc6cee0$6701a8c0@CVMLAP02>

Doesn't it come down to making sure you've defined your terms? A lot of = the
argument seems to be an implicit disagreement over what the word = "balanced"
means. In Knuth and other computer science texts that discuss indexes, I believe the definition of "balanced" is "an index is balanced iff (if = and
only if) all leaf nodes have the same distance to the root." By this definition, Oracle B*-tree indexes are ALWAYS balanced, and NEVER un-balanced. This point is not in contention, correct?

I think what's happening is that people who are complaining about un-balanced-ness are redefining the word "balance" to mean something completely different.

In general, I think it's sloppy to take change the meaning of a = scientific
word in a discussion or "white paper." When I say "scientific word," I = mean
one that has been carefully defined and used in a specific context = for--in
this case--decades. It's one of the things that drives me nuts about the Oracle culture, this bastardization of carefully defined, = well-established
terms for the convenience of some Oracle author who writes more than he reads. :)

I guess the problem is analogous to the one being solved in the XML = world by
the implementation of XML namespaces. Maybe instead of the term = "balanced",
we should use the term "knuth:balanced" or "choose-an-author:balanced". = In
this case, I would suggest that the default namespace should be set to "knuth".

Cary Millsap
Hotsos Enterprises, Ltd.
* Nullius in verba *

Upcoming events:

- Performance Diagnosis 101: 1/4 Calgary, 2/2 Sydney
- SQL Optimization 101: 11/8 Dallas, 12/13 Atlanta, 2/7 Sydney
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit for schedule details...

-----Original Message-----
From: =
On Behalf Of Alex
Sent: Friday, November 12, 2004 5:44 PM
To:;; Subject: RE: Index rebuilding

I agree with Dick! Always and never are to be used in cases like "the sun always rises in the east: or "I've never enjoyed working with Oracle more than I do now" :)


> Looked at Richard Foote's paper. Don't know about
> that. I did prove to
> OTS several years ago that a block could get "lost"
> in an index due to
> deletion/updates that left it empty. I believe that
> got finally fixed
> in Oracle 8i. I've still seen cases of index's
> becoming unbalanced, I
> know the docs day it's impossible, but it does
> happen without the index
> height increasing. And I still believe that index
> deletes don't get
> flushed so efficiently, as Richard suggests. If
> that was the case then
> I can't explain why an index rebuild can cause an
> index to shrink by 30%
> or more. And recent experience still shows that a
> rebuild can cause
> significant performance improvement. And Oracle has
> provided the
> capability to rebuild indexes which is not trivial.=20
> Therefore, NEVER
> use the word "never" unless your absolutely certain
> that under all
> circumstances it will be absolutely true. And in
> the current context,
> that is the truth, that is, never can never be an
> absolute.


> BTW: Since we've a few "myth busters" in the group.=20
> I appreciate the
> effort these people put into "myth busting", even if
> they are later
> proven to have erred. At a very minimum they start
> discussion and
> re-examination of commonly held beliefs that can
> have changed or lost
> significance over the years(like it's best to have
> all of a tables data
> in the first extent). Such discussion, although
> sometimes the start of
> "Holy Wars", is healthy (not the Holy War though)
> and a necessary part
> of all of us growing. That being said, let it be
> noted that I agree to
> disagree, in part, with Mr Foote.

> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> -----Original Message-----
> From: Jared Still []=3D20
> Sent: Friday, November 12, 2004 12:44 PM
> To:
> Cc:;
> Subject: Re: Index rebuilding

> On Fri, 12 Nov 2004 11:49:46 +0100, Karsten Weikop
> <>
> wrote:
> > Please read the execellent paper from Richard
> Foote (which can be
> > downloaded from Miracle's site):
> >

> > Conclusion form this paper: Never Rebuild, but
> find the course to the
> > problem.

> Never?

> I think you will find that statement as difficult to
> support as
> 'always rebuild'.

> --=3D20
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> --
> --



Do you Yahoo!?=20
Check out the new Yahoo! Front Page.=20

Received on Sun Nov 14 2004 - 10:46:58 CST

Original text of this message