Re: Sequence Numbers
Date: 1995/04/04
Message-ID: <Pine.HPP.3.91.950404171303.9349A-100000_at_stimpy.mfa.com>#1/1
On 4 Apr 1995, Stan Driggs wrote:
> Date: 4 APR 1995 14:18:58 GMT
> From: Stan Driggs <stan_at_lfs.loral.com>
> Newgroups: comp.databases.oracle
> Subject: Re: Sequence Numbers
>
>
> |>> How can you get a sequence that never issues a number that is already in
> |>> use in a table?
> |>>
> |>> Sequence numbers are obviously most useful for creating unique numbers that
> |>> can be used as keys. The problem is, you run out of numbers at some point
> |>> (before infinity), so the sequence must cycle.
> |>Oracle sequence numbers can have up to 38 digits. ...
> |>A quick calculation shows that if
> |>your application inserted 1 row per microsecond (1,000,000 rows per
> |>second) you can run for approximately 3170979198376432606210073 years
> |>before you have to worry about a wrap around!
>
> But what if you are using Pro*C, and selecting the key into a long? 2^31
> is 2 billion, so now you are down to 9 digits. Using your example, you run
> out of numbers in an hour. 2 billion is small enough to cause some
> applications to get nervous.
>
> The whole point of my post was that I find it more than a little surprising
> that there is no built-in way to produce unique keys. Since defining a
> uniqueness constraint forces the database to check anyways, why not do this
> when producing a sequence number? I agree with you, doing an insert and
> waiting for a failure is probably the best alternative, since the DB is going
> to check anyways. It just goes against my nature to rely on failures.
>
> Stan
My question at this point is, do you need the mathematic value of the
sequence
number, or are you just trying to generate a unique identifier? If you just
need uniqueness, why self-impose the constraint of reading this number into
an unsigned long? C offers plenty of flexibility as far as string
manipulation; I would think you would want to read anything like this into
a character array. If you are relying on this sequence number as part of
some mathematical equation, but have the potential need to manipulate
numbers larger than the domain of an unsigned long, you either need to
switch datatypes to doubles or switch implementations of C to a
implementation/platform capable of handlind your needs.
I may be going off on a tangent because of the example someone chose to use here, but I do believe the sequence number is adequate for the need you described.
Dave
David B Erickson, Project Leader * McHugh Freeman * "YIP YIP YIP YIP YIP YIP YIP YIP YIP" erickson_at_mfa.com * -DinoReceived on Tue Apr 04 1995 - 00:00:00 CEST