Managing Sequences

Without sequences, sequential values can only be produced programmatically. A new primary key value can be obtained by selecting the most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to wait for the next value of the primary key; this waiting is known as serialization. If you have such constructs in your applications, you should replace them with access to sequences. Sequences eliminate serialization and improve the concurrency of your application.

The following sections explain how to create, alter, use, and drop sequences using SQL commands. For instructions on tuning your sequences, see .

Creating Sequences

CREATE SEQUENCE emp_sequence
   INCREMENT BY 1
   START WITH 1
   NOMAXVALUE
   NOCYCLE
   CACHE 10;

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 option of the CREATE SEQUENCE command pre-allocates a set of sequence numbers and keeps them in memory so that they can be accessed faster. When the last of the sequence numbers in the cache have been used, another set of numbers is read into the cache.

For additional implications for caching sequence numbers when using the Oracle Parallel Server, see the Oracle7 Parallel Server Administrator's Guide. General information about caching sequence numbers is included .

Privileges Required to Create a Sequence

Altering Sequences

Use the SQL command ALTER SEQUENCE to alter a sequence, as in

ALTER SEQUENCE emp_sequence
   INCREMENT BY 10
   MAXVALUE 10000
   CYCLE
   CACHE 20;

Privileges Required to Alter a Sequence To alter a sequence, your schema must contain the sequence, or you must have the ALTER ANY SEQUENCE system privilege.

Using Sequences

The examples outlined in the following sections show how sequences can be used in master/detail table relationships. Assume an order entry system is partially comprised of two tables, ORDERS (master table) and LINE_ITEMS (detail table), that hold information about customer orders. A sequence named ORDER_SEQ is defined by the following statement:

CREATE SEQUENCE order_seq
   START WITH 1
   INCREMENT BY 1
   NOMAXVALUE
   NOCYCLE
   CACHE 20;

Referencing a Sequence

NEXTVAL and CURRVAL are not reserved words or keywords and can be used as pseudo-column names in SQL statements such as SELECTs, INSERTs, or UPDATEs.

Generating Sequence Numbers with NEXTVAL To generate and use a sequence number, reference seq_name.NEXTVAL. For example, assume a customer places an order. The sequence number can be referenced in a values list, as in

INSERT INTO orders (orderno, custno)
   VALUES (order_seq.NEXTVAL, 1032);

or in the SET clause of an UPDATE statement, as in

UPDATE orders
   SET orderno = order_seq.NEXTVAL
   WHERE orderno = 10112;

or the outermost SELECT of a query or subquery, as in

SELECT order_seq.NEXTVAL FROM dual;

As defined, the first reference to ORDER_SEQ.NEXTVAL returns the value 1. Each subsequent statement that references ORDER_SEQ.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 per row; that is, if NEXTVAL is referenced more than once in a single statement, the first reference generates the next number and all subsequent references in the statement return the same number.

Once a sequence number is generated, the sequence number is available only to the session that generated the number. Independent of transactions committing or rolling back, other users referencing ORDER_SEQ.NEXTVAL obtain unique values. If two users are accessing the same sequence concurrently, the sequence numbers each user receives might have gaps because sequence numbers are also being generated by the other user.

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 customer's order by inserting the line items for the order:

INSERT INTO line_items (orderno, partno, quantity)
   VALUES (order_seq.CURRVAL, 20321, 3);

INSERT INTO line_items (orderno, partno, quantity)
   VALUES (order_seq.CURRVAL, 29374, 1);

Assuming the INSERT statement given in the previous section generated a new sequence number of 347, both rows inserted by the statements in this section insert rows with order numbers of 347.

Uses and Restrictions of NEXTVAL and CURRVAL CURRVAL 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

• a subquery

• a view's query or snapshot's query

• a SELECT statement with the DISTINCT operator

• a SELECT statement with a GROUP BY or ORDER BY clause

• a SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

• the WHERE clause of a SELECT statement

• DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

• the condition of a CHECK constraint

Caching Sequence Numbers

The sequence cache consists of entries. Each entry can hold many sequence numbers for a single sequence.

Follow these guidelines for fast access to all sequence numbers:

• Be sure the sequence cache can hold all the sequences used concurrently by your applications.

• Increase the number of values for each sequence held in the sequence cache.

If your applications use many sequences concurrently, your sequence cache might not be large enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to all sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications.

The number of entries in the sequence cache is determined by the initialization parameter SEQUENCE_CACHE_ENTRIES. The default value for this parameter is 10 entries. Oracle creates and uses sequences internally for auditing, grants of system privileges, grants of object privileges, profiles, debugging stored procedures, and labels. Be sure your sequence cache has enough entries to hold these sequences as well as sequences used by your applications.

If the value for your SEQUENCE_CACHE_ENTRIES parameter is too low, it is possible to skip sequence values. For example, assume that this parameter is set to 4, and that you currently have four cached sequences. If you create a fifth sequence, it will replace the least recently used sequence in the cache. All of the remaining values in this displaced sequence are lost. That is, if the displaced sequence originally held 10 cached sequence values, and only one had been used, nine would be lost when the sequence was displaced.

The Number of Values in Each Sequence Cache Entry When a sequence is read into the sequence cache, sequence values are generated and stored in a cache entry. These values can then be accessed quickly. The number of sequence values stored in the cache is determined by the CACHE parameter in the CREATE SEQUENCE statement. The default value for this parameter is 20.

This CREATE SEQUENCE statement creates the SEQ2 sequence so that 50 values of the sequence are stored in the SEQUENCE cache:

CREATE SEQUENCE seq2
   CACHE 50

The first 50 values of SEQ2 can then be read from the cache. When the 51st value is accessed, the next 50 values will be read from disk.

Choosing a high value for CACHE allows you to access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.

If you use the NOCACHE option in the CREATE SEQUENCE statement, the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence. This CREATE SEQUENCE statement creates the SEQ3 sequence so that its values are never stored in the cache:

CREATE SEQUENCE seq3
   NOCACHE

Privileges Required to Use a Sequence

Dropping Sequences

DROP SEQUENCE order_seq;

When you drop a sequence, its definition is removed from the data dictionary. Any synonyms for the sequence remain, but return an error when referenced.

Privileges Required to Drop a Sequence You can drop any sequence in your schema. To drop a sequence in another schema, you must have the DROP ANY SEQUENCE system privilege.