Re: Stupidity or sequences?

The main difference between your proposal and the Oracle sequence mechanism is that the latter are visible to all sessions. If a session is going to build homegrown sequences and then cache them, then the cached values will be visible only within that individual session, unless that session will be performing some form of inter-process communication to "share" the cached values to other sessions. Yuck.

Oracle's sequence objects guarantee unique values; if you have a need to produce surrogate key values, they are perfect and have been designed for that task. They shouldn't be expected to guarantee against gaps or out-of-order values; they are not designed at all for producing check numbers for accounts payable, for example. If you have a requirement for ordering and against gaps, as with controlled paperwork such as checks, Oracle sequences are not a good option. A home-grown sequence method, minus the proposed caching, but including SELECT ... FOR UPDATE prior to the UPDATE itself to protect against buried updates, is probably the best option for requirements like that.

Hope this helps...

-Tim

  On 4/12/2013 10:03 AM, Nigel Thomas wrote:
> Johan
> A mechanism that can work very well (especially for high volume batch
> processing) is to reserve many IDs in one round trip (I have seen this done
> with sequences as well as with code control tables, by setting the sequence
> interval accordingly). This can be done in a separate (perhaps autonomous)
> transaction.
>
> So the program gets the next value, and then updates the control code table
> to (value + 500) say. It then manages that cache of 500 ID values
> internally, and doesn't need to go back to the database until the 500
> values are exhausted. This is how Oracle manages its own sequences, of
> course, and has the same drawback, that unused cached IDs can be lost, so
> there may be large gaps. And if you have multiple processes doing this, the
> ID values won't be monotonic with time (just as they aren't when you pull
> cached sequence numbers from different RAC instances). If that doesn't
> bother you, this approach can be pretty much as scalable as you like.
>
> HTH
>
> Nigel
>
>
>
>
> On 12 April 2013 16:24, Johan Eriksson <valpis_at_gmail.com> wrote:
>
>> Hi all,
>> I think most of us has seen someone trying to be smart or trying to gain
>> database independencies by not using oracle sequence but instead roll their
>> own system by using a table, and a row for each "sequence".
>> Almost every attempt on this I yet have seen has been plagued with row lock
>> contention or other concurrencies, scalability zero...
>>
>> Have anyone actually seen some implementation of this kind work when load
>> increase?
>>
>> /johan
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l