Sequences

Sequences

A sequence is a schema object from which multiple users can generate unique integers. A sequence generator provides a highly scalable and well-performing method to generate surrogate keys for a number data type.

Sequence Characteristics

A sequence definition indicates general information, such as the following:
■The name of the sequence
■Whether the sequence ascends or descends
■The interval between numbers
■Whether the database should cache sets of generated sequence numbers in memory
■Whether the sequence should cycle when a limit is reached

The following example creates the sequence customers_seq in the sample schema oe. An application could use this sequence to provide customer ID numbers when rows are added to the customers table.

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

The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference returns a value 1 greater than the previous reference.

Concurrent Access to Sequences

The same sequence generator can generate numbers for multiple tables. In this way, the database can generate primary keys automatically and coordinate keys across multiple rows or tables. For example, a sequence can generate primary keys for an orders table and a customers table.
The sequence generator is useful in multiuser environments for generating unique numbers without the overhead of disk I/O or transaction locking. For example, two users simultaneously insert new rows into the orders table. By using a sequence to generate unique numbers for the order_id column, neither user has to wait for the other to enter the next available order number. The sequence automatically generates the correct values for each user.
Each user that references a sequence has access to his or her current sequence number, which is the last sequence generated in the session. A user can issue a statement to generate a new sequence number or use the current number last generated by the session. After a statement in a session generates a sequence number, it is available only to this session. Individual sequence numbers can be skipped if they were generated and used in a transaction that was ultimately rolled back.

Caution:
If your application requires a gap-free set of numbers, then you cannot use Oracle sequences. You must serialize activities in the database using your own developed code.