Using Global Sequences in INSERT or REPLACE Statements

You can use global sequences in INSERT or REPLACE statements to provide unique global sequence across schemas in a DDM instance. Generating sequence numbers with NEXTVAL and CURRVAL is supported in INSERT or REPLACE statements. NEXTVAL returns the next sequence number, and CURRVAL returns the current sequence number, for example, schema.seq.nextval and schema.seq.currval. If no schema is specified, use the global sequence of the currently connected schema.

Concurrently executing schema.seq.nextval in multiple sessions is supported to obtain unique global sequence numbers.

Example

There are two schemas dml_test_1 and dml_test_2, and both of them have table test_seq.

Table Definition

CREATE TABLE test_seq(col1 BIGINT,col2 BIGINT) DBPARTITION BY HASH(col1)

Procedure

Log in to the required DDM instance using a client.
Open the required schema.
Run the following command to create a global sequence for a schema:
use dml_test_1;

create sequence seq_test;
Run the following statement to use the global sequence in an INSERT or REPLACE statement:
- use dml_test_1;
insert into test_seq(col1,col2)values(seq_test.nextval,seq_test.currval);
- use dml_test_2;
insert into test_seq(col1,col2)values(dml_test_1.seq_test.nextval,dml_test_1.seq_test.currval);

The global sequence is created in schema dml_test_1. To use the global sequence in schema dml_test_2, you need to specify a schema name, for example, dml_test_1.seq_test.nextval or dml_test_1.seq_test.currval.
- Using global sequences in INSERT and REPLACE statements is supported only in sharded tables, but not in broadcast or unsharded tables.
- NEXTVAL and CURRVAL are executed from left to right in INSERT and REPLACE statements. If NEXTVAL is referenced more than once in a single statement, the sequence number is incremented for each reference.
- Each global sequence belongs to a schema. When you delete a schema, the global sequence of the schema is also deleted.