Purpose
This statement is used to change the increment, minimum and maximum values, cache count, and behavior of an existing sequence. The corresponding operations only affect subsequent sequence numbers.
Note
You can modify a sequence only if it is in the current schema or you have the ALTER privilege on the sequence.
Note
After creating a sequence, if you modify the INCREMENT BY clause before retrieving the first sequence value (NEXTVAL), the value retrieval behavior of OceanBase Database differs from that of Oracle:
- Oracle: The first value after modification is
start_val - old_inc_val + new_inc_val. - OceanBase Database: The first value after modification is still the value specified by
START WITH(i.e.,start_val).
If the sequence has already been used to retrieve values, modifying INCREMENT BY results in the same value retrieval behavior for both: the next value is the previous value plus the new increment (i.e., last_val + new_inc_val).
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 description
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 restarts from MINVALUE; when descending, it restarts from MAXVALUE. You can also use START WITH int_value to restart 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 caches 20 sequence numbers by default. |
| 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 sequence
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;