Oracle序列缓存 [英] Oracle sequence caching

问题描述

我试图在Oracle数据库中实现序列，作为表的替代密钥创建者。出于性能原因，我希望这个序列被缓存。我读过，使用缓存序列时存在潜在的陷阱，因为回滚和实例失败将导致错误的值。

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.

这让我想到。让我们说，我创建一个缓存大小为100的序列。然后我做一个50记录插入我的表，序列值作为主要代理键。提交后，序列的当前值尚未写入磁盘。假设我现在有一个实例失败。当数据库恢复时，我的理解是，当前序列值将被重置为写入磁盘的最后一个值。

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.

如果我尝试插入另一个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?

推荐答案

不，不会是这样。

您的序列将在 101 处继续， 50 code>

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.

您可能对本文，其中说明

创建序列的缺点
带有缓存是如果系统
失败，所有未被使用的缓存序列
值将是
lost。这导致在
分配的序列值中的间隙。当
系统恢复时，Oracle将
缓存从序列中剩下
的新数字，忽略那么
称为丢失序列值。

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序列缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！