Purpose
This statement is used to modify the increment, minimum value, maximum value, cache size, and behavior of an existing sequence. The corresponding operations only affect subsequent sequence numbers.
Note
You can only operate on sequences within your own schema, or if you have the ALTER object privilege on that sequence.
Syntax
ALTER SEQUENCE [ schema. ] sequence_name
[ INCREMENT BY int_value ]
[ START WITH int_value ]
[ MINVALUE int_value | NOMINVALUE ]
[ MAXVALUE int_value | NOMAXVALUE ]
[ CACHE int_value | NOCACHE ]
[ ORDER | NOORDER ]
[ CYCLE | NOCYCLE ]
[ RESTART ]
Parameters
field |
Description |
|---|---|
| schema. | Specifies the schema that contains the sequence. If omitted, the database changes the sequence in the schema where the user is currently located. |
| sequence_name | The name of the sequence to be changed. |
| RESTART | Resets the sequence value. For ascending order, starts from MINVALUE Resumption of value retrieval; for descending order, from MAXVALUE Resumes value retrieval. |
| START WITH int_value | Changes the starting value of a sequence. The value range of int_value is [-(1027-1) ~ (1028-1)]. |
| MINVALUE int_value | Change the minimum value of the sequence. The value range of int_value is [-(10^{27}-1) ~ (10^{27}-1)]. |
| NOMINVALUE | If changed to NOMINVALUE, the minimum value is 1 in ascending order and -(1027-1) in descending order. |
| MAXVALUE int_value | Change the maximum value of the sequence. The value range of int_value is [(-10^{27}+1) ~ (10^{28}-1)]. The newly specified MAXVALUE must not be less than the current sequence number. Notice When both MINVALUE and MAXVALUE are specified, MINVALUE must be less than MAXVALUE. The difference between MAXVALUE and MINVALUE must be greater than or equal to the int_value in INCREMENT BY. |
| NOMAXVALUE | If changed to NOMAXVALUE Then, the maximum value in ascending order is (1028-1), and the maximum value in descending order is -1. |
| INCREMENT BY int_value | Changes the increment step of a sequence. int_value Cannot be 0. If a positive value is specified, the sequence is ascending; if a negative value is specified, the sequence is descending. The default value is 1 if not specified. |
| CACHE int_value | Change the number of auto-increment values pre-allocated in memory. int_value defaults to 20. The value of CACHE int_value must be greater than 1. If the value of CACHE INT_VALUE is 1, it is equivalent to NOCACHE. |
| NOCACHE | The value of the sequence is changed to an unspecified, unallocated value. If both CACHE and NOCACHE are omitted, the database caches 20 serial numbers by default. |
| ORDER | The values of the sequence are generated sequentially when you change them. |
| NOORDER | If changed to NOORDER, the sequence values are not guaranteed to be generated in order. |
| CYCLE | The sequence value is changed to enable cyclic generation. That is, the sequence continues to generate values after reaching its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates the minimum value. After a descending sequence reaches its minimum value, it generates its maximum value. Notice
|
| NOCYCLE | The default value is NOCYCLE. If changed to NOCYCLE, the specified sequence cannot generate more values after reaching its maximum or minimum value. |
Examples
Modify the maximum value of sequence
seq1and specify a cyclic auto-increment value.-- You must create a sequence first: CREATE SEQUENCE seq1; obclient> ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE;Change the maximum value and increment step of sequence
seq3, enableCYCLEandORDER, and specifyNOCACHEto indicate that the sequence values are not pre-allocated.-- You must create a sequence first: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER;Reset the sequence value.
-- You must create a sequence first: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 RESTART;Set the sequence start value and reset the sequence.
-- You must create a sequence first: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART;The
RESTARTclause can be placed anywhere in the options. The following two SQL statements for resetting sequence values are equivalent.-- You must create a sequence first: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART MINVALUE -100 CYCLE; obclient> ALTER SEQUENCE seq3 RESTART START WITH 3 MINVALUE -100 CYCLE;