ALTER SEQUENCE

ALTER SEQUENCE

Syntax

alter_sequence ::=

sequence_options ::=

l

sync_table_clause ::=

Prerequisites

Only the SYS user, the owner of the schema to which the sequence belongs, users having the ALTER object privilege on the sequence, and users having the ALTER ANY SEQUENCE system privilege can execute this statement.

Description

After a sequence has been created using the CREATE SEQUENCE statement, this statement is used to change the definition of the sequence. For more detailed information, please refer to the description of the CREATE SEQUENCE statement.

user_name

This is used to specify the name of the owner of the sequence to be changed. If omitted, Altibase will assume that the sequence belongs to the schema of the user connected via the current session.

seq_name

This is used to specify the name of the sequence to be altered.

INCREMENT BY

This is used to specify the interval between sequence numbers.

MAXVALUE

This is used to specify the maximum value that the sequence can generate.

MINVALUE

This is used to specify the minimum value of the sequence.

CYCLE

This is used to allow a sequence to continue to output values after it reaches the limit specified by MAXVALUE or MINVALUE. In the case of an ascending sequence, the minimum value will be output once its maximum value has been reached, whereas the opposite is true for a descending sequence: once it reaches its minimum value, it outputs its maximum value.

CACHE

This is used to specify the number of sequence values that are cached in memory so that they can be accessed more quickly. The first time the sequence is referred to, the cache is populated, and whenever values are subsequently requested from the sequence, they are retrieved from the cached values. After the last sequence value in the cache has been used, the next request for a key value from the sequence causes new sequence values to be created and cached in memory. The number of sequence values that are created and cached at this time is set using the CREATE SEQUENCE statement. If this option is omitted, the default value is 20.

FLUSH CACHE

This flushes the sequence values cached in memory. If the value of a sequence is requested after flushing the cache using this option, new sequence values are cached in memory.

ENABLE SYNC TABLE

This creates a custom table for sequence replication for the purpose of replicating sequence numbers. The custom table for sequence replication is automatically granted the name, "[sequence name]$seq".

DISABLE SYNC TABLE

This drops the custom table for sequence replication used for the purpose of replicating a sequence.

Restriction

When changing the definition of an existing sequence, the START WITH clause cannot be used, because the sequence has already been created.

A custom table for sequence replication can be created only if the length of the sequence name is equal to or smaller than 36 bytes.

For detailed more information about sequences, please refer to the description of the CREATE SEQUENCE statement.

Examples

<Query> Change the sequence seq1 so that the minimum value is 0, the maximum value is 100, and increments by 1.

iSQL> ALTER SEQUENCE seq1
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 100;
Alter success.             

<Query> Change the minimum and maximum values of sequence seq2 to unlimited.

iSQL> ALTER SEQUENCE seq2
    NOMAXVALUE 
    NOMINVALUE;
Alter success.

<Query> Flush the sequence values cached in the sequence seq1.

iSQL> ALTER SEQUENCE seq1 FLUSH CACHE;
Alter success.

<Query> Create a custom table for sequence replication for the purpose of replicating the sequence seq1.

iSQL> ALTER SEQUENCE seq1 ENABLE SYNC TABLE;