| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: How does Oracle keep B-tree indexes to 3 levels?
>However, 9.2 introduced a bug in this special code
>that made it revert back to 50/50 splits. 10g is fixed.
Um, this is ugly. Do you know if Oracle ever created a patch for this? We are about to upgrade our Oracle Applications to 9.2.0.4 and the Apps make very heavy use of sequences (and indexes there on).
thanks,
Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
"The box said I needed to have windows 98 or better...So I installed linux."
jonathan_at_jlcomp.de
mon.co.uk To: oracle-l_at_freelists.org
Sent by: cc:
oracle-l-bounce_at_fr Subject: Re: RE: How does Oracle keep B-tree indexes to 3 levels?
eelists.org
02/24/2004 10:03
AM
Please respond to
oracle-l
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
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
-- 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 - 11:10:17 CST
 |
 |