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

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 you need to guarantee that there are no 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.

Answer 2

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.