Re: RE: How does Oracle keep B-tree indexes to 3 levels?
- From: "Ron Thomas" <rthomas@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 24 Feb 2004 10:09:36 -0700
>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@xxxxxxxxxxxx
"The box said I needed to have windows 98 or better...So I installed linux."
jonathan@xxxxxxxxx
mon.co.uk To:
oracle-l@xxxxxxxxxxxxx
Sent by: cc:
oracle-l-bounce@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
----- Original Message -----
From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
