Re: Maximum height of an Oracle B-tree index
- From: "Richard Foote" <richard.foote@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Mar 2004 00:07:16 +1000
Hi Jared,
Thanks for chasing this up.
My memory must really be going as I'm sure Steve mentioned achieving an
index height far greater than 14. Cary Millsap was there at the time but I
guess I must have drunk too much wine the night before and mis-heard what
Steve said !!
Cheers
Richard
----- Original Message -----
From: Jared.Still@xxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, March 11, 2004 3:35 PM
Subject: 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' ;
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
