RE: Maximum height of an Oracle B-tree index

From: <Jared.Still_at_radisys.com>
Date: Wed, 10 Mar 2004 21:35:01 -0800
Message-ID: <OF54656CE7.650343D2-ON88256E54.0015D6D3-88256E54.001E9615@radisys.com>

For the record, Steve did not make it to blevel of 20, he made it to 14.

Jared

"Jacques Kilchoer" <Jacques.Kilchoer_at_quest.com> Sent by: oracle-l-bounce_at_freelists.org
 03/10/2004 07:01 PM
 Please respond to oracle-l  

        To:     <oracle-l_at_freelists.org>
        cc:     "Richard Foote" <richard.foote_at_bigpond.com>
        Subject:        RE: Maximum height of an Oracle B-tree index

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Richard Foote Sent: mercredi, 10. mars 2004 14:31
To: oracle-l_at_freelists.org
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 ?    

Better than 6? Yes. Better than Steve Adams' record of 20? No, but my tablespace only had 100MB free. I got up to belevel = 14 before running out of space.
I didn't try anything clever, all I did was: 2K blocksize database, large index (maximum length for the Oracle version), pctfree 99, and inserting the index values in descending order.  

Sun Solaris 2.8
Oracle 8.0.6
db_block_size 2K
Index size = 79 MB, blevel = 14  

Proof:
SQL> select value
  2 from v$parameter
  3 where name = 'db_block_size' ;
VALUE

---

2048  

SQL> column segment_name format a12
SQL> select segment_name, segment_type, bytes, blocks, extents   2 from user_segments
  3 where (segment_name = 'T' and segment_type = 'TABLE')   4 or (segment_name = 'I' and segment_type = 'INDEX') ; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS EXTENTS

------------ ----------------- --------- --------- ---------
T            TABLE              20738048     10126       157
I            INDEX              82681856     40372       626
 

SQL> analyze index i compute statistics ; Analizzato indice.
SQL> select

  2     blevel, leaf_blocks, distinct_keys,
  3     avg_leaf_blocks_per_key,
  4     avg_data_blocks_per_key,
  5     clustering_factor, num_rows,
  6     sample_size,
  7     to_char (last_analyzed, 'SYYYY/MM/DD HH24:MI:SS') as last_analyzed

  8 from user_indexes
  9 where index_name = 'I' ;  

   BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY

--------- ----------- ------------- ----------------------- 
-----------------------

CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

----------------- --------- ----------- --------------------
       14       20189         20000                       1        1
            10000     20000           0  2004/03/10 18:50:17

SQL>  

• script to create objects drop table t ; create table t (c01 char (22), c02 char (22), c03 char (22), c04 char (22), c05 char (22), c06 char (22), c07 char (22), c08 char (22), c09 char (22), c10 char (22), c11 char (22), c12 char (22), c13 char (22), c14 char (22), c15 char (22), c16 char (22), c17 char (22), c18 char (23), c19 char (23), c20 char (23), c21 char (23), c22 char (23), c23 char (23), c24 char (23), c25 char (23), c26 char (23), c27 char (23), c28 char (23), c29 char (23), c30 char (23), c31 char (23), c32 char (23)) ; create index i on t (c01, c02, c03, c04, c05, c06, c07, c08, c09, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32) pctfree 99 ; declare commit_count constant pls_integer := 1000 ; r pls_integer := 0 ; v1 constant varchar2 (22) := 'abcdefghijklmnopqrstuv' ; v2 constant varchar2 (23) := 'abcdefghijklmnopqrstuvw' ; v3 varchar2 (23) ; low constant pls_integer := ascii ('a') ; high constant pls_integer := ascii ('z') ; c pls_integer ; len pls_integer ; begin
  • this select enables restart when loop fails because of
  • tablespace / rbs limits etc. select nvl (min (c32), 'zzzzzzzzzzzzzzzzzzzzzz' || chr (ascii ('z') + 1)) into v3 from t ; len := length (v3) - 1 ; loop for i in 0..len loop c := ascii (substr (v3, len + 1 - i, 1)) ; if c > low then v3 := substr (v3, 1, len - i) || chr (c - 1) ; if i > 0 then v3 := v3 || rpad (chr (high), i, chr (high)) ; end if ; exit ; end if ; end loop ; insert into t values (v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v2, v2, v2, v2, v2, v2, v2, v2, v2, v2, v2, v2, v2, v2, v3) ; r := r + 1 ; if mod (r, commit_count) = 0 then commit ; end if ; end loop ; end ; /
• show index statistics select value from v$parameter where name = 'db_block_size' ; column segment_name format a12 select segment_name, segment_type, bytes, blocks, extents from user_segments where (segment_name = 'T' and segment_type = 'TABLE') or (segment_name = 'I' and segment_type = 'INDEX') ; analyze index i compute statistics ; select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows, sample_size, to_char (last_analyzed, 'SYYYY/MM/DD HH24:MI:SS') as last_analyzed from user_indexes where index_name = 'I' ;

  ---

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 Wed Mar 10 2004 - 23:33:26 CST