Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequence starts with 1 after reaching MAXVALUE even STARTWITH 100 specified

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?

like image 599
Mikayil Abdullayev Avatar asked Sep 25 '14 10:09

Mikayil Abdullayev


People also ask

What happens when a sequence reaches the Maxvalue and the cycle value is set?

This is the default. CYCLE 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.

What is sequence default start with value?

The default starting value is minvalue for ascending sequences and maxvalue for descending ones.

How do you increment a sequence value?

The syntax to create a sequence in Oracle is: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; sequence_name. The name of the sequence that you wish to create.


2 Answers

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.

like image 159
yamny Avatar answered Oct 17 '22 02:10

yamny


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.

like image 4
Justin Cave Avatar answered Oct 17 '22 04:10

Justin Cave