I meant ITL. Fat fingers, what can I say?
On 02/24/2004 03:39:29 PM, Jared.Still_at_radisys.com wrote:
> Mladen,
>
> Do you mean ITL? Or is ITAL yet something else I am ignorant of?
>
> Jared
>
>
>
>
>
> Mladen Gogala <mladen_at_wangtrading.com>
> Sent by: oracle-l-bounce_at_freelists.org
> 02/24/2004 11:56 AM
> Please respond to oracle-l
>
>
> To: oracle-l_at_freelists.org
> cc:
> Subject: Re: RE: How does Oracle keep B-tree indexes to 3 levels?
>
>
> 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
> -----------------------------------------------------------------
>
>
>
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 - 14:50:19 CST
