Re: Sequence Numbers

From: Dave Erickson <erickson_at_mfa.com>
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.

BTW, Oracle DOES generate a guaranteed unique identifier for each row you insert, called 'rowid'. It's Oracle-specific, so if you're trying to stick to generic SQL, it's probably not a good idea, but if you're that concerned about getting a unique id per row, take the unique constraint off your primary key, and rely on this. That would be a pretty unorthodox approach for me, as well. I've never done it. But it does provoke thought...

Dave

David B Erickson, Project Leader     * 
McHugh Freeman                       * "YIP YIP YIP YIP YIP YIP YIP YIP YIP"
erickson_at_mfa.com                     *                           -Dino
Received on Tue Apr 04 1995 - 00:00:00 CEST

Original text of this message