Purpose
This statement is used to change the increment, minimum value, and maximum value, cache count, and behavior of an existing sequence. The corresponding operations only affect subsequent sequence numbers.
Note
You can only modify sequences in the current schema or have the ALTER privilege on the sequence.
Syntax
ALTER SEQUENCE [schema.]sequence_name
{
RESTART [START WITH integer]
| MINVALUE integer | NOMINVALUE
| MAXVALUE integer | NOMAXVALUE
| INCREMENT BY integer
| CACHE integer | NOCACHE
| ORDER | NOORDER
| CYCLE | NOCYCLE
}
Syntax
| Field | Description |
|---|---|
| schema. | Specifies the schema that contains the sequence. If you omit the schema, the database will change the sequence in the current schema. |
| sequence_name | The name of the sequence to be modified. |
| RESTART | Resets the sequence value. When ascending, it starts from MINVALUE; when descending, it starts from MAXVALUE. You can also use START WITH int_value to start from int_value. |
| MINVALUE int_value | Changes the minimum value of the sequence. The value of int_value ranges from [-9223372036854775808,9223372036854775807]. |
| NOMINVALUE | If changed to NOMINVALUE, the minimum value is 1 when ascending and -9223372036854775808 when descending. |
| MAXVALUE int_value | Changes the maximum value of the sequence. The value of int_value ranges from [-9223372036854775808,9223372036854775807]. The new MAXVALUE cannot be less than the current sequence number. Notice
|
| NOMAXVALUE | If changed to NOMAXVALUE, the maximum value is 9223372036854775807 when ascending and -1 when descending. |
| INCREMENT BY int_value | Changes the increment step of the sequence. int_value cannot be 0. If specified as a positive number, the sequence is ascending; if specified as a negative number, the sequence is descending. The default value is 1 if not specified. |
| CACHE int_value | Changes the number of increment values preallocated in memory. The default value of int_value is 20. CACHE int_value must be greater than 1. If CACHE INT_VALUE is 1, it is equivalent to NOCACHE. |
| NOCACHE | Changes the sequence value to not be preallocated. If both CACHE and NOCACHE are omitted, the database defaults to caching 20 |
| ORDER | Changes the sequence value to be generated in order. |
| NOORDER | If changed to NOORDER, the sequence value is not guaranteed to be generated in order. |
| CYCLE | Changes the sequence value to be generated in a cycle. That is, the sequence continues to generate values after reaching its maximum or minimum value. When ascending, it generates the minimum value after reaching the maximum value. When descending, it generates the maximum value after reaching the minimum value. Notice
|
| NOCYCLE | NOCYCLE is the default value. If changed to NOCYCLE, the sequence cannot generate more values after reaching its maximum or minimum value. |
Examples
Modify the maximum value of the increment column
seq1and specify that the increment values are generated in a cycle.obclient> CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1 MAXVALUE 2048 CYCLE; obclient> ALTER SEQUENCE seq1 MAXVALUE 4096 CYCLE;Change the maximum value and increment step of the sequence
seq2, enableCYCLEandORDER, and specifyNOCACHEto indicate that the sequence values are not preallocated.obclient> CREATE SEQUENCE seq2 MINVALUE 1 MAXVALUE 1000 START WITH 1 INCREMENT BY 1 CACHE 20; obclient> ALTER SEQUENCE seq2 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER;Reset the sequence value and start from 3.
obclient> ALTER SEQUENCE seq2 RESTART; obclient> ALTER SEQUENCE seq2 RESTART START WITH 3;The position of
RESTARTcan be anywhere in the options. The following two SQL statements that reset the sequence value are equivalent.obclient> ALTER SEQUENCE seq2 START WITH 3 RESTART MINVALUE -100 CYCLE; obclient> ALTER SEQUENCE seq2 RESTART START WITH 3 MINVALUE -100 CYCLE;