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

  • From: chris@xxxxxxxxxxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 6 Feb 2004 09:22:50 +0000

Ryan,

From what I can remember, it was a good few years ago, DB2 trys to minimize 
index page splits and hence keep index levels to a minimum. It had the notion 
of sub-pages within an index page for locking puposes (it didn't do row-level 
locking then, I don't know about now). This sub-paging meant that it was 
inherently less space efficient than Oracle and therefore probably not as good 
at minimising the number of index levels.

I stand to be corrected by those with more up todate / better knowledge than me.

Chris



Quoting Ryan <ryan.gaffuri@xxxxxxx>:

> Is oracle the only database that is good at keeping the number of levels in
> an index to a minimum? Or is it common amongst the other major dbms?
> ----- Original Message -----
> From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Thursday, February 05, 2004 11:04 PM
> Subject: RE: How does Oracle keep B-tree indexes to 3 levels?
> 
> 
> > You get a new level only if the parent node splits propagate all the way
> > to the index root node. Jonathan Lewis and Steve Adams have some good
> > tests in their courses that use block dumps to show how Oracle
> > redistributes keys in branch blocks when nodes split.
> >
> > Yes, Oracle index nodes are one block apiece.
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > * Nullius in verba *
> >
> > Upcoming events:
> > - Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle
> > - SQL Optimization 101: 2/16 Dallas
> > - Hotsos Symposium 2004: March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ryan
> > Sent: Thursday, February 05, 2004 9:32 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: How does Oracle keep B-tree indexes to 3 levels?
> >
> > I'm taking an academic database class. My professor showed us that when
> > you have a block split and the parent node(s) split, you end up with a
> > new level. I'm assuming oracle 'redistributes' the pointers instead of
> > adding a new level?
> > Are index nodes always one block?
> > ----------------------------------------------------------------
> > 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
> -----------------------------------------------------------------
> 


Chris Dunscombe

chris@xxxxxxxxxxxxxxxxxxxxx

------------------------------------------------- 
Everyone should have http://www.freedom2surf.net/ 
----------------------------------------------------------------
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: