以 2 而不是 1 开头的 Oracle 序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4153807/
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 starting with 2 instead of 1
提问by Peter Lang
Unexpected behavior:
意外行为：
I am encountering strange behavior of Oracle sequences with 11g (works with 10g):
我在使用 11g（适用于 10g）时遇到 Oracle 序列的奇怪行为：
CREATE SEQUENCE test_sequence START WITH 1;
CREATE TABLE test_table ( val INT );
INSERT INTO test_table VALUES ( test_sequence.NEXTVAL );
Even though the sequence starts with 1, the first value inserted is 2:
即使序列以1开头，插入的第一个值也是2：
SELECT * FROM test_table;
VAL
----------
2
Expected behavior:
预期行为：
Selecting NEXTVALwithout the insert works as expected:
NEXTVAL没有插入的选择按预期工作：
CREATE SEQUENCE test_sequence_2 START WITH 1;
SELECT test_sequence_2.NEXTVAL FROM dual
NEXTVAL
----------
1
Question:
题：
Can anyone reproduce this using Oracle 11g? Is this a known issue?
任何人都可以使用 Oracle 11g 重现这个吗？这是一个已知的问题？
I'm usingOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.
我正在使用Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.
回答by Leigh Riffel
This is documented in the 11.2 SQL Language Referencewhere it says,
这记录在 11.2 SQL Language Reference 中，它说，
If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped.
如果您尝试将序列值插入到使用延迟段创建的表中，则序列返回的第一个值将被跳过。
See the link in Jeffrey Kemp's answer for a My Oracle Support (Metalink) note and a workaround.
有关 My Oracle Support (Metalink) 说明和解决方法，请参阅 Jeffrey Kemp 的回答中的链接。
回答by Jeffrey Kemp
I'd say the cause is this "undocumented feature". See My Oracle Support Document ID 1273858.1 (which is unfortunately behind a paywall and cannot be copied here).
我会说原因是这个“未记录的功能”。请参阅 My Oracle Support 文档 ID 1273858.1（不幸的是，它位于付费墙后面，无法在此处复制）。
Try it without deferred segment creation and see if the problem persists.
在不创建延迟段的情况下尝试一下，看看问题是否仍然存在。
回答by Tony Andrews
I can't reproduce on 11G, i.e. the table contains a 1 after following your steps.
我无法在 11G 上重现，即按照您的步骤，该表包含一个 1。
However, it is debatable whether this should be considered an "issue", because sequences are never guaranteed to be gap-free. What START WITH guarantees is that the sequence will never return a value lowerthan the specified starting value - e.g. to avoid conflicts with existing data. I do agree however that what you are seeing is surprising and I would be interested to know the reason!
然而，这是否应该被视为一个“问题”是有争议的，因为序列永远不能保证是无间隙的。START WITH 保证的是序列永远不会返回低于指定起始值的值——例如避免与现有数据发生冲突。但是，我同意您所看到的令人惊讶，我很想知道原因！
回答by Leomir Andrade
Use:
用：
CREATE SEQUENCE SQ_SEQUENCE_NAME
INCREMENT BY 1
START WITH 1
MINVALUE 0 -- This will ensure start at 1!
MAXVALUE 99
NOCYCLE
NOCACHE
ORDER;
