Re: RE: How does Oracle keep B-tree indexes to 3 levels?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 24 Feb 2004 17:03:47 -0000
Message-ID: <024301c3faf8$2b256410$6702a8c0@Primary>

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof DYnamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU> To: <oracle-l_at_freelists.org> Sent: Tuesday, February 24, 2004 4:55 PM Subject: RE: RE: How does Oracle keep B-tree indexes to 3 levels?

If data is random, and the blocks split 50/50 then on average the blocks indeed should be 75% full without any merging occuring whatsoever. I moved last weekend and so don't have my Knuth handy, but I believe he states this in one of hist texts. If data is not random then the above doesn't apply. For example, and index based on an ever increasing sequence wut 50/50 block splits would never use the block containing the the lower again. All the blocks save one of this index would be at most half-full. Do these half-full blocks get merged automatically some how? Should we be running "alter index coalesce" on the sequence based indexes. Is there anyway in 10G to instruct Oracle, that the data for these indexes is not random and the splits should be 100/0.

Ian macGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

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 - 11:00:36 CST