| 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?
I think in the past the 90/10 split has also been referred to as 99/1. I'm
curious, have any of you read a paper titled "A Study in Pctfree in B*-Tree
Indexes" by P R Marshall? Its available on the orapub web site and has some
interesting observations. I have not had the time to verify the info, but
would be curious if anyone else has read the paper. It's been around for a
while and does attempt to address some of the issues that have been
mentioned in this thread.
Andy Rivenes
arivenes_at_llnl.gov
At 05:03 PM 2/24/2004 +0000, Jonathan Lewis wrote:
>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
>
-- 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:20:09 CST
 |
 |