Re: MAXTRANS in indexes

In <C7Mvyx.Do8_at_world.std.com> edwards_at_world.std.com (Jonathan Edwards) writes:

>Oracle doesn't do page-level locking, but this MAXTRANS limit is almost as
>bad. You have to permanently allocate space in each page for concurrent
>transaction accesses, and lock out transactions when this overflows.
 

>Isn't this an enormous problem, particularly on indexes? Lets say that I am
>indexing a table on a sequence-generated field. By definition, all inserts
>will be clustered into the last leaf of the index. Therefore it would seem
>that I need INITRANS = entries/leaf to avoid lockouts. At 23 bytes/tran,
>this means about half the index is wasted on transactions!
>THis then doubles the IO to this index.

You are assuming that your userbase/hardware combination is capable of generating as many simultaneous insert transactions as your (reduced) number of index entries per leaf block. You are also presumably aware that your "about half" depends on your (unstated) assumptions about the value of PCTFREE and the length of entries in the index in question.

>And I can't double the block size for this index to compensate, because
>Oracle only supports a single global permanent block size, which is a
>severe limitation in its own right.

It's never struck me as a *severe* limitation, more as a fact-of-life.

>Is this analysis correct?

If the above assumption is true, then I believe your analysis is correct (and I think it stinks, also).

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.