Oracle Sequence: What is Oracle Sequence in Database?

A sequence is a database object that generates numbers in sequential order. Applications most often use these numbers when they require a unique value in a table such as primary key values. Some database management systems use an “auto number” concept or “auto increment” setting on numeric column types. Both the auto numbering columns and sequences provide a unique number in sequence used for a unique identifier.

The quickest way to retrieve the data from a table is to have a column in the table whose data uniquely identifies a row. By using this column and a specific value, in the where condition of a select statement the oracle engine will be able to identify and retrieve the row fast.

To achieve this, a constraint is attached to a specific column in the table that ensures that the column is never left blank and the data in the column are unique. Since data entry is done by human being so it is quite likely to enter duplicate values.

If the value to be entered is machine generated it will always fulfill the constraints and the row will always be accepted for storage. So sequences plays important role for generating unique values.

Features of sequences

The following list describes the characteristics of sequences:

• Sequences are available to all users of the database.

• Sequences are created using SQL statements.

• Sequences have a minimum and maximum value (the defaults are minimum=0 and maximum=263-1); they can be dropped, but not reset.

• Once a sequence returns a value, the sequence can never return that same value.

• While sequence values are not tied to any particular table, a sequence is usually used to generate values for only one table.

• Sequences increment by an amount specified when created (the default is 1).

Syntax:

CREATE SEQUENCE<sequence_name> 
[START WITH star_num] 
[INCREMENT BY increment_num] 
[{MAXVALUE maximum_num I NOMAXVALUE}] 
[{MINVALUE minimum_num I NOMINVALUE}] 
[{CYCLE I NOCYCLE}] 
[{CACHE cache_num I NO CACHE}] 
[{ORDER I NOORDER}]; 

Keywords and Parameters

Start With: It specifies the start value for the sequence. The default START WITH for an ascending sequence is the sequence minimum value (1) and for descending sequence, it is the maximum value (-1).

Increment By: It specifies the value how the sequence increments each iteration. By default a sequence generator increments by 1. It can be any positive or negative value but not zero.

Minvalue: This is the minimum value that the sequence will generate. The value specified in MINVALUE must be greater than or equal to the START WITH value. NOMINVALUE is the default value that is equal to 1 for an ascending sequence and – 10²⁶ for descending sequence.

Maxvalue: It specify value the bounds of the sequence generator. It specifies the highest value that it can generate. NOMAXVALUE is the default value that is equal to 10²⁷ and -1 for descending sequence.

Cycle: Specify CYCLE to indicate that when the maximum value is reached the sequence starts over again at the start value. Specify NOCYCLE to generate an error upon reaching the maximum value.

Cache: It specify how many values of a sequence Oracle pre-allocates and keeps in memory for faster access. The minimum value for this parameter is two.

Nocache: It specifies that values of a sequence are not pre-allocated.

Order: It guarantees that sequence numbers are generated in the order of request.

Noorder: This does not guarantee sequence numbers are assigned in order of request. Notice that several parameters can be specified to control the function of sequences. You can use these parameters to indicate whether the sequence is ascending or descending. the starting point of the sequence, the minimum and maximum values, and the interval between sequence values. The NOCYCLE option indicates that the sequence cannot generate more values after reaching its maximum or minimum value.

The CACHE clause pre-allocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, the database reads another set of numbers into the cache.

Creating Sequence

To create a sequence you must have the Create Sequence System Privilege. The minimum information required for generating numbers using a sequence is:

• The starting number.

• The maximum number that can be generated by a sequence.

• The increment value for generating the next number.

Example:

SQL > CREATE SEQUENCE ANU 
START WITH 1 
INCREMENT BY 1 
MAXVALUE 100; 
Sequence Created. 

On execution, Oracle will create a sequence ‘ANU’. Its start with value is 1, incrementing the sequence number by 1. Maximum value that it can generate is 100.

Referencing a Sequence

A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudo columns;

NEXTVAL: Each new sequence number is generated by a reference to the sequence pseudo column. For example for references it should be used as ‘seq_name.NEXTVAL‘.

CURRVAL: The current sequence number can be repeatedly referenced using the pseudo column. For example for references it should be used as ‘seq_name.CURRVAL‘.

NEXTVAL and CURRYAL are not reserved words or keywords and can be used as pseudo column names in SQL statements such as SELECT, INSERT, or UPDATE.

Generating Sequence Numbers With Nextval

Sequences are used in the Oracle because NEXTVAL is used to generate unique number. To generate and use a sequence number, reference seq_name.NEXTVAL. We can use it with the Insert statement or, the sequence number can be referenced in the SET clause of an UPDATE statement.

