Oracle sequence caching

Question

I am trying to implement a sequence in an Oracle database to act as a surrogate key creator for a table. For performance reasons, I want this sequence to be cached. I have read that there are potential pitfalls when using cached sequences since rollbacks and instance failures will result in missed values.

This got me to thinking. Let's say I create a sequence with a cache size of 100. Then I make a 50 record insert to my table, with the sequence value as the primary surrogate key. After the commit, the current value of the sequence would not yet have been written to disk. Suppose I were to have an instance failure at this point. When the database comes back up, it is my understanding that the current sequence value will be reset to the last value written to disk.

If I were to try inserting another 50 records into my table, will I now break the primary key constraint because the sequence was reset to its last state from disk and primary keys are now getting reused? If this is the case, how would I prevent this?

Answer 1

Say the cache has the values 101-200. The value written on disk is 201. Your insert uses 101-150. Instance goes down. Instance starts up. Next time the sequence is used 201-300 will be cached.

Answer 2

Turns out that this is not (or no longer true). Shut down and restart of instance does not lose cached values. Simple test with cache = 1000.

SQL> select ordered.currval from dual;

CURRVAL

    22

SQL> select unordered.currval from dual

CURRVAL

    24

SQL> SHUTDOWN IMMEDIATE SQL> STARTUP

NEXTVAL

    23

SQL> select unordered.nextval from dual;

NEXTVAL

    25

Also, ALL_SEQUENCES.LAST_NUMBER does not hold the last last number provided by the sequence except on startup and before first NEXTVAL. After first NEXTVAL, it holds last number served plus CACHE_SIZE. This does not change until new cache is generated. However, on shutdown, it apparently gets reset to just the last number served.

Answer 3

No, this will not be the case.

Your sequence will continue at 101, the values between 50 and 100 will be missing.

The only reason to disable sequence caching is when trying to avoid gaps in your sequence, which is not relevant for most Primary Keys.

You might be interested in this article, which states that

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.