Re: Index Insider

Hi All,

Just thought I'd hop in and clarify this from the perspective of my book. Basically, I would say there is a word missing from the paragraph - "typical". The sentence should read "....with a TYPICAL maximum of 4 levels.". If you read on to the next sentence, "[The number of levels] directly correlates to the size of the Oracle block..", the real intention of the paragraph becomes clear. I'll put it in the Errata! The 4 levels comment comes from the bias of the book towards OLTP, with small (ie normally a lot less than 40 bytes) keys, and a comparatively small number of rows compared to data warehouses. If we compared a fairly large OLTP index (5 NUMBERs and a couple of DATEs), the maximum size of each key would be around 54-bytes (assuming the NUMBER was about 13 digits). This would make the table around 24 million rows, allowing a little in each index block for overhead. In OLTP, it is unusual to have (many) tables over this size, thus making a depth of 5 quite rare(1.6 billion rows max).

James

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:973948361.29278.0.nnrp-13.9e984b29_at_news.demon.co.uk...
>
> I can't resist.
>
> Let's assume that Oracle stores a depth
> indicator on each branch (can't think why,
> it would be a terrible cost on branch block
> splits, but there is a hint that it does when
> you do an index treedump). But let's make
> the assumption that somewhere (either
> stored, or in a piece of code) there is an
> explicit limiting.
>
> The smallest space to store it would be
> a signed byte - which would force a max
> of 127 branch levels.
>
> In the worst case, each branch points to
> just two blocks, so the number of leaf blocks
> would be power (2,127).
>
> Since power(2,10) is a bit over power(10,3)
> let's approximate power(2,127) to
> power(2,130) and call it power(10,3 * 13)
>
> Somehow I don't think I'll try the experiment.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
>
> Publishers: Addison Wesley Longman
> Book bound date: 8th Dec 2000
> See a first review at:
> http://www.ixora.com.au/resources/index.htm#practical_8i
>
> Connor McDonald wrote in message <3A0BDDF6.10A4_at_yahoo.com>...
>
> >I wonder what level you could actually achieve - take the smallest
> >blocksize, largest index keys and keep on pumping rows in until you hit
> >the table size limit then check the level...
> >
> >Anyone care to do the maths ??
> >
>
>
>
>
Received on Wed Nov 15 2000 - 02:48:18 CST