Re: unsequential sequences

On Fri, 08 Jul 2005 13:23:06 -0700, Kevin Blount interested us by writing:

> the problem is that the IDs created tend to a) end in 1 and b) jump 20
> numbers.
>
> if I were to insert a new record on two different days, the changes are
> that the IDs would be 301 and 321 (based on the last number above).
> However, if I were to add them both at the same time, or within a small
> time period) they would probably be 301 and 302.
>
> My DB admin has spotted the issue, but doesn't know the cause or the
> fix.
>

From the Oracle9i Administrator's Guide (which your DBA should review [again]) in Chapter 20 on administering sequences:

The CACHE option pre-allocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, Oracle reads another set of numbers into the cache.

Oracle might skip sequence numbers if you choose to cache a set of sequence numbers. For example, when an instance abnormally shuts down (for example, when an instance failure occurs or a SHUTDOWN ABORT statement is issued), sequence numbers that have been cached but not used are lost. Also, sequence numbers that have been used but not saved are lost as well. Oracle might also skip cached sequence numbers after an export and import. See Oracle9i Database Utilities for details.

> Can anyone help? I doesn't really effect any of the scripts I'm
> writing, but when those IDs are visible it make my applications look
> wierd, when 90% of the assigned IDs end in 1, ya dig?
>
> thanks for reading.. many thanks for replying :)

One concern I have is that you are mis-interpreting the purpose of sequences - easy to do, especially since it's a possibly a poor choice of names. (You are adding the requirement of 'consequitive numbering' to the requirement of 'unique ID'.)

However, one possible way of *minimizing* holes is to use nocache. You may still get holes if transactions are rolled back.

IMO, the only real way to guarantee no holes (and still maintain timely transactions) is to provide a periodic batch job that backfills a separate id column.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***