| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: How does Oracle keep B-tree indexes to 3 levels?
Indeed Mark, just as you say:
Monotonic:
NAME BLOCKS LF_BLKS LF_BLK_LEN USED_SPACE ------------------------------ ---------- ---------- ---------- ---------- IDXTEST_PK 40 34 7996 140131 Random: NAME BLOCKS LF_BLKS LF_BLK_LEN USED_SPACE ------------------------------ ---------- ---------- ---------- ---------- IDXTEST_PK 40 32 7996 174795
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Powell, Mark D
> Sent: 24 February 2004 19:19
> To: 'oracle-l_at_freelists.org'
> Subject: RE: RE: How does Oracle keep B-tree indexes to 3 levels?
>
>
> 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
> -----------------------------------------------------------------
>
-- 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:39:00 CST
 |
 |