RE: RE: How does Oracle keep B-tree indexes to 3 levels?

From: Joe Belka <jbelka_at_ntlworld.com>
Date: Tue, 24 Feb 2004 18:53:59 -0000
Message-ID: <NPEFJEHBKIBFDNLNFILBEELJCFAA.jbelka@ntlworld.com>

Index block splits for monotonic indexes seems to be fixed in 9.2.0.3.

SQL> select * from v$version;

BANNER

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production

SQL> @idxtest
SQL>
SQL> drop table idxtest;

Table dropped.

SQL> drop sequence idxtest_seq;

Sequence dropped.

SQL> create table idxtest (
2 col1 number, col2 varchar2(80),
3 constraint idxtest_pk primary key ( col1 ) );

Table created.

SQL> create sequence idxtest_seq;

Sequence created.

SQL>
SQL> begin
2 for i in 1..10000 loop

  3      insert into idxtest ( col1, col2 )
  4        values ( idxtest_seq.nextval, rpad('x',80,'x') );

  5 end loop;
  6 commit;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index idxtest_pk validate structure;

Index analyzed.

SQL>
SQL> select name, blocks, lf_blks, lf_blk_len, used_space from index_stats;

NAME                               BLOCKS    LF_BLKS LF_BLK_LEN USED_SPACE
------------------------------ ---------- ---------- ---------- ----------
IDXTEST_PK                             24         18       7996     139971

SQL>
SQL> drop table idxtest;

Table dropped.

SQL> drop sequence idxtest_seq;

Sequence dropped.

SQL> create table idxtest (
2 col1 number, col2 varchar2(80),
3 constraint idxtest_pk primary key ( col1 ) );

Table created.

SQL> create sequence idxtest_seq;

Sequence created.

SQL>
SQL> begin
2 for i in 1..10000 loop

  3      insert into idxtest ( col1, col2 )
  4        values ( dbms_random.random, rpad('x',80,'x') );

  5 end loop;
  6 commit;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index idxtest_pk validate structure;

Index analyzed.

SQL>
SQL> select name, blocks, lf_blks, lf_blk_len, used_space from index_stats;

NAME                               BLOCKS    LF_BLKS LF_BLK_LEN USED_SPACE
------------------------------ ---------- ---------- ---------- ----------
IDXTEST_PK                             40         32       7996     174838

SQL> Regards
Joe.

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
> Sent: 24 February 2004 17:04
> To: oracle-l_at_freelists.org
> Subject: Re: RE: How does Oracle keep B-tree indexes to 3 levels?
>
>
>
> For an index based on a column populated from
> an increasing sequence that is being used properly,
> the split is not 50/50. Oracle recognises the special
> case and does what is named in the stats as a 90/10
> split - but in fact is a 100/0 split, putting the top row
> into the next block when the current block is full.
>
> However, 9.2 introduced a bug in this special code
> that made it revert back to 50/50 splits. 10g is fixed.
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>

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 Tue Feb 24 2004 - 12:50:58 CST