Assume the table of ‘EMPLOYEES’ where we want to add the EID in the sequence starting from 1 to 100. Let’s see what the Structure of the table is

SQL> DESCRIBE EMPLOYEES;
 The sequence number can be referenced in a values list. For example:
SQL> INSERT INTO EMPLOYEES 
values (ANU. NEXTVAL, ‘VINOD' , 15000, 9815618378 , 'New City, Jal. CITY. ) ; 

In the Example ‘ANU’ is the Sequence number and ‘NEXTVAL’ is used to generate a unique sequence number. As defined, the first reference to ANU.NEXTVAL returns the value 1. Each subsequent statement that references ANU.NEXTVAL generates the next sequence number (2, 3, 4, … ). The pseudo-column NEXTVAL can be used to generate as many new sequence numbers as necessary. However, only a single sequence number can be generated for each row. In other words, if NEXTVAL is referenced more than once in a single statement, then the first reference generates the next number, and all subsequent references in the statement return the same number.

• Use the Select statement to view the sequence numbers.

SQL> Select * from Employees;
Using Sequence Numbers with Currval

To use or refer to the current sequence value of your session, reference seq_name.CURRVAL. CURRVAL can only be used if seq_name.NEXTVAL has been referenced in the current user session (in the current or a previous transaction). CURRVAL can be referenced as many times as necessary, including multiple times within the same statement.

The next sequence number is not generated until NEXTVAL is referenced.

• Continuing with the previous example, you would finish placing the 3rd Sequence number in the table.

SQL > INSERT INTO EMPLOYEESvalues (ANU.NEXTVAL, 'PUNARDEEP' , 25000, 98156123456 , 'New City', 'Jalandhar City'. ); 

Use the select statement to view the effect of ‘CURRVAL’.

Uses and Restrictions of Nextval and Currval

CURRYAL and NEXTVAL can be used in the following places:

• VALUES clause of INSERT statements.
• The SELECT list of a SELECT statement.
• The SET clause of an UPDATE statement.
• CURRVAL and NEXTVAL cannot be used in these places:
• In a subquery
• In a view query or materialized view query.
• In a SELECT statement with the DISTINCT operator.
• In a SELECT statement with a GROUP BY or ORDER BY clause.
• In a SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator.
• In the WHERE clause of a SELECT statement.
• In DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement.
• In the condition of a CHECK constraint.

Altering Sequences

To alter a sequence, your schema must contain the sequence, or you must have the ALTER ANY SEQUENCE system privilege. You can alter a sequence to change any of the parameters that define how it generates sequence numbers except the sequence starting number. Some important restrictions on Altering the Sequence:

• To change the starting point of a sequence, drop the sequence and then re-create it.

• New MAXVAL should not be less than starting value.

Syntax:

ALTER SEQUENCE<sequence_name> 
[START WITH star_num] 
[INCREMENT BY increment_num] 
[{MAXVALUE maximum_num I NOMAXVALUE}] 
[{MINVALUE minimum_num I NOMINVALUE}] 
[{CYCLE I NOCYCLE}] 
[{CACHE cache_num I NOCACHE}] 
[{ORDER I NOORDER}]; 
Dropping Sequences 

You can drop any sequence in your schema. To drop a sequence in another schema, you must have the DROP ANY SEQUENCE system privilege. If a sequence is no longer required, you can drop the sequence using the DROP SEQUENCE statement.

Syntax:

DROP SEQUENCE [SEQUENCE_NAME];
 
Example:

SQL> DROP SEQUENCE ANU; 
Sequence dropped. 

Viewing Sequences

You can view the sequences generated by the Oracle users. You just need to use a simple command on the SQL prompt.

Syntax:

SELECT * FROM USER_SEQUENCES; 

A sequence is a database object that generates numbers in sequential order. Applications most often use these numbers when they require a unique value in a table such as primary key values. Some database management systems use an “auto number” concept or “auto increment” setting on numeric column types. Both the auto numbering columns and sequences provide a unique number in sequence used for a unique identifier.

The quickest way to retrieve the data from a table is to have a column in the table whose data uniquely identifies a row. By using this column and a specific value, in the where condition of a select statement the oracle engine will be able to identify and retrieve the row fast.

To achieve this, a constraint is attached to a specific column in the table that ensures that the column is never left blank and the data in the column are unique. Since data entry is done by human being so it is quite likely to enter duplicate values.

If the value to be entered is machine generated it will always fulfill the constraints and the row will always be accepted for storage. So sequences plays important role for generating unique values.