TechnicalBits

What is Sequence?

• Sequences are used to generate unique, sequential integer values that are used as primary key values in database tables.
• Speeds up the efficiency of accessing sequence values when cached in memory.
• The sequence of numbers can be generated in either ascending or descending order.  

Create SEQUENCE Statement
Syntax : 

CREATE SEQUENCE sequence_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];

START WITH  
Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.

INCREMENT BY 
Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits.
If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

MAXVALUE 
Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.

NOMAXVALUE  
Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.

MINVALUE 
Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

NOMINVALUE  
Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. 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.

NOCYCLE  
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

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)
If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Note:
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.

NOCACHE  
Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default. 

Creating a Sequence

• Create a sequence named ORDER_NUMBER_SEQUENCE to be used for the primary key of the DEPT table.
• Do not use the CYCLE option.

CREATE SEQUENCE ORDER_NUMBER_SEQUENCE INCREMENT BY 1 START WITH 1 MAXVALUE 100 NOCACHE NOCYCLE;/*Sequence created.*/

Confirming Sequences

• Verify the sequence values by querying the USER_SEQUENCES system view with a SELECT command. 
• This view is part of the database's data dictionary.

SELECT sequence_name, min_value, max_value,increment_by, last_number FROM user_sequences; 

The LAST_NUMBER column displays the next available sequence number.

Modifying a Sequence
Can alter the increment value, maximum value, minimum value, cycle option, or cache option by the Alter command.

ALTER SEQUENCE order_number_sequence INCREMENT BY 1 MAXVALUE 999999 NOCACHE NOCYCLE;   /*Sequence altered.*/

Guidelines for Modifying a Sequence

• You must be the owner or have the ALTER privilege for the sequence.
• Only future sequence numbers are affected.
• The sequence must be dropped and re-created to restart the sequence at a different number.
• Some validation is performed.

Removing a Sequence

• Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.
• Once removed, the sequence can no longer be referenced.

Accessing Sequence Values 
Example : 

CREATE TABLE sales_order (order_number NUMBER(9),     order_amount NUMBER(9),     constraint pk_sales_order PRIMARY KEY (order_number);
/* Table created.*/

The INSERT commands shown below insert three rows into the SALES_ORDER table. The INSERT commands reference the ORDER_NUMBER_SEQUENCE.NEXTVAL pseudo column.

INSERT INTO sales_order values (order_number_sequence.nextval, 156 );
INSERT INTO sales_order values (order_number_sequence.nextval,450 );
INSERT INTO sales_order values (order_number_sequence.nextval, 17);

Example : 

SELECT * from sales_order 

Output : 

ORDER_NUMBER ORDER_AMOUNT
-------------------------------------------------------------
1 156
2 450
3 17

NEXTVAL and CURRVAL Pseudo columns

• NEXTVAL returns the next available sequence value.
• It returns a unique value every time it is referenced, even for different users.
• CURRVAL obtains the current sequence value.
• NEXTVAL must be issued for that sequence before CURRVAL contains a value.
• The CURRVAL pseudo column returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.  

Using a Sequence

• Caching sequence values in memory allows faster access to those values.
• Gaps in sequence values can occur when:
• A rollback occurs
• The system crashes
• A sequence is used in another table.
• View the next available sequence, if it was created with NOCACHE, by querying the USER_SEQUENCES table.