Re: Sequence Numbers

If you're just using the sequence number as a key then why fetch it into Pro*C as an integer. Fetch it as a character string and you'll be back to the-end-of-uselss-life-of-the-plant for when it'll recycle.

Stan Driggs (stan_at_lfs.loral.com) wrote:

: 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

--
Kirk Bradley
Oracle Corporation
Mainframe and Integration Technologies Group