Oracle7 Server SQL Reference

CREATE SEQUENCE

Purpose

To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

Prerequisites

To create a sequence in your own schema, you must have CREATE SEQUENCE privilege.

To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege. If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the owner of the schema to contain the sequence.

Syntax

Keywords and Parameters

schema

is the schema to contain the sequence. If you omit schema, Oracle7 creates the sequence in your own schema.

sequence

is the name of the sequence to be created.

INCREMENT BY

specifies 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 less digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.

MINVALUE

specifies the sequence's minimum value. This integer value can have 28 or less digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

NOMINVALUE

specifies a minimum value of 1 for an ascending sequence or -(10²⁶) for a descending sequence.

The default is NOMINVALUE.

MAXVALUE

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

NOMAXVALUE

specifies a maximum value of 10²⁷ for an ascending sequence or -1 for a descending sequence.

The default is NOMAXVALUE.

START WITH

specifies the first sequence number to be generated. You can use this option 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 sequence's minimum value. For descending sequences, the default value is the sequence's maximum value. This integer value can have 28 or less digits.

CYCLE

specifies 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.

NOCYCLE

specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

The default is NOCYCLE.

CACHE

specifies how many values of the sequence Oracle7 pre-allocates and keeps in memory for faster access. This integer value can have 28 or less 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; thus, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE-MINVALUE)) / ABS(INCREMENT)

NOCACHE

specifies that values of the sequence are not pre-allocated.

If you omit both the CACHE parameter and the NOCACHE option, Oracle7 caches 20 sequence numbers by default. However, if you are using Oracle7 with the Parallel Server option in parallel mode and you specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option.

ORDER

guarantees that sequence numbers are generated in order of request. You may want to use this option if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

NOORDER

does not guarantee sequence numbers are generated in order of request.

If you omit both the ORDER and NOORDER options, Oracle7 chooses NOORDER by default. Note that the ORDER option is only necessary to guarantee ordered generation if you are using Oracle7 with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.

Usage Notes

If you are using Trusted Oracle7, the new sequence is automatically labeled with your DBMS label.

Using Sequences

You can use sequence numbers to automatically generate unique primary key values for your data, and you can also coordinate the keys across multiple rows or tables.

Values for a given sequence are automatically generated by special Oracle7 routines and, consequently, sequences avoid the performance bottleneck which results from implementation of sequences at the application level. For example, one common application-level implementation is to force each transaction to lock a sequence number table, increment the sequence, and then release the table. Under this implementation, only one sequence number may be generated at a time. In contrast, Oracle7 sequences permit the simultaneous generation of multiple sequence numbers while guaranteeing that every sequence number is unique.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Because sequence numbers are generated independently of tables, the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

Sequence Defaults

The sequence defaults are designed so that if you specify none of the clauses, you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.

Incrementing Sequence Values

You can create a sequence so that its values increment in one of following ways:

The sequence values increment without bound.

The sequence values increment to a predefined limit and then stop.

The sequence values increment to a predefined limit and then restart.

To create a sequence that increments without bound, omit the MAXVALUE parameter or specify the NOMAXVALUE option for ascending sequences or omit the MINVALUE parameter or specify the NOMINVALUE for descending sequences.

To create a sequence that stops at a predefined limit, specify a value for the MAXVALUE parameter for an ascending sequence or a value for the MINVALUE parameter for a descending sequence. Also specify the NOCYCLE option. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.

To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify the CYCLE option. If you do not specify MINVALUE, then it defaults to NOMINVALUE; that is, the value 1.

The value of the START WITH parameter establishes the initial value generated after the sequence is created. Note that this value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.

Caching Sequence Numbers

The number of values cached in memory for a sequence is specified by the value of the sequence's CACHE parameter. Cached sequences allow faster generation of sequence numbers. A cache for a given sequence is populated at the first request for a number from that sequence. The cache is repopulated every CACHE requests. If there is a system failure, all cached sequence values that have not been used in committed Data Manipulation Language statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

A CACHE of 20 future sequence numbers is the default.

Accessing and Incrementing Sequence Values

Once a sequence is created, you can access its values in SQL statements with the following pseudocolumns:

CURRVAL

returns the current value of the sequence.

NEXTVAL

increments the sequence and returns the new value.

For more information on using the above pseudocolumns, see the section "Pseudocolumns" beginning .

Example

The following statement creates the sequence ESEQ:

CREATE SEQUENCE eseq

	INCREMENT BY 10

The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.