Sequences|V4.2.0| docs|Distributed Database

Sequences

Last Updated：2023-10-31 11:17:11 Updated

A sequence is a database object of Oracle tenants. You can use it to generate unique sequential values for the primary key column. This topic describes the basic features of sequences and the differences between sequences in OceanBase Database and Oracle Database.

Basic features

A sequence has two pseudocolumns: CURRVAL and NEXTVAL, which respectively return the current value and next value of a sequence. Each reference to NEXTVAL will update the value of CURRVAL.

You can specify the name, interval, and order of the sequence in the definition.

You can execute the CREATE SEQUENCE statement to create a sequence. The following syntax can be used:

CREATE SEQUENCE sequence_name
[MINVALUE value | NOMINVALUE]
[MAXVALUE value | NOMAXVALUE]
[START WITH value]
[INCREMENT BY value]
[CACHE value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];

Note

The MINVALUE and MAXVALUE parameters specify the minimum and maximum numbers in the sequence.
START WITH specifies the first sequence number to be generated.
INCREMENT BY specifies the interval between sequence numbers. The interval can be negative. The default value is 1.
CACHE specifies the number of sequence values to be cached for faster access. We recommend that you specify this parameter in high concurrency scenarios.
CYCLE specifies that the sequence continues to generate values after reaching its maximum or minimum value. For a cycle sequence, you must specify a minimum and a maximum value.

You can view the sequences that you have created in the USER_SEQUENCES, ALL_SEQUENCES, and DBA_SEQUENCES views.

You can execute the ALTER SEQUENCE statement to modify the properties of a sequence except for the starting value. The properties that you can modify include the minimum value, maximum value, interval, and cycle. The syntax is as follows:

ALTER SEQUENCE sequence_name
[MINVALUE value | NOMINVALUE]
[MAXVALUE value | NOMAXVALUE]
[INCREMENT BY value]
[CACHE value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];

You can execute the DROP SEQUENCE statement to drop a sequence. The following syntax can be used:

DROP SEQUENCE sequence_name;

Sequence numbers are generated independently of tables. Therefore, the same sequence can be used for one or multiple tables. In the following example, the first INSERT statement inserts the value 1 into the t1 table and the second INSERT statement inserts the value 2 into the t2 table.

CREATE SEQUENCE s1 MINVALUE 1 MAXVALUE 100 INCREMENT BY 1;
INSERT INTO t1 (id) VALUES (s1.nextval);
INSERT INTO t2 (id) VALUES (s1.nextval);

Considerations

CACHE and ORDER

The following table describes the differences between sequences in OceanBase Database and Oracle Database in terms of the CACHE and ORDER options.

Mode	Oracle Database	OceanBase Database
NOCACHE with ORDER	The sequence numbers are not cached by database instances. When multiple requests to generate sequence numbers are received, the system reads the sequence numbers from the global `cache` and returns the sequence numbers based on the order of the requests.	The sequence numbers are not cached by database instances. When multiple requests to generate sequence numbers are received, the system reads the sequence numbers from the global `cache` and returns the sequence numbers based on the order of the requests.
NOCACHE with NOORDER	The sequence numbers are not cached by database instances. When requests to generate sequence numbers are received, the system reads the sequence numbers from the global `cache` and may postpone the processing of some requests when it is busy. This may cause the sequence numbers to be returned without following the order of the requests.	Only the syntax of this combination is supported. This combination achieves the same result as that of the `NOCACHE with ORDER` combination.
CACHE with ORDER	Each database instance keeps the same number of preallocated sequence numbers in the cache. The system uses a global lock to synchronize the next available position in the sequence before it generates a new sequence number.	Only the syntax of this combination is supported. This combination achieves the same result as that of the `NOCACHE with ORDER` combination.
CACHE with NOORDER	Each database instance keeps different numbers of preallocated sequence numbers in the cache. Global synchronization of the `cache` status is not required for sequence number generation. In this case, the sequence numbers are generated without following the order.	Each database instance keeps different numbers of preallocated sequence numbers in the cache. Global synchronization of the `cache` status is not required for sequence number generation. In this case, the sequence numbers are generated without following the order.

Note

To achieve the best performance, we recommend that you use the default CACHE with NOORDER combination in OceanBase Database.
Do not use the NOCACHE with ORDER combination, because it provides the worst performance. If you use this combination, the SELECT and UPDATE statements are executed every time the NEXTVAL pseudocolumn is called. This affects the performance of the database.
By default, the database caches only 20 sequence numbers. When you create a sequence, we recommend that you specify a larger value. For a database instance with the capability of handling 100 transactions per second (TPS), you can set the CACHE SIZE parameter to 360000.

CURRVAL

The value returned by the NEXTVAL pseudocolumn cannot be obtained by the CURRVAL pseudocolumn on another node. In some cases, a proxy may forward queries for the NEXTVAL and CURRVAL pseudocolumns to different nodes. To prevent this issue, you can query the NEXTVAL and CURRVAL pseudocolumns in the same transaction. This ensures that the proxy always forwards the NEXTVAL and CURRVAL retrieval queries to the same node.