Re: leaf node 90-10 splits
Hi Tanel, I have no idea but if you currently have 9 entries in a leaf block and the 10th entry you're about insert causes this type of split, then 9 entries (the 90% currently in the existing leaf node) remain and the new entry (10%) goes into the new leaf node. A 90-10 (%) split. Possible with small blocks (say 2K) and large index entries (200ish bytes) when 2K blocks ruled the Oracle seas. Like I said I have no real idea but it's my theory and makes a good bed-time story. Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 12:24 AM Hi! 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Richard Foote INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: leaf node 90-10 splits
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: leaf node 90-10 splits
On 9i: select * from v$statname where name like '%leaf%'; Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/10/2003 07:39 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: leaf node 90-10 splits 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: leaf node 90-10 splits
Hi! SQL select * From v$sysstat where name like '%split%'; STATISTIC# NAME CLASS VALUE -- -- -- 195 leaf node splits 128612 196 leaf node 90-10 splits 128209 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: leaf node 90-10 splits
The failure to do the 90/10 split is a bug (it worked properly in 8.1) which is currently being investigated. I can't remember the number, but I passed it to Oracle some time ago. As to the original question - no idea, 100/0 seems to be the correct strategy; however, there was an earlier version of Oracle where I did some tests that showed Oracle doing block splits which whose position seemed to be affected by the relative position of the new entry in the block. But I can't find the tests, and I now wonder if it was just the special split to optimise branch compression that Steve Adams worked out a little while ago. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 9:24 PM Hi! SQL select * From v$sysstat where name like '%split%'; STATISTIC# NAME CLASS VALUE -- --- - -- -- 195 leaf node splits 128612 196 leaf node 90-10 splits 128209 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
