Explain the different parameters of ‘CREATE SEQUENCE’ statement

 

Q) Explain the different parameters of ‘CREATE SEQUENCE’ statement.

ANS:

a)    START WITH –

·       Specifies the first sequence number to be generated.

b)    INCREMENT BY –

·       Specifies the interval b/w sequence number.

·       This integer value can be any Positive or Negative integer, but it can’t be 0.

c)    MAXVALUE –

·       Specifies the maximum value, the  sequence can generate.

·       MAXVALUE must be >= START WITH and must be > MINVALUE.

d)    NOMAXVALUE –

·       Specifies to indicate a maximum value of 10²⁷ for an ascending seq or -1 for a descending seq (by default).

e)    MINVALUE –

·       Specifies the minimum value of the sequence.

·       MINVALUE must be <= START WITH and must be < MAXVALUE.

f)     NOMINVALUE –

·       Specifies to indicate a minimum value of 1 for an ascending seq or -10²⁶ for a descending seq (by default).

g)    CYCLE –

·       Specifies that the value reaching either its MIN/MAXVALUE, sequence will restart its cycle from beginning.

h)    NOCYCLE –

·       Specifies to indicate that the sequence can’t generate more value after reaching its MIN/MAXVALUE.

i)      CACHE –

·       Specifies how many value of the sequence, the Database pre-allocates and keeps in memory for faster access.

j)      ORDER –

·       Specify to guarantee that sequence number are generated in order of request.