SQL$HELP_OLD72.HLB  —  ALTER  SEQUENCE
    Alters a sequence. A sequence is a database object from which
    multiple users can generate unique integers. You can use
    sequences to automatically generate primary key values.

1  –  Environment

    You can use the ALTER SEQUENCE statement:

    o  In interactive SQL

    o  Embedded in host language programs to be precompiled

    o  As part of a procedure in an SQL module

    o  In dynamic SQL as a statement to be dynamically executed

2  –  Format

  ALTER SEQUENCE <sequence-name> -++--> <sequence-attributes>  --------++->
                                  |+--> RENAME TO <new-sequence-name> -+|
                                  +--------------<----------------------+

  sequence-attributes =

   --+--> INCREMENT BY <numeric-value> ---------+->
     +--> sequence-range -----------------------+
     +--> CYCLE --------------------------------+
     +--> NOCYCLE ------------------------------+
     +--> CACHE <numeric-value> ----------------+
     +--> NOCACHE ------------------------------+
     +--> ORDER --------------------------------+
     +--> NOORDER ------------------------------+
     +--> RANDOMIZE ----------------------------+
     +--> NORANDOMIZE --------------------------+
     +--> RESTART WITH -------------------------+
     +--> WAIT ---------------------------------+
     +--> NOWAIT -------------------------------+
     +--> DEFAULT WAIT -------------------------+
     +--> COMMENT -+-------+--+-> '<string>' -+-+
                   +-> IS -+  +------ / <-----+

  sequence-range =

  -+-> MINVALUE -+-+--> <numeric-value> -+--+-->
   +-> MAXVALUE -+ +--> TINYINT      ----+  |
   |               +--> SMALLINT      ---+  |
   |               +--> INTEGER      ----+  |
   |               +--> BIGINT      -----+  |
   +-> NOMINVALUE --------------------------+
   +-> NOMAXVALUE --------------------------+

3  –  Arguments

3.1  –  CACHE numeric-value

 Syntax options:

    CACHE numeric-value| NOCACHE

    The CACHE clause specifies how many values of the sequence Oracle
    Rdb should preallocate and keep in memory for faster access.
    The mumeric value must be a value between 2 and 2147483647.
    You cannot cache more values than will fit in a given cycle of
    sequence numbers; thus, the maximum value allowed for the CACHE
    clause must be less than the value resulting from the following
    formula:

    (MAXVALUE-MINVALUE)/ABS(INCREMENT)

    You can alter the CACHE value if it is currently a value of 2
    or higher. When you alter the CACHE value, existing users of the
    sequence continue to use the original setting. You can use the
    SET FLAGS 'SEQ_CACHE' statement to adjust the cache size for a
    single process. See the See the SET_FLAGS statement for details.

    If NOCACHE is currently enabled or the CACHE value is 1, you can
    alter the CACHE value, but may have to wait until other users
    of the sequence have released locks on it. (Note that CACHE 1 is
    equivalent to NOCACHE.)

    A cache for a given sequence is populated at the first request
    for a number from that sequence, and whenever a value is
    requested when the cache is empty. If a system failure occurs,
    or when the cache is released any unfetched values will be
    discarded. The maximum number of lost values is equal to the
    current cache size. This may be the value specified by CACHE or
    by the SET FLAGS SEQ_CACHE option.

    The NOCACHE clause specifies that values will be allocated one
    at a time. This will require more I/O to the Rdb root file than
    using a CACHE value.

    Note that even after you alter the CACHE value, users who
    were using the sequence at the time you altered the CACHE will
    continue to use the original setting.

