Sequences in Oracle

Jun 04 2017 04:06 AM by Kishan Mashru

A sequence is an Oracle object, that generates unique integer values each time the sequence is incremented.

 As sequences generate a unique number each time it is incremented, they are useful in creating Unique or Primary Key values for any table, also the sequences are independent of the table, procedure, function etc. in which they are used. So any sequence can be associated with any number of Oracle objects.

But if a sequence is used to generate a primary key value for a table, it is advised that the sequence is only associated with that table, so that there are no gaps between the sequence values inserted into the table(unless the records are deleted from the table).

A create sequence syntax looks as follows
 

CREATE SEQUENCE schema_name.seq_name
START WITH n
INCREMENT BY n
MAXVALUE n | NOMAXVALUE
MINVALUE n | NOMINVALUE
CYCLE | NOCYCLE
CACHE | NOCAHE
ORDER | NOORDER;


The above statements tell about the complete create sequence syntax.

Note :

1. All that are mentioned in red are optional attributes,  Yes, all of them are option, as all of them are given a default value so if you dont give any explicit value for the attribute, it considers the default value.
2. All that are underlined are default attributes.
3. NO specific order is required while mentioning the create sequence statement, you can have increment by follows with minvalue and start with at the end. 

Let's understand each and every attribute

START WITH(DEFAULT 1):

 

  • Start with clause specifies from where the sequence has to be started when the sequence is called for the first time. 
  • This start value once set for a sequence cannot be modified even with an alter sequence statement. 
  • The starting value should be less than maxvalue attribute.

INCREMENT BY(DEFAULT 1):

  • Increment by clause specifies the interval between sequence numbers.
  • It can be a positive or a negative number, but cannot be 0.
  • If positive the sequence will be an ascending sequence, if negative the sequence will be a decreasing sequence.

MAXVALUE:

  • This number specifies the maximum number that the sequence can generate.
  • Its value should be greater than or equal to the value specified in START WITH attribute.
  • Its value should be greater than to the value specified in MINVALUE attribute.
  • If a sequence tried to generate a number greater than the number specified as maxvalue, oracle would raise the error as follows: ORA-08004: sequence seq_name.NEXTVAL exceeds MAXVALUE and cannot be instantiated.

NOMAXVALUE (DEFAULT):

  • It's like saying that this sequence has no maximum value limit.
  • But internally oracle sets the limit as 10^27 for ascending sequences, and -1 for descending sequences.
  • Note: Technically if you are using a sequence for inserting data in a column of a table, then the size of the column will be the maxvalue of the sequence. For eg. If the column is the emp_id number(6), then you will not be able to store more than 999999 in the column, so indirectly the size of the column will act as the maxvalue for the sequences.

MINVALUE:

  • This number specifies the minimum number that the sequence can generate.
  • Its value should be less than or equal to the value specified in START WITH attribute.
  • Its value should be less than to the value specified in MAXVALUE attribute.


NOMINVALUE (DEFAULT):

  • It means that the sequence is not bounded by any minimum or lower value.
  • But internally oracle sets the minvalue as  10^-26 for decreasing sequence, and 1 for ascending sequence.

CYCLE:

  • It specifies that the sequence continues to generate number once the sequence reaches it maximum or minimum value.
  • For ascending sequence, when the sequence reaches its maximum value it again starts from the minimum value.
  • For a descending sequence, when the sequence reaches its minimum value it again starts from the maximum value.

NOCYCLE (DEFAULT):

  • The sequence cannot generate more values once it has reached it maxvalue or minvalue.

CACHE:

  • Used for faster access of the sequence value.
  • The cache number determines how many values of the sequence is pre-allocated and kept in memory.
  • 28 or fewer digits are allowed, if mentioning a minimum of 2 numbers should be mentioned
  • Your cannot cache more numbers than the given cycle, for example if a sequence starts with 100 has an increment by 1 and has a maxvalue of 105, then it makes no sense to cache 20 values, as the sequence itself contains 6 values form 100 to 105.
  • So for guidance, oracle has provided this simple formula, and the cache number should be less then the number given by the formula. ( CEIL(MAXVALUE - MINVALUE) ) / ABS(INCR).

NOCACHE:

  • Mention the option of nocache, when you want database to not preallocate any values foe the sequence.
  • NOTE : That if you don't mentioned either CACHE or NOCACHE, database will cache 20 values of the sequence. The same can be queried by checking the following query after creating the sequences.
  • select cache_size, last_number from user_sequences.

ORDER :

  • Guarantees that the sequence numbers are generated in order.
  • Useful when you are using sequence number as timestamps.
  • Needed only in RAC applications.
  • In exclusive mode, the sequences are always generated in order.

NOORDER (DEFAULT) :

  • Specify when you don't want to guarantee that the sequence number are generated in order.

 

The below is an example to create a sequence which starts with 100 and increments by 10 :
 

CREATE SEQUENCE DEMO_SEQ
START WITH 100
INCREMENT BY 10;


sequence created.

Now to execute or increment the above sequence we will use the nextval pseudo-column as follows :
 

SELECT  DEMO_SEQ.NEXTVAL
FROM DUAL;

-- Output

100

 

On executing the sequence again the sequence value will be incremented, as follows:

SELECT  DEMO_SEQ.NEXTVAL
FROM DUAL;

-- output

110
ON next execution

SELECT  DEMO_SEQ.NEXTVAL
FROM DUAL;

-- Output

120

So till the time, we learned how to increment the sequence, now what to do if we want to know the current value of sequence, not increment it. Just querying the current value. To do the same use SEQ_NAME.CURRVAL as follows :

 

SELECT DEMO_SEQ.CURRVAL.

FROM DUAL;

-- Output:

CURRVAL

120


To know more on sequences check the above video on YouTube.