Maximum value allowed for CACHE in CYCLE sequence

Question

CREATE SEQUENCE demo_seq
START WITH 1
INCREMENT BY 3
MINVALUE 1
MAXVALUE 14
CYCLE
CACHE (?);

According to formula i.e.

(CEIL(MAXVALUE-MINVALUE))/ABS(INCREMENT)

So,

(CEIL(14-1))/ABS(3)

The value is 4.33 So what is the CACHE value for the above Sequence 4 or 5.

Answer 1

You've taken that formula from the documentaion:

CACHE
Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

You seem to have missed the "must be less than" part of that paragraph. So in your example the cache would have to be less that 4.333; and therefore would have to be 4 - at least according the documentation. Oracle does actually allow you to set the cache to 5 though; it only complains about 6 or above:

CREATE SEQUENCE demo_seq
START WITH 1
INCREMENT BY 3
MINVALUE 1
MAXVALUE 14
CYCLE
CACHE 5;

Sequence DEMO_SEQ created.

drop sequence demo_seq;

Sequence DEMO_SEQ dropped.

CREATE SEQUENCE demo_seq
START WITH 1
INCREMENT BY 3
MINVALUE 1
MAXVALUE 14
CYCLE
CACHE 6;

ORA-04013: number to CACHE must be less than one cycle

... so that looks like a documentation bug.