RE: Stupidity or sequences?

There is a special case requirement, that of needing contiguous numbers within a single thread, where if you have a known maximum batch size you can use the stride of the sequence to get your chunk of numbers. Then the single batches' set of numbers will be contiguous, cheap to fetch, somewhat correlated with the order of the sequence, and unique.

But, agreed, it fulfills NEITHER gapless overall use nor strict chronological order across multiple threads. This is a bit simpler than doing the time math if all you need is ordered gapless within a batch and not duplicated in any other batch. If you don't need gapless, you don't have to match the maximum batch size and stride, just set a reasonable stride and take another drink if you need to for processing a given batch. Using the stride DOES prevent another session's ill-mannered use of the sequence from trashing gaplessness within a single thread. (Oracle calls stride increment_by, I always write stride because it matches up with some math I did a long time ago involving piecewise multiple linear regression.) Knowing the start time of a batch and the end time of a batch can be used across all batches for information lifecycle planning involving use of the sequence, and getting two clock calls instead of n may from time to time be useful.

Having the numbers strictly in order and gapless is useful in statistics processing or using binary search methods, but that tends to be in a program post processing the data. Still there are uses.

Please carefully note that NONE of this is intended to contradict what Tim wrote but represents rather a special use case I've seen a few times that seems valid to me.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Saturday, April 13, 2013 7:42 PM
To: oracle-l_at_freelists.org
Subject: Re: Stupidity or sequences?

Nuno,

Agreed on the basic suggestion to save pre-provisioned numbers (and related information) in a table, but....

 >> 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

This guarantees neither serial nor gapless numbering. An Oracle sequence is inherently multi-user and nothing will stop that; another session with a user thinking "hmmm, what's the present value in this sequence?" could perform a SELECT xxx.NEXTVAL FROM DUAL at any time while the pre-provisioning INSERT ... SELECT is in progress and create a gap in the set of inserted values.

Solution: have the pre-provisioning session generate its own values, using a loop, for example. Stay completely away from Oracle sequences when attempting to fulfill requirements for which Oracle sequences were not designed. As long as the default setting of NOCYCLE is retained, Oracle sequences will generate unique values using monotonically ascending numerics, with gaps always possible and (in the case of RAC) possibly out of chronological order. If chronologicaling order is a requirement, use SYSDATE or SYSTIMESTAMP, not a sequence; dates and timestamps can always easily be made into numerics if needed, if that matters. If no gaps are a requirement, pre-provision values as Nuno has described -- without the use of a sequence.

Thanks!

-Tim

On 4/13/2013 8:03 AM, Nuno Souto wrote:
> Bingo! Basically, the idea is to use the INSERT to grab a gapless
> series and stash it away for later use, either by the original session
> or another that "cleans up" later if original fails for whatever
> reason. As in: grab a sequence of numbers to be used in serialising
> documents/cheques/invoices/whatever physical representation is needed.
> In a fashion that avoids serial loss if serializing session fails.
> Of course, just like you said: it's all about what the needs are. In
> most cases where gapless is not needed, a simple Oracle sequence will
> do the job nicely and without major convolutions.
>

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


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