| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Maximum height of an Oracle B-tree index
Hi Ryan,
These are exactly this misconceptions my paper addresses.
An Oracle B-Tree index *only ever* increases in height when the root block splits. The root block only splits when the index structure beneath it has grown to the point it can no longer address all the blocks at the next level. And this generally happens as a direct result of the quantity of data in the index as mentioned by Mark.
Simplistically, large indexes require more levels and a rebuild very very rarely results in a reduction of a level, making the whole exercise pointless.
See my paper for all the details where I prove all this scientifically.
Cheers
Richard
----- Original Message -----
From: <ryan.gaffuri_at_cox.net>
To: <oracle-l_at_freelists.org>
Sent: Tuesday, March 09, 2004 11:20 PM
Subject: Re: Maximum height of an Oracle B-tree index
> what type of algorithm do you run to increase the height of a b-tree
index? My understanding is that oracle dynamically increases the number of
pointers each block can have(which is different than other b-trees) in order
to keep the height low? am I correct in this assumption?
>
> Why isn't it good to rebuild an index when the height increases? The
formula for calculating I/O of an index is as follows
>
> LOG_height(blocks) = estimated I/O
>
> That is LOG of the height of an index to the base of its total number of
blocks. Now I think there is a fudge factor based on the size of your
blocks, because larger blocks incur more LIOs.
>
> This is not oracle specific. Its general tree theory.
> >
> > From: "Richard Foote" <richard.foote_at_bigpond.com>
> > Date: 2004/03/09 Tue AM 09:18:59 EST
> > To: <oracle-l_at_freelists.org>
> > Subject: Maximum height of an Oracle B-tree index
> >
> > Hi All,
> >
> > I'm currently writing a rather detailed paper for our local user group
on
> > Index Internals, tentatively titled "Index Internals - Rebuilding The
> > Truth". I haven't had this much fun with tree and block dumps for quite
a
> > while ;)
> >
> > One of the many myths I'm exposing is the "rebuild if index has more
than 2,
> > 3, 4, 42, whatever levels". Now to get an honary mention in the paper
(what
> > more reward can one wish for !!), I would love to know who on the list
has
> > created an index with the greatest height and perhaps a little info on
it's
> > circumstance.
> >
> > Steve Adams once mentioned to me creating an index with 20+ levels, can
> > anyone else come close ?
> >
> > Thanks for any replies.
> >
> > Richard
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 09 2004 - 15:20:52 CST
 |
 |