Of course, there is a small performance penalty to pay for committing
after each row, which is really insignificant compared to the disk
saving of 30k or more. The difference is coming from the fact that when
you commit in huge batches, ITAL's and locking info are left in the block
for the delayed cleanup.
On 02/24/2004 02:41:58 PM, Joe Belka wrote:
> 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
> > -----------------------------------------------------------------
> >
>
>
> ----------------------------------------------------------------
> 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:54:08 CST
