Re: Index Insider

I doubt if James Morle intended the paragraph to be interpreted the way you have taken it, this must be a little point where the grammar didn't work quite as intended. "Typically" you do only see a maximum of 4 non-leaf levels in real-life - in fact a very large number of sites will not go beyond 3 non-leaf levels.

I think that if JM had wanted to suggest that 4 was a real limit, he wouldn't have slipped it in as a throwaway comment at the end of a paragraph.

For amusement, the following is a little script that will create an N-level (root to leaf) index using power(2,N) rows.

rem
rem big_ind.sql
rem Create a table with an index that will grow very rapidly rem
rem Appropriate for 2K block sizes, for 4K blocks use rem 1500 + a bit for the column size and rpad(). rem
rem Interestingly, an IOT version packed 2 rows per leaf block rem

drop table big_ind_tab;

create table big_ind_tab (
 v1 varchar2(748),
 constraint bit_pk primary key (v1)
)
-- organization index
;

truncate table big_ind_tab;

begin
for i in reverse 1..64 loop -- choose any power of 2. insert into big_ind_tab values (
 rpad('x',738,'x') ||
 to_char(i,'FM0000000000')
);
commit;
end loop;
end;
/

validate index bit_pk;
select * from index_stats;

--

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

yong321_at_yahoo.com wrote in message <8ul7g8$5mr$1_at_nnrp1.deja.com>...


>Hi, Jonathan (and Howard),

>

>Quote from James Morle "Scaling oracle8i, Building Highly Scalable OLTP

>System Architectures" p.236:

>

>Oracle maintains very shallow B-trees. This means that the number of

>root/branch blocks read before the actual leaf block is hit is very low-

>-typically two (one root, one branch), with the maximum of four levels.

>

>End quote

>

>James seems to say that the number of levels *is* limited to 4 as if

>it's hardcoded in Oracle software, not because of common DBA practice

>or difficulty to do an experiment to achieve more than 4 levels.

>However, his book is not Oracle documentation. So we can take that with

>a grain of salt.