The SEQUENCE operator calculates the value of the SEQUENCE pseudo column.
A SEQUENCE pseudo-column is created by using the CREATE SEQUENCE statement. The calculation is performed once for each row output from subsequent operators.
Example: Calculating the current and next values of the SEQUENCE pseudo column.
obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.09 sec)
obclient>CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected (0.09 sec)
obclient>CREATE SEQUENCE seq INCREMENT BY 1 START WITH 1;
Query OK, 0 rows affected (0.09 sec)
obclient>EXPLAIN SELECT seq.NEXTVAL, seq.CURRVAL FROM t1\G;
*************************** 1. row ***************************
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, the function of SEQUENCE, the No. 0 operator in the execution plan display, is to calculate the value of the sequence. The output([SEQ.NEXTVAL],[SEQ.CURRVAL] clause specifies the current and next values of the SEQUENCE to be calculated. The SEQUENCE operator calculates the sequence value once 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.
Field |
Description |
|---|---|
| output | The output columns of the operator. Parameters of the SEQUENCE operator: * CURRVAL: calculates the current value of the sequence. * NEXTVAL: calculates the next value of the sequence. |
| filter | The filter conditions of the operator. In this example, the condition is set to nil because no filter is configured for the SEQUENCE operator. |