Re: Ini_trans for indexes

I think I've changed my mind since then.

It's also interesting to note that the default __implemented__ value on tables for Oracle 89 is 2 (even though user_tables reports 1).

I haven't decided on a right answer, but I think that 'table + 1' is probably unnecessary on the following basis:

If you have 20 concurrent updates on a single block in a table - what is the probability that you are updating every row on a specific column so that every row appears in the same block in the index on that column ? Probably pretty low.

As far as inserts are concerned - if you have twenty concurrent inserts on a single block, you should have set freelists and freelist groups to split the inserts across multiple blocks of the table.

If you have multiple inserts that manage to insert into the same index block at the same time, then isn't the index block likely to split anyway - making lots of free space ? I haven't actually tested what happens to an index block when (three concurrent inserts are relevant to the block, and two have managed to fill it completely - so this question is still open).

--
Regards

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

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

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





Norman Dunbar wrote in message ...


>Hi Ashok,

>

>I asked this very same question some time ago, and the general

opinion


>was index should be one higher than the table.

>

>Cheers,

>Norman.

>

>-------------------------------------

>Norman Dunbar

>Database/Unix administrator

>Lynx Financial Systems Ltd.

>mailto:Norman.Dunbar_at_LFS.co.uk

>Tel: 0113 289 6265

>Fax: 0113 289 3146

>URL: http://www.Lynx-FS.com

>-------------------------------------