RE: RE: How does Oracle keep B-tree indexes to 3 levels?
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Feb 2004 08:55:56 -0800
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
-----Original Message-----
From: Tanel Põder [mailto:tanel.poder.003@xxxxxxx]
Sent: Wednesday, February 18, 2004 4:28 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: RE: How does Oracle keep B-tree indexes to 3 levels?
Hi Jonathan,
Is this a public paper you're talking about?
Tanel.
> ** Footnote - I used to believe it was supposed to be at 75% because
> on average a block was somewhere between half full and full - but I've
> given a reference to a paper that demonstrates that the failure to
> merge in real time leaves Oracle with a lower value. I haven't yet
> found time to read the paper, though.
>
>
> 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 March 2004
> Charlotte NC OUG - 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: <ryan.gaffuri@xxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Friday, February 06, 2004 1:58 PM
> Subject: Re: RE: How does Oracle keep B-tree indexes to 3 levels?
>
>
> so nodes can be as sparse as having 1-2 pointers in them? Doesnt this
> increase the size of the tree and decrease performance?
>
> also, does oracle use 'sparse' indexes. With standard dense indexes
> there
is
> a pointer to every record in the table. With sparse indexes you get
pointers
> to a range of records.
>
> For example.
>
> You have a column with
>
> 1
> 2
> 3
> 4
> 5
>
>
> You might have a pointer to '1' and a pointer to '5'. Does oracle use
this?
> >
>
>
> ----------------------------------------------------------------
> 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
