Re: Secret maximum for INITRANS?

I've found the note. It wasn't what I remembered, it was worse. I've just re-run a test on 9.0.1.2 that shows an interesting issue:

create table t1 (n1 number, v1 varchar2(10)); create index i1 on t1(n1) pctfree 0 initrans 10;

insert into t1
select rownum,'x'
from all_objects
where rownum <= 3000;

validate index i1;
select leaf_blk_len from index_stats;

This shows leaf_blk_len = 3904 on my 4K blocks - follow this with a block dump, and you find that the ITL has a total length of 2 on leaf blocks, despite the demand for initrans 10.

alter index rebuild;
validate index i1;
select leaf_blk_len from index_stats;

This shows leaf_blk_len = 3712 on my 4K blocks - follow this with a block dump, and you find that the ITL has a total length of 10 as required.

Comment: don't expect INITRANS to be obeyed if you do an array insert into an empty table until you have proved that it is actually obeyed. There are no doubt more tests you could do to pursue this one and find out exactly when things go wrong. My starting assumption is that it only goes wrong on a new, or truncated, table.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: ORACLE-L_at_fatcity.com <ORACLE-L_at_fatcity.com> Date: 05 March 2002 08:26

|
|Nice to know the actual strategy.
|
|I came across an oddity some time ago
|when trying to work this one out by setting
|silly values for INITRANS. This would be
|(correctly) ignored on a 'create index', and
|then obeyed on a 'rebuild index' with the
|result that the index got bigger. I think
|it was 8.1.5 - it doesn't reproduce in 8.1.7.3
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).