Oracle 序列缓存
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2381681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sequence caching
提问by Melvin
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.
我正在尝试在 Oracle 数据库中实现一个序列来充当表的代理键创建者。出于性能原因，我希望缓存此序列。我已经读到使用缓存序列时存在潜在的缺陷，因为回滚和实例失败会导致丢失值。
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.
这让我开始思考。假设我创建了一个缓存大小为 100 的序列。然后我将 50 条记录插入到我的表中，并将序列值作为主代理键。提交后，序列的当前值尚未写入磁盘。假设此时我有一个实例失败。当数据库恢复时，我的理解是当前序列值将重置为写入磁盘的最后一个值。
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?
如果我尝试将另外 50 条记录插入到我的表中，我现在是否会打破主键约束，因为序列已从磁盘重置为其最后状态并且主键现在得到重用？如果是这种情况，我将如何防止这种情况发生？
回答by Peter Lang
No, this will not be the case.
不，情况不会如此。
Your sequence will continue at 101, the values between 50and 100will be missing.
您的序列将继续101，之间的值，50并且100将丢失。
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.
使用缓存创建序列的缺点是，如果发生系统故障，所有未使用的缓存序列值都将“丢失”。这会导致分配的序列值出现“差距”。当系统恢复时，Oracle 将从它在序列中停止的位置缓存新数字，忽略所谓的“丢失”序列值。
回答by Todd Pierce
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.
假设缓存的值为 101-200。写入磁盘的值是 201。您的插入使用 101-150。实例关闭。实例启动。下次使用该序列时，将缓存 201-300。
回答by Jeff Jacobs
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.
事实证明这不是（或不再正确）。关闭和重启实例不会丢失缓存值。缓存 = 1000 的简单测试。
SQL> select ordered.currval from dual;
SQL> selectordered.currval from dual;
CURRVAL
曲率
22
SQL> select unordered.currval from dual
SQL> select unordered.currval from dual
CURRVAL
曲率
24
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP
SQL> 立即关闭 SQL> 启动
NEXTVAL
下一个值
23
SQL> select unordered.nextval from dual;
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.
此外，ALL_SEQUENCES.LAST_NUMBER 不保存序列提供的最后一个数字，除了在启动时和第一个 NEXTVAL 之前。在第一个 NEXTVAL 之后，它保存最后提供的数字加上 CACHE_SIZE。这在生成新缓存之前不会改变。但是，在关闭时，它显然会重置为最后一个服务的数字。
