sequence in cycle mode in oracle

Question

I have tried to find some real use cases fot sequences in cycle mode.

we can use use sequence for generating unique IDs for primary keys but it is not needed because we can use IDENTITY as well.

can you give me some good practical scenarios for using CYCLE sequence incrementing by 1 and then by a higher number like 10 or 100?

thanks

Answer 1

As you said, we usually use sequences for generating unique values - a good choice for primary keys. As primary keys can't be duplicated, there's no much sense in cycling the sequence.

(As of the identity columns: yes, that's an option in recent Oracle database versions, but they didn't exist until 12c so we used and still use sequences in lower versions).

Personally, I've never used MAXVALUE; most of my sequences are simple, using default options, such as

create sequence seq;

However, if you do set MAXVALUE and don't pay attention to number of values you use from it, once you reach its maximum you'll get

ORA-08004: sequence SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

One solution to that problem is to remove maxvalue (or set it to a higher value); another one is to use CYCLE so that - once you reach the maximum - sequence keeps working. Though, you have to use the CACHE parameter along with it (and its value must be lower than one cycle):

SQL> create sequence seq maxvalue 3 cycle;
create sequence seq maxvalue 3 cycle
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

Cycle is "3", so you can't set CACHE to value higher than that. This works:

SQL> create sequence seq maxvalue 3 cycle cache 2;

Sequence created.

When to use it?

In cases where its value is part of a composite primary key, where the second (actually, other) column(s) make sure that cycled sequence values won't violate primary key.

Another option is a staging table; for example, you daily get up to 1 million rows that represent payments. If primary key is set to number(6), you can't let sequence unconstrained (without the maxvalue) because you won't be able to insert value higher than 999.999 into that column. But, if you use the CYCLE parameter, everything will work OK - there won't be duplicates and values will fit the column.