Oracle Sequence starting with 2 instead of 1
Unexpected behavior:
I am encountering strange behavior of Oracle sequences with 11g (works with 10g):
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:
SELECT * FROM test_table;
VAL
----------
2
Expected behavior:
Selecting NEXTVAL
without the insert works as expected:
CREATE SEQUENCE test_sequence_2 START WITH 1;
SE开发者_如何转开发LECT test_sequence_2.NEXTVAL FROM dual
NEXTVAL
----------
1
Question:
Can anyone reproduce this using Oracle 11g? Is this a known issue?
I'm using
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
.This is documented in the 11.2 SQL Language Reference where it says,
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.
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).
Try it without deferred segment creation and see if the problem persists.
I can't reproduce on 11G, i.e. the table contains a 1 after following your steps.
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 lower than 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!
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;
精彩评论