Oracle APEX 中的 SQL 自动增量在递增时偶尔会跳过一大块数字？
我在 APEX 中创建了一个表，该表的 PK 通过 SQL 序列递增：
CREATE SEQUENCE seq_increment
MINVALUE 1
START WITH 880
INCREMENT BY 1
CACHE 10
这似乎工作得很好。问题是，有时，通常当我早上起来并运行一个进程来输入新行时，它会跳过一堆数字。我之所以关心，是因为这些数字被用作我公司文档的 ID#，当该工具上线时，丢失/跳过数字块是不可接受的。
它似乎确实跳到了下一个“10”数字。即昨天我的最后一次测试分配了 883，今天早上它分配了 890 作为下一个数字。查看用于创建序列的代码，我注意到我已将其设置为缓存 10 个值，以便处理速度更快。是否有可能该缓存在一夜之间被转储，并且它正在提取 890，因为它在缓存中有 880-889 并且被转储了？
还有其他潜在原因和解决方案吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
序列不会也不可能生成无间隙值。因此您可能会期望数字偶尔会被跳过。当您使用序列时，这是完全正常的。
正如您所猜测的，最可能的情况是，当不使用 APEX 应用程序时，序列缓存在一夜之间从共享池中老化。您可以通过声明序列 NOCACHE 来减少间隙的频率，但这会降低性能，并且不会消除间隙，只会降低间隙的频率。
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.
Oracle 序列永远不能保证是连续的。如果您需要一组绝对连续的值，则需要实施自定义解决方案。
在这种情况下，
CACHE 10很可能就是您丢失数字的原因。缓存值是在内存中存储多少个序列值以供将来使用。重新启动将清除缓存并导致检索 10 个新值。类似地，如果该序列使用的时间不够长，则当前的值集可能会被从共享池中清除，同时也会导致检索一组新的值。在您的实例中显然不是这种情况，但序列号也可能由于回滚而丢失。涉及一个或多个序列的回滚事务会丢弃序列值。
Oracle sequences are never guaranteed to be contiguous. If you need an absolutely contiguous set of values, you'll need to implement a custom solution.
Odds are that
CACHE 10is why you're losing numbers in this case. The cache value is how many sequence values are stored in memory for future use. Rebooting will clear the cache and cause 10 new values to be retrieved. Similarly, if the sequence is not used for long enough, the current set of values may be flushed out of the shared pool, also causing a new set of values to be retrieved.This is clearly not the case in your instance, but sequence numbers can also be lost due to rollbacks. A rolled back transaction involving one or more sequences discards the sequence value(s).
一些序列号已经从内存结构之一中老化（我认为是共享池？）。这是序列的预期行为。您拥有的唯一保证是它们是独一无二的。如果您需要呈现无间隙序列，则必须在报告时使用 rownum 伪列来执行此操作。这是故意这样做的，否则您将不得不序列化所有插入，即锁定表。如果插入件回滚，即使这样也无法正常工作！
Some sequence numbers have been aged out of one of the in-memory structures (shared pool I think?). This is expected behaviour for sequences. The only guarantee that you have is that they are unique. If you need to present gap-free sequences you'll have to do this at reporting time using e.g. rownum pseudo-column. It is made this way deliberately otherwise you would have to serialise all inserts i.e. lock table. And even that wouldn't work properly if an insert was rolled back!