Oracle APEX中的SQL自动增量在递增时偶尔会跳过一些数字吗? [英] SQL Auto-Increment in Oracle APEX occasionally skips a chunk of numbers when incrementing?

问题描述

我已经在APEX中创建了一个表，该表的PK通过SQL序列递增:

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

这似乎很好.问题是，有时，通常当我早上起床并运行一个过程以输入新行时，它会跳过很多数字.我只在乎，因为这些数字在我公司中被用作文档的ID#，并且当此工具上线时，丢失/跳过数字块是不可接受的.

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.

它似乎跳到了下一个"10"数字.也就是说，昨天我的上次测试分配了883，今天早上分配了890作为下一个数字.在查看用于创建序列的代码时，我注意到我已将其设置为缓存10个值，以便更快地处理.此高速缓存是否有可能在一夜之间被转储，并且因为它具有880-889的高速缓存而被转储了，所以它正在拉890?

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?

推荐答案

序列将也不会生成无间隙值.因此，您希望数字有时会被跳过.当您使用序列时，这是完全正常的.

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.

如您所料，最有可能的情况是，当不使用APEX应用程序时，序列缓存会在一夜之间从共享池中老化.您可以通过声明序列NOCACHE减少间隔的频率，但这会降低性能，并且不会消除间隔，只会降低间隔的频率.

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 APEX中的SQL自动增量在递增时偶尔会跳过一些数字吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！