AntDB-Oracle Compatibility Developer's Manual P3–11
Published: 2023-09-01 05:16:15

#### CREATE SEQUENCE

**Name**

CREATE INDEX - define a new sequence generator.

**Summary**

CREATE SEQUENCE name \[ INCREMENT BY increment ]

\[ { NOMINVALUE \| MINVALUE minvalue } ]

\[ { NOMAXVALUE \| MAXVALUE maxvalue } ]

\[ START WITH start ] \[ CACHE cache \| NOCACHE ] \[ CYCLE \| NOCYCLE ]

**Description**

The CREATE SEQUENCE command is used to create a new sequence number generator. This operation involves the creation and initialization of a table with a single record with the name specified by the parameter name. The user running this command is the owner of the newly created sequence generator.

If the schema name is specified, then the sequence is created in the specified schema, otherwise it is created in the current schema. The name of the sequence cannot be the same as any other sequence, table, index or view in the same schema.

After the sequence is created, the sequence is manipulated using the functions NEXTVAL and CURRVAL. The usage of these functions is described in detail in Section 2.5.6.

**argument**

name

The name of the sequence to be created (which can be referenced in a schema-qualified manner).

increment

We can use the optional INCREMENT BY increment clause to add the current sequence value to the specified increment value, which will produce a new value. A positive increment will produce an ascending sequence and a negative increment will produce a descending sequence of values. The default value of this parameter is 1.

NOMINVALUE \| MINVALUE minvalue

The optional clause MINVALUE minvalue determines the minimum value that a sequence can produce. If this clause is not used, the default value will be used. The default value is 1 for ascending order and -2 \^ 63 for descending order. Note that you can use the keyword NOMINVALUE to set the minimum value that a sequence can produce back to the default value.

NOMAXVALUE \| MAXVALUE maxvalue

The optional clause MAXVALUE maxvalue determines the maximum value that can be generated by a sequence. If this clause is not used, the default value will be used. The default value is 2 \^ 63 -1 for ascending sequences and -1 for descending sequences. Note that you can use the keyword NOMAXVALUE to set the maximum value that a sequence can produce back to the default value.

start

The optional clause START WITH start allows the sequence to start from any position. The default value is minvalue for ascending sequences and maxvalue for descending sequences. The specified start value must be greater than or equal to minvalue.

cache

The optional clause CACHE cache specifies how many sequences are pre-allocated and stored in memory for fast access. The minimum value of this parameter is 1 (only one value is generated at a time, e.g., NOCHACHE), and this is also the default value.

CYCLE \| NOCYCLE

The CYCLE option allows the sequence to return to the initial position when the ascending and descending sequences reach their respective maximum and minimum values. If the limit is reached, then the next generated values are the respective minimum and maximum values.

If NOCYCLE is used or not written (the default is NOCYCLE), any call to NEXTVAL will return an error when the sequence reaches its maximum value.

**Note**

The sequence is based on the large integer algorithm, so the values cannot exceed 8-byte length integers (from -9223372036854775808 to 9223372036854775807 ). In some older versions of platforms, there may be no compiler support for 8-byte-length integers, in which case the sequence can only use the regular integer algorithm (ranging from -2147483648 to +2147483647).

When multiple sessions concurrently access a sequence object with a cache setting greater than 1, undesired results may be achieved. This is because each session allocates and caches consecutive sequence values during the access to the sequence object, and therefore increases the latest value of the sequence object. Then, the next cache-1 call of the NEXTVAL function in the session simply returns the pre-assigned values without actually contacting the sequence object. Thus, when the session ends, any values that have been allocated but not used in the session will be lost. This creates a vulnerability in the sequence.

In addition, although it is guaranteed that multiple sessions will be assigned their own different sequence values, the resulting associated values will be out of sequence range when all sessions are considered. For example, when the cache is set to 10, Session A could keep values 1...10 and return NEXTVAL=1, then Session B could keep values 11...20 and return NEXTVAL=11 before Session A produces NEXTVAL=2. So it is safe to set the cache setting to 1 to ensure that the NEXTVAL values are generated sequentially: a cache setting greater than 1 only ensures that the NEXTVAL values are all different, not that they are generated sequentially. And the last value just reflects the value retained by any session, whether it has been returned by NEXTVAL or not.

**Example**

Create an ascending sequence with the name serial, starting with 101.

```
CREATE SEQUENCE serial START WITH 101; 
```

Select the next value from this sequence.

```
SELECT serial.NEXTVAL FROM DUAL;

SERIAL.NEXTVAL  
-----------------
             101
(1 row)
```

Create a sequence with the name supplier\_seq, using the NOCACHE option.

```
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
```

Select the next value from this sequence:

```
SELECT supplier_seq.NEXTVAL FROM DUAL;

SUPPLIER_SEQ.NEXTVAL  
---------------------------------------
                     1
(1 row)
```