Sequences|V4.3.0| docs|Distributed Database

Sequences

Last Updated：2024-04-19 08:42:50 Updated

In Oracle tenants of OceanBase Database, a sequence is a database object that can generate unique sequential values. These values are particularly useful for creating primary keys in tables that require unique values. This topic provides an overview of the basic features of sequences and highlights the differences between sequences in OceanBase Database and Oracle Database.

Note

Starting from V4.2.1, OceanBase Database supports the feature of accessing remote sequences. For more information, see Use a DBLink to access data in a remote database.

Basic features

Sequences provide two pseudocolumns, CURRVAL and NEXTVAL, which return the current sequence value and the next sequence value, respectively. Each time NEXTVAL is queried, the CURRVAL value is updated.

In sequence definitions, you can specify the sequence name, sequence interval, and whether the sequence is in ascending or descending order.

The CREATE SEQUENCE statement is used to create a sequence, and its syntax is as follows:

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 value and the maximum value of the sequence, respectively.
START WITH specifies the first sequence number to be generated.
INCREMENT BY specifies the interval between sequence numbers. The interval can be negative, and 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.

The ALTER SEQUENCE statement is used 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];

The DROP SEQUENCE statement is used to drop a sequence, and its syntax is as follows:

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 better performance, we recommend that you use the default CACHE with NOORDER mode in OceanBase Database.
In OceanBase Database, it is not advisable to use NOCACHE with ORDER. In this mode, each call to NEXTVAL will trigger an SELECT and UPDATE operation on the internal table, which can impact database performance.
By default, the CACHE value is only 20. Therefore, when you creatie a sequence, it is recommended to manually specify a larger value. For example, if the TPS of a single node is 100, you can set CACHE SIZE 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.