RE: Maximum height of an Oracle B-tree index
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Mar 2004 19:01:41 -0800
-----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:
