Why do We Create Sequences in Oracle?

Sequences in Oracle

Oracle database systems use the concept of sequences to generate distinct sequential integer values, which serves as a technique for autonumbering. In order to automatically generate numerical IDs, usually for primary key or unique key columns in tables, sequences are a form of database object that can be accessed by numerous users.

Data Definition Language (DDL) commands like CREATE SEQUENCE and DROP SEQUENCE are used in the management of these objects.

Sequences Concept and Usage

Sequences produce distinct integers without regard to whether a transaction is committed or rolled back. Because of this independence, there may be gaps in the sequence numbers that each user obtains if two users increase the same sequence at the same time. Additionally, a particular sequence number is skipped and won’t be used again if it is generated and used in a transaction that later rolls back.

Sequence gaps can arise because any unused cached values are lost in the event of a system failure if sequence values are cached in memory, which is advised for performance, particularly in Real Application Clusters (RAC) environments. This “mostly gap-free” unique number generator is suitable for the majority of common applications. Although this frequently negatively affects performance, developers who insist on ensuring gap-free sequences should completely forgo Oracle sequence objects and use their own intricate sequence generator instead.

Accessing Sequence Values

Following the creation of a sequence, several pseudocolumns in SQL statements are used to access its values:

NEXTVAL: Increments the sequence and returns the next available unique value. The first reference to NEXTVAL returns the initial value defined by the START WITH parameter.
CURRVAL: Returns the current value of the sequence. It always reflects the value returned by the last reference to NEXTVAL within the current session.

Crucially, before a user can refer to CURRVAL, they must first initialise the sequence within their session by referring to NEXTVAL.

Creating a Sequence

The build SEQUENCE system privilege in your own schema or the CREATE ANY SEQUENCE system privilege in another user’s schema are required in order to build a sequence.

Several crucial attributes can be controlled with the CREATE SEQUENCE statement:

INCREMENT BY: Specifies the interval between sequence numbers. This must be a non-zero integer.
START WITH: Specifies the first number generated.
MAXVALUE / NOMAXVALUE: Sets the upper limit the sequence can reach. NOMAXVALUE is the default for ascending sequences, setting the maximum value to $10^{27}$.
MINVALUE / NOMINVALUE: Sets the lowest limit.
CYCLE / NOCYCLE: If CYCLE is specified, the sequence restarts at the minimum value after reaching the maximum (or vice versa for descending sequences). NOCYCLE is the default.
CACHE / NOCACHE: Determines how many sequence values are preallocated and stored in memory for performance. The default is CACHE 20 if both are omitted. NOCACHE means no values are preallocated.
ORDER / NOORDER: Guarantees that sequence numbers are generated in the order of request, which is often important when using sequences as timestamps in a distributed environment like RAC. NOORDER is the default.

Code Example 1: Creating a Sequence

The following statement creates a sequence named customers_seq in the sample schema, intended for providing customer ID numbers:

CREATE SEQUENCE customers_seq
START WITH     1000
INCREMENT BY   1
NOCACHE
NOCYCLE;

Output:

Sequence created.

Customers_seq.NEXTVAL will return 1000 the first time it is referenced. 1001 is returned by the second reference, and so forth.

Code Example 2: Using Sequence Pseudocolumns

Sequence values are typically used in INSERT or UPDATE statements. The DUAL table an internal Oracle table useful for performing functions that return a single row can also be used to quickly retrieve the next value:

First, retrieve the next available sequence number:

SQL> select customers_seq.nextval from dual;

NEXTVAL
----------
1000

Next, use the generated value during an insertion into a table (using pseudocode for demonstration purposes):

/* Inserting sequence values into a table */
INSERT INTO employees (employee_id, first_name, last_name, job_id, department_id)
VALUES (customers_seq.nextval, 'John', 'Doe', 'PU_CLERK', 30);

To reuse that same sequence number (e.g., for related inserts into a detail table), CURRVAL is utilized, as NEXTVAL is only used once to increment the sequence:

/* Reusing the current value of a sequence */
INSERT INTO employee_details (order_id, detail_line, item_name)
VALUES (customers_seq.currval, 1, 'Detail Item A');

Autoincrementing Columns

Beginning with Oracle Database 12c, identification columns which essentially offer built-in autonumbering can be added to tables. These columns will automatically fill in using an internally maintained sequence. By eliminating the requirement for developers to use NEXTVAL in their INSERT statements, this method streamlines DML.

The table formation DDL allows for the definition of the underlying sequence parameters (START WITH, INCREMENT BY).

Code Example 3: Creating a Table with an Identity Column

This example specifies that the underlying sequence starts at 50 and increments by 2:

create table inv(
inv_id number generated as identity (start with 50 increment by 2)
,inv_desc varchar2(30 char));

Output (after creation and insertion):

SQL> insert into inv (inv_desc) values( 'Book'); 
SQL> insert into inv (inv_desc) values( 'Pen'); 

SQL> select * from inv;
    INV_ID INV_DESC
---------- ------------------------------
        50 Book
        52 Pen

When records were added, the sequence values (50 and 52) were automatically filled in without a value for inv_id.

Dropping and Managing Sequences

Dropping a Sequence

The sequence object can be permanently deleted from the database using the DROP SEQUENCE query. All related grants on the item are also dropped when a sequence is dropped. Grants must be renewed to users who need access if the sequence needs to be replicated later.

Code Example 4: Dropping a Sequence

SQL> drop sequence customers_seq;

Output:

Sequence dropped.

Resetting a Sequence

Oracle documentation typically recommends discarding and recreating a sequence if you want to restart it at a different value. But since dropping requires regranting permissions, DBAs choose to avoid this. Using the ALTER SEQUENCE statement with a big INCREMENT BY value is a standard administrative method for resetting a sequence without dropping it.

For example, to set the next value of a sequence called myseq 1000 integers higher than its current value:

SQL> alter sequence myseq increment by 1000;
SQL> select myseq.nextval from dual; -- Increments by 1000
SQL> alter sequence myseq increment by 1;

Likewise, a huge negative INCREMENT BY value is used to reset the sequence to a much lower number. The RENAME statement can be used to rename a series.

Page Content

Tutorials