6

I've come across a very weird Oracle sequence behavior. I have the following Sequence:

CREATE SEQUENCE SEQ1 INCREMENT BY 10 START WITH 100 MAXVALUE 200 CYCLE NOCACHE;

Here's an excerpt from "OCA/OCP Oracle Database 11g All-in-One Exam Guide":

CYCLE Controls the behavior on reaching MAXVALUE or MINVALUE. The default behavior is to give an error, but if CYCLE is specified the sequence will return to its starting point and repeat.

From this I infer that after reaching the MAXVALUE of 200, I'll get 100, as the starting point is 100. But surprisingly I get one. Why is that?

Mikayil Abdullayev
  • 12,117
  • 26
  • 122
  • 206

3 Answers3

9

Let's look at the following excerpt from document:

Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

It means that START WITH value is not enough in your case, so both MINVALUE and MAXVALUE should be settled. Without given MINVALUE, cycle will start from number 1.

yamny
  • 660
  • 4
  • 13
  • That really explains the behavior. It clearly indicates that `it generates its minimum value`. So the book I was referring had to be careful when choosing words. In fact that book is also by Oracle and there's no word about starting with the minimum value. – Mikayil Abdullayev Sep 25 '14 at 11:24
  • The same can go to international books that are translated by _translators_ and not by _database specialists_ – yamny Sep 25 '14 at 12:46
4

When your sequence cycles, it starts again at the MINVALUE of the sequence. That defaults to 1 if you don't specify a value. If you wanted the sequence to start again at 100, you'd need to specify a MINVALUE of 100.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0

I was reading the oracle document on this, and it was written like 'it will start with 100' in their case 1000

Creating a Sequence: Example The following statement creates the sequence customers_seq in the sample schema oe. This sequence could be used to provide customer ID numbers when rows are added to the customers table.

CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.

Singh Gaurav
  • 147
  • 3
  • 12