Oracle FAQ Your Portal to the Oracle Knowledge Grid
 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Maximum height of an Oracle B-tree index

# RE: Maximum height of an Oracle B-tree index

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 9 Mar 2004 10:39:52 -0500

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

Because the height of an index may be the direct result of the quantity of data in the index. Thus rebuilding the index may not reduce the height and in this case the rebuild operation would be a waste of time and effort. Better rebuild criteria are called for.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of ryan.gaffuri_at_cox.net Sent: Tuesday, March 09, 2004 8:21 AM
To: oracle-l_at_freelists.org
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,
>

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
-----------------------------------------------------------------
```
Received on Tue Mar 09 2004 - 09:47:36 CST

Original text of this message

 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US