Re: Sequence Numbers

joachim_at_softouch.bc.ca (Joachim Achtzehnter) writes:
|
|>
|>On 3 Apr 1995 Stan Driggs <stan_at_leia.endicott.ibm.com> writes:
|>>
|>> 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 only way I can think of to test this is to search the table, or insert
|>> the record and wait for a failure. Neither option is very efficient.
 

|>...my guess is that just trying an insert and relying on a
|>uniqueness constraint to cause a failure may still be the most efficient
|>alternative.
|>
|>Joachim

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 Received on Tue Apr 04 1995 - 00:00:00 CEST