RE: Maximum height of an Oracle B-tree index

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

Jared






"Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
 03/10/2004 07:01 PM
 Please respond to oracle-l

 
        To:     <oracle-l@xxxxxxxxxxxxx>
        cc:     "Richard Foote" <richard.foote@xxxxxxxxxxx>
        Subject:        RE: Maximum height of an Oracle B-tree index


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
Behalf Of Richard Foote
Sent: mercredi, 10. mars 2004 14:31
To: oracle-l@xxxxxxxxxxxxx
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' ;

Other related posts: