SQL Auto-Increment in Oracle APEX occasionally skips a chunk of numbers when incrementing?

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

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.