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

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 24 Feb 2004 14:18:42 -0500
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AA0C@USAHM018.amer.corp.eds.com>

Joe, place the commit inside the loop, i.e., after each row insert and check to see what happens. On 9.2.0.4 the index is roughly double the size for the same process when you commit only once like in your example.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Joe Belka Sent: Tuesday, February 24, 2004 1:54 PM To: oracle-l_at_freelists.org
Subject: RE: RE: How does Oracle keep B-tree indexes to 3 levels?

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
-----------------------------------------------------------------
----------------------------------------------------------------
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 - 13:15:51 CST