| 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
Got to a blevel of 20. There is rumored to be hard limit of 23 levels on
a b*tree index as implemented by Oracle, maybe I will give that a try
later.
05:00:09 rsysdevdb.radisys.com - jkstill_at_dv03 SQL> set echo on 05:00:13 rsysdevdb.radisys.com - jkstill_at_dv03 SQL> @r 05:00:14 rsysdevdb.radisys.com - jkstill_at_dv03 SQL> 05:00:14 rsysdevdb.radisys.com - jkstill_at_dv03 SQL> select count(*) from t;
COUNT(*)
289810
1 row selected.
05:00:38 rsysdevdb.radisys.com - jkstill_at_dv03 SQL>
05:00:38 rsysdevdb.radisys.com - jkstill_at_dv03 SQL> select height
05:00:38 2 , name
05:00:38 3 , blocks
05:00:38 4 , br_blks
05:00:38 5 , br_rows
05:00:38 6 , distinct_keys
05:00:38 7 from index_stats
05:00:38 8 /
HEIGHT NAME BLOCKS BR_BLKS BR_ROWS DISTINCT_KEYS
---------- ------ ---------- ---------- ---------- -------------
20 TIDX 580096 289821 289809 18059
1 row selected.
05:00:38 rsysdevdb.radisys.com - jkstill_at_dv03 SQL>
"Richard Foote" <richard.foote_at_bigpond.com>
Sent by: oracle-l-bounce_at_freelists.org
03/10/2004 02:31 PM
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: Re: Maximum height of an Oracle B-tree index
Hi Jared/Tin and all,
I believe that was precisely how Steve managed it, very small block size, large pctfree, a bit of cleverness and heaps of storage (until it ran out). He mentioned it btw at the Hotsos tuning class in Sydney last year.
So far the best I've heard privately is height of 6.
Can anyone do better ?
Cheers
Richard
----- Original Message -----
From: Jared.Still_at_radisys.com
To: oracle-l_at_freelists.org
Sent: Thursday, March 11, 2004 6:46 AM
Subject: Re: Maximum height of an Oracle B-tree index
My thoughts mirrored Tims.
Though after seeing Jonathan Lewis force a very small index to a blevel of 3 with the clever use of data and storage parms and who knows what else, I decided there was a distinct possibility that I was wrong.
I've refrained from asking Steve how he did this, but if I see him at dinner this evening, I will find out and report back. :)
Jared
Live from HOTSOS
Tim Gorman <tim_at_sagelogix.com>
Sent by: oracle-l-bounce_at_freelists.org
03/10/2004 06:08 AM
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: Re: Maximum height of an Oracle B-tree index
It would take a heckuva lot of space, even with DB_BLOCK_SIZE = 512 and PCTFREE=99 on the index, to get BLEVEL over 10...
Hat's off to Steve on getting it to 20+...
on 3/9/04 7:18 AM, Richard Foote at richard.foote_at_bigpond.com wrote:
> 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
> -----------------------------------------------------------------
-- 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 -----------------------------------------------------------------Received on Thu Mar 11 2004 - 07:49:59 CST
 |
 |