Re: Sequence Numbers

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. Of course it is correct to say that you run out of numbers before infinity, in practise, however, you must have a pretty unusual application to reach this limit within the application's lifetime. 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! Hopefully by that time nobody uses Oracle anymore :-)

>
> 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.
>

Assuming there is no requirement to restrict the range of the sequence the best and most effcient approach is to ignore the problem.

If you must use a small range so that wrap-around becomes possible, then the best approach may depend on the way rows are deleted from your table. Although 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. If older rows are more likely to be deleted (as opposed to a completely random pattern) it may help to keep track of available ranges of sequence numbers.

Joachim

--
__________________________________
private:  joachim_at_wimsey.bc.ca
work:     joachim_at_softouch.bc.ca