CREATE SEQUENCE

The CREATE SEQUENCE statement creates a new sequence number generator that can subsequently be used by multiple users to generate unique integers. Use the CREATE SEQUENCE statement to define the initial value of the sequence, define the increment value, the maximum or minimum value and determine if the sequence continues to generate numbers after the minimum or maximum is reached.

Required Privilege

CREATE SEQUENCE (if owner) or CREATE ANY SEQUENCE (if not owner).

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. The BATCH clause is supported in TimesTen Scaleout only.

SQL Syntax

CREATE SEQUENCE [Owner.]SequenceName
       [INCREMENT BY IncrementValue]
       [MINVALUE MinimumValue]
       [MAXVALUE MaximumValue]
       [CYCLE]
       [CACHE CacheValue]
       [START WITH StartValue]
       [BATCH BatchValue]

Parameters

Parameter Description

SEQUENCE [Owner.]SequenceName

Name of the sequence number generator.

INCREMENT BY IncrementValue

The incremental value between consecutive numbers. This value can be either a positive or negative integer. It cannot be 0. If the value is positive, it is an ascending sequence. If the value is negative, it is descending. The default value is 1. In a descending sequence, the range starts from MAXVALUE to MINVALUE, and vice versa for ascending sequence.

MINVALUE MinimumValue

Specifies the minimum value for the sequence. The default minimum value is 1.

MAXVALUE MaximumValue

The largest possible value for an ascending sequence, or the starting value for a descending sequence. The default maximum value is (263) -1, which is the maximum of BIGINT.

CYCLE

Indicates that the sequence number generator continues to generate numbers after it reaches the maximum or minimum value. By default, sequences do not cycle. Once the number reaches the maximum value in the ascending sequence, the sequence wraps around and generates numbers from its minimum value. For a descending sequence, when the minimum value is reached, the sequence number wraps around, beginning from the maximum value. If CYCLE is not specified, the sequence number generator stops generating numbers when the maximum/minimum is reached and TimesTen returns an error.

CACHE CacheValue

CACHE indicates the range of numbers that are cached each time. When a restart occurs, unused cached numbers are lost. If you specify a CacheValue of 1, then each use of the sequence results in an update to the database. Larger cache values result in fewer changes to the database and less overhead. The default is 20.

START WITH StartValue

Specifies the first sequence number to be generated. Use this clause to start an ascending sequence at a value that is greater than the minimum value or to start a descending sequence at a value less than the maximum. The StartValue must be greater or equal MinimumValue and StartValue must be less than or equal to MaximumValue.

BATCH BatchValue

Valid with TimesTen Scaleout only. Configures the range of unique sequence values that are stored at each element of the grid. The default value is 10 million.

Description

  • All parameters in the CREATE SEQUENCE statement must be integer values.

  • If you do not specify a value in the parameters, TimesTen defaults to an ascending sequence that starts with 1, increments by 1, has the default maximum value and does not cycle.

  • Do not create a sequence with the same name as a view or materialized view.

  • Sequences with the CYCLE attribute cannot be replicated (TimesTen Classic).

  • In TimesTen Classic, in which there is a replicated environment for an active standby pair, if DDL_REPLICATION_LEVEL is 3 or greater when you execute CREATE SEQUENCE on the active database, the sequence is replicated to all databases in the replication scheme. To include the sequence in the replication scheme, set DDL_REPLICATION_ACTION to INCLUDE. See Making DDL Changes in an Active Standby Pair in the Oracle TimesTen In-Memory Database Replication Guide for more information.

Usage with TimesTen Scaleout

  • The CREATE SEQUENCE statement creates a global object. Once you create the sequence, the sequence values are retrieved from any element of the database.

  • Sequence values are unique, but across elements the values might not be returned in monotonic order. Within a single element, sequence values are in monotonic order. But over time, across elements, sequence values are not returned monotonically. However, the monotonic property is guaranteed within an element.

  • The batch value is the range of unique sequence values stored in the element. Each element has its own batch. An element will get a new batch when its local batch is consumed. There is one element that owns the sequence and is responsible for allocating batch sequence blocks to other elements.

  • For the BATCH clause:

    • Use this clause to specify the range of sequence values that are stored on each element of the grid.

    • The default is 10 million.

    • BatchValue must be greater than or equal to CacheValue.

    • The maximum value for BatchValue is dependent on the maximum value of the signed integer for the platform.

  • Each element in a replica set has its own batch.

  • An element's batch sequence values are recoverable. Cache values are not recoverable.

