RE: RE: How does Oracle keep B-tree indexes to 3 levels?
- From: "Joe Belka" <jbelka@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Feb 2004 18:53:59 -0000
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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
> Sent: 24 February 2004 17:04
> To: oracle-l@xxxxxxxxxxxxx
> 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@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:
