Let me START WITH sequences

It’s always cool that you can learn stuff every single day, even on the most simple of topics.  This one came from an AskTom question.  We define a sequence to start with 100, and then alter it to modify the INCREMENT BY.

SQL> create sequence test_seq INCREMENT BY 25 START WITH 100 MAXVALUE 1000 NOCACHE NOCYCLE;

Sequence created.

SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     : 1
MAX_VALUE                     : 1000
INCREMENT_BY                  : 25
CYCLE_FLAG                    : N
ORDER_FLAG                    : N
CACHE_SIZE                    : 0
LAST_NUMBER                   : 100
PARTITION_COUNT               :
SESSION_FLAG                  : N
KEEP_VALUE                    : N
-----------------

PL/SQL procedure successfully completed.

SQL> alter sequence test_seq INCREMENT BY 1 MAXVALUE 999999 NOCACHE NOCYCLE;

Sequence altered.

SQL> select test_seq.nextval from dual;

So the question is – what will the NEXTVAL query above return ?

Well, the following might come as a surprise.

SQL> select test_seq.nextval from dual;

   NEXTVAL
----------
        76

Yes, even though the sequence was defined as START WITH 100, when we look at the sequence definition, the combination of START WITH 100, and INCREMENT BY 25 has the following impact on the LAST_NUMBER value stored, when the ALTER command was issued.

SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     : 1
MAX_VALUE                     : 999999
INCREMENT_BY                  : 1
CYCLE_FLAG                    : N
ORDER_FLAG                    : N
CACHE_SIZE                    : 0
LAST_NUMBER                   : 76
PARTITION_COUNT               :
SESSION_FLAG                  : N
KEEP_VALUE                    : N
-----------------

PL/SQL procedure successfully completed.

SQL>

This is alluded to in the documentation for ALTER SEQUENCE

“If you change the INCREMENT BY value before the first invocation of NEXTVAL, then some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value. “