SQL Auto-Increment in Oracle APEX occasionally ski

Question

I have created a table in APEX that has a PK that is incremented by a SQL sequence:

CREATE SEQUENCE seq_increment
MINVALUE 1
START WITH 880    
INCREMENT BY 1
CACHE 10

This seems to work perfectly. The issue is that sometimes, usually when I get on in the morning and run a process to enter a new row, it skips a bunch of numbers. I only care because these numbers are being used as the ID# of documents in my company and losing/skipping blocks of numbers is not going to be acceptable when this tool goes live.

It does seem to jump to the next '10' number. i.e. yesterday my last test assigned 883 and this morning it assigned 890 as the next number. Looking at my code for creation of the sequence I notice that I have set it up to cache 10 values so that it will process quicker. Is it possible that this cache is getting dumped over night and that it is pulling 890 because it had 880-889 in cache and it was dumped?

Are there other potential causes and solutions?

Answer 1

Some sequence numbers have been aged out of one of the in-memory structures (shared pool I think?). This is expected behaviour for sequences. The only guarantee that you have is that they are unique. If you need to present gap-free sequences you'll have to do this at reporting time using e.g. rownum pseudo-column. It is made this way deliberately otherwise you would have to serialise all inserts i.e. lock table. And even that wouldn't work properly if an insert was rolled back!

Answer 2

Sequences will not and can not generate gap-free values. So you'd expect that numbers will occasionally be skipped. That's perfectly normal when you're using sequences.

As you've surmised, the most likely scenario is that the sequence cache is aging out of the shared pool overnight when the APEX application isn't being used. You can reduce the frequency of gaps by declaring your sequence NOCACHE but that will decrease performance and it will not eliminate gaps it will just make them less frequent.

Answer 3

Oracle sequences are never guaranteed to be contiguous. If you need an absolutely contiguous set of values, you'll need to implement a custom solution.

Odds are that CACHE 10 is why you're losing numbers in this case. The cache value is how many sequence values are stored in memory for future use. Rebooting will clear the cache and cause 10 new values to be retrieved. Similarly, if the sequence is not used for long enough, the current set of values may be flushed out of the shared pool, also causing a new set of values to be retrieved.

This is clearly not the case in your instance, but sequence numbers can also be lost due to rollbacks. A rolled back transaction involving one or more sequences discards the sequence value(s).