See Using Sequences in Oracle TimesTen In-Memory Database Scaleout User's Guide for detailed information and examples.

Using CURRVAL and NEXTVAL in TimesTen Scaleout

To refer to the SEQUENCE values in a SQL statement, use CURRVAL and NEXTVAL.

  • CURRVAL returns the value of the last call to NEXTVAL if there is one in the current session, otherwise it returns an error.

  • NEXTVAL increments the current sequence value by the specified increment and returns the value for each row accessed.

If you execute a single SQL statement with multiple NEXTVAL references, TimesTen only increments the sequence once, returning the same value for all occurrences of NEXTVAL. If a SQL statement contains both NEXTVAL and CURRVAL, NEXTVAL is executed first. CURRVAL and NEXTVAL have the same value in that SQL statement.

NEXTVAL and CURRVAL can be used in the following.

  • The SelectList of a SELECT statement, but not the SelectList of a subquery

  • The SelectList of an INSERT...SELECT statement

  • The SET clause of an UPDATE statement

See Using Sequences in Oracle TimesTen In-Memory Database Scaleout User's Guide for information on the usage of CURRVAL and NEXTVAL in a grid and for examples.

Using CURRVAL and NEXTVAL in TimesTen Classic

To refer to the SEQUENCE values in a SQL statement, use CURRVAL and NEXTVAL.

  • CURRVAL returns the value of the last call to NEXTVAL if there is one in the current session, otherwise it returns an error.

  • NEXTVAL increments the current sequence value by the specified increment and returns the value for each row accessed.

The current value of a sequence is a connection-specific value. If there are two concurrent connections to the same database, each connection has its own CURRVAL of the same sequence set to its last NEXTVAL reference. When the maximum value is reached, SEQUENCE either wraps or issues an error statement, depending on the value of the CYCLE option of the CREATE SEQUENCE. In the case of recovery, sequences are not rolled back. It is possible that the range of values of a sequence can have gaps; however, each sequence value is still unique.

If you execute a single SQL statement with multiple NEXTVAL references, TimesTen only increments the sequence once, returning the same value for all occurrences of NEXTVAL. If a SQL statement contains both NEXTVAL and CURRVAL, NEXTVAL is executed first. CURRVAL and NEXTVAL have the same value in that SQL statement.

Note:

NEXTVAL cannot be used in a query on a standby node of an active standby pair.

NEXTVAL and CURRVAL can be used in the following.

  • The SelectList of a SELECT statement, but not the SelectList of a subquery

  • The SelectList of an INSERT...SELECT statement

  • The SET clause of an UPDATE statement

Examples: TimesTen Scaleout

For detailed examples, see Using Sequences in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Syntax example:

Command> CREATE SEQUENCE mysequence BATCH 100;
Command> describe mysequence;
 
Sequence SAMPLEUSER.MYSEQUENCE:
  Minimum Value: 1
  Maximum Value: 9223372036854775807
  Current Value: 1
  Increment:     1
  Cache:         20
  Cycle:         Off
  Batch:         100
 
1 sequence found.

Examples: TimesTen Classic

Create a sequence.

CREATE SEQUENCE mysequence INCREMENT BY 1 MINVALUE 2 
       MAXVALUE 1000;

This example assumes that tab1 has 1 row in the table and that CYCLE is used: 

CREATE SEQUENCE s1 MINVALUE 2 MAXVALUE 4 CYCLE;
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 3; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 4; */

After the maximum value is reached, the cycle starts from the minimum value for an ascending sequence.

SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */

To create a sequence and generate a sequence number:

CREATE SEQUENCE seq INCREMENT BY 1;
INSERT INTO student VALUES (seq.NEXTVAL, 'Sally');

To use a sequence in an UPDATE SET clause: 

UPDATE student SET studentno = seq.NEXTVAL WHERE name = 'Sally';

To use a sequence in a query:

SELECT seq.CURRVAL FROM student;

See Also