Re: Sequence Numbers

Stan Driggs (stan_at_lfs.loral.com) wrote:
> 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. Now, if you are using the
> sequence numbers as a primary key in a table, there is no way to tell if the
> next sequence number in already in the table (i.e. it is really not available).
> 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.
> Any one have a better way? I just can't believe that there is no reliable
> mechanism for generating primary key values.

You have to search the table if there exists a possibility of a seq. number already existing; but that defeats the purpose of sequence numbers. You should have a purge done on the table after some interval (it may be in years). So if you declare a sequence of number(12), the maximum sequence number you would get in 999,999,999,999, lets say you lose 1/4 of those because of cache overhead, so your max is 7.5 EE 11. To put that in perspective if your table gets a 100,000 rows per month; without any purging you would run out of sequences in 7,500,000 months or 625,000 years, or 6,250 centuries (approximately). I hope by that time you are not carrying data that is 625,000 years old; because if you are that table would require 7.5 EE 13 bytes (say 100 byte records) approx.

That is some huge number.

AND sequence can be more than number(12).

My point is that declare a sequence number with a big maximum. There is not much overhead of declaring a bigger one. Then if you have a primary key constraint, by the time the seq turns around and if there is a conflict, you would get an insert error. After 625,000 years, you might have to write a script to some how adjust table seq numbers but by that time we would not be using Oracle 7.x.x.x.x.x.x.x.x.

So don't worry about that possibility, but make sure you appropriately trap the insert errors.

--
**************************************************************
*                          Saad Ahmad                        *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************