Re: Stupidity or sequences?

  • From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
  • To: valpis@xxxxxxxxx
  • Date: Fri, 12 Apr 2013 17:03:28 +0100

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@xxxxxxxxx> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


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


Other related posts: