Re: leaf node 90-10 splits

Hi!

SQL> select * From v$sysstat where name like '%split%';

STATISTIC# NAME
CLASS VALUE

---------- ---------------------------------------------------------------- 
---------- ----------
       195 leaf node splits
128        612
       196 leaf node 90-10 splits
128        209
       197 branch node splits
128          3

I did a little test few days ago (using stats & treedumps):

If you insert an equal or larger key to the current max value in a full leaf block *within the transaction which filled the block*, just a new leaf block is added to index and "leaf node 90-10 splits" statistic is incremented. If you commit in the meantime, before "overflowing" the block, then the leaf block is split 50-50 and "leaf node splits" stat is incremented. So, Oracle 9.2 cares about transactions as well, in addition to checking key values...

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 10, 2003 5:39 PM

> Hi, Tanel,
>
> Where do you see this statistic? I only see "leaf node splits" in 8.1.7
and 9.2
> documentation. If the index is on strictly monotonically increasing
numbers,
> won't a new node be added to the right without a block split?
>
> Yong Huang
>
> > I wonder why does statistic "leaf node 90-10 splits" imply that
right-hand
> > index leaf block is split as 90-10, not 100-0 as it really is. (tested
on
> > 9.2.0.4 W2k).
> >
> > Historical reasons?
> >
> > Tanel.
>
> __________________________________
> Do you Yahoo!?
> New Yahoo! Photos - easier uploading and sharing.
> http://photos.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Yong Huang
> INET: yong321_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).