3.2  –  COMMENT_IS

    Adds a comment about the sequence. SQL displays the text of the
    comment when it executes a SHOW SEQUENCE statement. Enclose the
    comment in single quotation marks ( ') and separate multiple
    lines in a comment with a slash mark (/).

3.3  –  CYCLE

    Syntax options:

    CYCLE | NOCYCLE

    The CYCLE clause specifies that the sequence is to continue
    generating values after reaching either the MINVALUE or MAXVALUE.
    After an ascending sequence reaches the MAXVALUE, the sequence
    starts again from its MINVALUE. After a descending sequence
    reaches its MINVALUE, the sequence starts again at its MAXVALUE.
    The NOCYCLE clause specifies that the sequence should not
    continue generating values after reaching either its minimum
    or maximum value. An error is generated if an attempt is made to
    increment the sequence beyond its limits.

    Note that even after you alter the CYCLE clause, those who
    were using the sequence at the time you altered the CYCLE will
    continue to use the original setting.

3.4  –  INCREMENT BY numeric-value

    Specifies the size of the increment and the direction (ascending
    or descending) of the sequence. This numeric value must be in the
    range -2147483648 through 2147483647, excluding 0. The absolute
    value must be less than the difference between MAXVALUE and
    MINVALUE. A negative value specifies a descending sequence; a
    positive value specifies an ascending sequence. If the existing
    value is positive, then the new value must also be positive.
    Likewise, if the existing value is negative, then the new value
    must also be negative. That is, you cannot change a sequence from
    ascending to descending or from descending to ascending.

3.5  –  MAXVALUE

    Syntax options:

    MAXVALUE numeric-value | NOMAXVALUE

    The MAXVALUE clause specifies the maximum BIGINT value that the
    sequence can generate. For an ascending sequence, the new maximum
    value must be greater than or equal to the existing RDB$NEXT_
    SEQUENCE_VALUE. For a descending sequence, the new maximum
    value must be greater than or equal to the existing MAXVALUE.
    This ensures that the MAXVALUE is not less than any currently
    issued values. In addition, the numeric value must be between
    -9223372036854775808 and 9223372036854775808. The MAXVALUE must
    be greater than the value specified with the MINVALUE clause.
    The NOMAXVALUE clause specifies that the maximum value for an
    ascending sequence is 9223372036854775808 (minus the cache size),
    and -1 for a descending sequence. The NOMAXVALUE clause is the
    default.

3.6  –  MAXVALUE integer-option

    Syntax options:

       MAXVALUE TINYINT
       MAXVALUE SMALLINT
       MAXVALUE INTEGER
       MAXVALUE BIGINT

    SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
    to follow MAXVALUE instead of a numeric value. This allows easy
    range setting for sequences used with these data types. The value
    supplied will be the largest positive value that can be assigned
    to this data type.

3.7  –  MINVALUE

    Syntax options:

    MINVALUE numeric-value | NOMINVALUE

    The MINVALUE clause specifies the minimum signed quadword
    (BIGINT) value that the sequence can generate. For an ascending
    sequence, the new minimum value must be less than or equal
    to the existing MINVALUE. For a descending sequence, the
    new minimum value must be less than or equal to the existing
    RDB$NEXT_SEQUENCE_VALUE. This prevents the minimum value
    from being greater than any currently issued values. In
    addition, the numeric value must be equal to or greater than -
    9223372036854775808. The MINVALUE must be less than the value
    specified with the MAXVALUE clause. The NOMINVALUE clause
    specifies that the minimum value for an ascending sequence is 1,
    and -9223372036854775808 (plus the cache size) for a descending
    sequence.

    The NOMINVALUE clause is the default.

3.8  –  MINVALUE integer-option

    Syntax options:

       MINVALUE TINYINT
       MINVALUE SMALLINT
       MINVALUE INTEGER
       MINVALUE BIGINT

    SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
    to follow MINVALUE instead of a numeric value. This allows easy
    range setting for sequences used with these data types. The value
    supplied will be the smallest negative value that can be assigned
    to this data type.

3.9  –  ORDER

    Syntax options:

    ORDER | NOORDER

    The ORDER clause specifies that sequence numbers are guaranteed
    to be assigned in order for each requesting process, thus
    maintaining a strict history of requests. The NOORDER clause
    specifies that sequence numbers are not guaranteed to be
    generated in order of request.

3.10  –  RANDOMIZE

    Syntax options:

    RANDOMIZE | NORANDOMIZE

    The RANDOMIZE clause specifies that the sequence numbers are to
    be returned with a random value in the most significant bytes
    of the BIGINT value. This allows unique values to be generated
    that have a random distribution. When you specify the NORANDOMIZE
    clause, sequence numbers are close in value to others created at
    the same time.

    The advantage of the RANDOMIZE clause is that updates to columns
    of a stored index to which these values are written occur in
    different locations in the index structure and so may improve
    concurrent access for large indexes as leaf nodes in different
    parts of the index can be updated independently. In contrast,
    the sequence numbers generated when you specify the NORANDOMIZE
    clause are likely to be close in numeric value to other sequence
    values generated at the same time. This may cause index updates
    to occur in the same or nearby index nodes, which may lead to
    contention in one part of the sorted index.

    The full range of values in the BIGINT value returned for the
    sequence are used; therefore, the NOMAXVALUE and NOMINVALUE
    clauses must be specified (or defaulted to) for the sequence
    definition. The most significant bits of the BIGINT value
    are set to a randomly generated positive value. A generated
    distinct value is returned in the least significant 32 bits so
    that uniqueness is guaranteed. If you also specify the CYCLE
    clause, then only the least significant 32 bits are cycled. When
    a query is performed on the column RDB$NEXT_SEQUENCE_VALUE in
    the RDB$SEQUENCES table, only the generated value of the least
    significant bits is returned, because the most significant bits
    are not assigned until the NEXTVAL pseudocolumn is referenced.

    If you specify RANDOMIZE, you cannot also specify ORDER,
    MAXVALUE, or MINVALUE.

3.11  –  RENAME_TO

    Changes the name of the sequence being altered. See the RENAME
    statement for further discussion. If the new name is the name of
    a synonym then an error will be raised.

    The new name must not exist as the name of an existing sequence,
    synonym, table or view. You may not rename a system sequence.

    The RENAME TO clause requires synonyms be enabled for this
    database. Refer to the ALTER DATABASE SYNONYMS ARE ENABLED
    clause. Note that these synonyms may be deleted if they are no
    longer used by database definitions or applications.

3.12  –  RESTART_WITH

    The RESTART WITH clause allows the database administrator to
    reset the sequence to a specified value. The value must be
    within the range of MINVALUE and MAXVALUE. This command requires
    exclusive access to the sequence. Once the ALTER SEQUENCE
    statement is successfully committed, applications that use the
    sequence will start with a value based on the restarted value.

                                   NOTE

       The TRUNCATE TABLE statement issued for a table with
       an IDENTITY column implicitly executes an ALTER
       SEQUENCE...RESTART WITH process on the sequence, applying
       the MINVALUE if it is an ascending sequence, or MAXVALUE if
       it is a descending sequence.

3.13  –  sequence-name

    The name of the sequence whose definition you want to change.

3.14  –  WAIT

    Syntax options:

    WAIT | NOWAIT | DEFAULT WAIT

    Specifies which wait state is used when a reference to NEXTVAL
    is used. A reference to NEXTVAL for a sequence may require
    synchronization with other users of the sequence. When you
    specify DEFAULT WAIT the wait state (WAIT or NOWAIT) of the
    current transaction is used. This may mean that no waiting is
    performed during a NOWAIT transaction.

    If you specify WAIT (the default) for the sequence, then
    regardless of the wait state set for the current transaction, all
    synchronization waits for the next value. This is the recommended
    setting if the application uses NOWAIT transactions. The current
    WAIT timeout interval defined for the transaction or database is
    used.

    If you specify NOWAIT for the sequence, then regardless of the
    current transaction setting, all synchronization will not wait
    for the next value.

    Note that even after you alter the WAIT value, users who were
    using the sequence at the time you altered WAIT will continue to
    use the original setting.

4  –  Examples

    Example 1: Altering a sequence

    SQL> -- Show current sequence definition:
    SQL> --
    SQL> SHOW SEQUENCE EMPIDS
         EMPIDS
     Sequence Id: 1
     Initial Value: 1
     Minimum Value: 1
     Maximum Value: 9223372036854775787
     Next Sequence Value: 1
     Increment by: 1
     Cache Size: 20
     No Order
     No Cycle
     No Randomize
    SQL> --
    SQL> -- Alter the sequence.
    SQL> --
    SQL> ALTER SEQUENCE EMPIDS
    cont> MINVALUE 0
    cont> MAXVALUE 2000
    cont> CACHE 30
    cont> ORDER
    cont> CYCLE;
    SQL> --
    SQL> -- Show new definition.
    SQL> --
    SQL> SHOW SEQUENCE EMPIDS
         EMPIDS
     Sequence Id: 1
     Initial Value: 1
     Minimum Value: (none)
     Maximum Value: 2000
     Next Sequence Value: 1
     Increment by: 1
     Cache Size: 30
     Order
     Cycle
     No Randomize

    Example 2: Reset the sequence to a specified value

    SQL> show sequence NEW_EMPLOYEE_ID
         NEW_EMPLOYEE_ID
     Sequence Id: 1
     Initial Value: 472
       .
       .
       .
    SQL>
    SQL> alter sequence NEW_EMPLOYEE_ID
    cont> restart with 500;
    SQL>
    SQL> show sequence NEW_EMPLOYEE_ID
         NEW_EMPLOYEE_ID
     Sequence Id: 1
     Initial Value: 500
       .
       .
       .
    SQL>
Close Help