SEQUENCE

The SEQUENCE operator calculates the value of the SEQUENCE pseudo column.

You can use the CREATE SEQUENCE statement to create the SEQUENCE pseudo column. The SEQUENCE operator calculates the value of the SEQUENCE pseudo column for each row of data output by subsequent operators.

Example: Calculate the current and next values of the SEQUENCE pseudo column.

obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected

obclient>CREATE SEQUENCE seq INCREMENT BY 1 START WITH 1;
Query OK, 0 rows affected

obclient> EXPLAIN SELECT seq.NEXTVAL, seq.CURRVAL FROM t1;

Query Plan:
|=====================================
|ID|OPERATOR   |NAME|EST. ROWS|COST |
-------------------------------------
|0 |SEQUENCE   |    |100000   |77868|
|1 | TABLE SCAN|T1  |100000   |64066|
=====================================
Outputs & filters:
-------------------------------------
  0 - output([SEQ.NEXTVAL], [SEQ.CURRVAL]), filter(nil)
  1 - output([T1.__pk_increment]), filter(nil),
      access([T1.__pk_increment]), partitions(p0)

In the preceding example, Operator 0 SEQUENCE in the execution plan calculates the value of the sequence. The output([SEQ.NEXTVAL],[SEQ.CURRVAL] clause specifies to calculate the current and next values of the SEQUENCE pseudo column. The SEQUENCE operator calculates the sequence value for each row of data output from table t1. In the execution plan display, the Outputs & filters section shows in detail the output information of the SEQUENCE operator.