Re: Stupidity or sequences?
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 14 Apr 2013 20:11:29 +1000
Actually, if CACHE is used and set to same size as the batch that needs
to be gapless, I've yet to see it gain a gap with an INSERT. Of course:
this was a serial generation *dedicated* sequence that was not being
used to generate ad-hoc single sequences by other processes. One type
of process, one sequence. And the sequence was not used in itself: it
was included in another formula that generated the actual, final gapless
number. There were of course other sequences and processes without any
need for gapless.
Entirely agreed: if the requirement is for a gapless generation in any
circumstance and combination of processes, then sequences are not the
way to go. Other than as starting points. For example, they may
provide the first digit in a thousands number, with ROWNUM being used to
add to it and generate a purely serial gapless number for a batch of,
say, 1000. Always guaranteed to start from a unique value, always
sequential from that value for the convention length. Of course other
algorithms based on timestamp can also be used, as noted.
It is rarely needed to do this sort of thing, though. In most cases,
simple uniqueness is all that is required. Gapless is a highly
over-rated requirement. Even for surrogate keys.
--
Cheers
Nuno Souto
dbvision@xxxxxxxxxxxx
On 14/04/2013 12:44 PM, Mark W. Farnham wrote:
> +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
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
