Re: Stupidity or sequences?
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 13 Apr 2013 15:56:31 +1000
I was going to comment that Nigel's proposal is a very good description
of how sequences in Oracle can work. After all, they can also cache
ahead a few values.
But Tim raises an interesting problem: how to generate, say, 500
consecutive values off a sequence, for use by cheques or any other
similar operation and ensure even if the generating transaction fails,
they'll be used.
One way I've seen is to generate them in a single INSERT into a
table_of_pending_cheques (topc) statement, using a sequence . This
guarantees the serial numbering and by committing after the INSERT,
ensure the cheque numbers stay there, even if this session fails to
further process them for any reason. They can then be "actioned" into
real cheques, by reading off topc table into somewhere else and then
throwing them away off topc at the end.
This worked really well in a similar situation - not cheques, but close
enough to be used as such.
--
Cheers
Nuno Souto
dbvision@xxxxxxxxxxxx
On 13/04/2013 4:00 AM, Tim Gorman wrote:
> Nigel,
>
> 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.
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
