RE: Stupidity or sequences?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <tim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Apr 2013 22:44:25 -0400
+1 to what Tim wrote.
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tim Gorman
Sent: Saturday, April 13, 2013 7:42 PM
To: oracle-l@xxxxxxxxxxxxx
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.
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
