SQL$HELP_OLD72.HLB  —  CREATE  SEQUENCE  Arguments

1  –  CACHE

    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
    numeric value must be 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)

    The SET FLAGS option SEQ_CACHE can be used to override the
    setting of CACHE at runtime. See the SET_FLAGS statement for
    more details.

    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.

    By default, Oracle Rdb caches 20 sequence values.

2  –  COMMENT IS 'string '

    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  –  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. The NOCYCLE clause is
    the default.

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 of MAXVALUE and MINVALUE.
    A negative value specifies a descending sequence; a positive
    value specifies an ascending sequence. By default, the numeric
    value is 1.

5  –  MAXVALUE

    Syntax options:

    MAXVALUE numeric-value | NOMAXVALUE

    The MAXVALUE clause specifies the maximum signed quadword
    (BIGINT) value that the sequence can generate. The numeric value
    must be between -9223372036854775808 and 9223372036854775808. The
    MAXVALUE must be equal to or greater than the value specified for
    the START WITH clause and greater than the value specified with
    the MINVALUE clause. The NOMAXVALUE clause specifies that the
    maximum value for an ascending sequence is 9223372036854775808
    (plus the cache size) and -1 for a descending sequence.

    The NOMAXVALUE clause is the default.

6  –  MAXVALUE integer

    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.

7  –  MINVALUE

    Syntax options:

    MINVALUE numeric-value | NOMINVALUE

    The MINVALUE clause specifies the minimum signed quadword
    (BIGINT) value that the sequence can generate. The numeric
    value must be equal to or greater than -9223372036854775808. The
    MINVALUE must be less than or equal to the value specified with
    the START WITH clause and 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.

8  –  MINVALUE integer-value

    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.

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.

    The NOORDER clause is the default.

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 sorted index to which these values are written occur in
    different locations in the index structure. This 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 (which are likely to be close in numeric value to other
    sequences) result in index updates that 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. The NORANDOMIZE clause is the default.

11  –  sequence-name

    The name of the sequence that you want to create. Use a name that
    is unique among all sequence, synonym, table, and view names in
    the database, or in the schema if you are using a multischema
    database. Use any valid SQL name.

12  –  START WITH numeric-value

    Specifies the initial numeric value to be used for the sequence.
    This value must be in the range specified by (or defaulted to)
    the other sequence attribute clauses. Valid values are in the
    range -9223373036854775808 to 9223372036854775807.

    If omitted, the START WITH value defaults to the value of
    MINVALUE for ascending sequences and MAXVALUE for descending
    sequences.

13  –  STORED NAME IS stored-name

    Specifies a name that Oracle Rdb uses to access a sequence
    created in a multischema database. The stored name allows you to
    access multischema definitions using interfaces, such as Oracle
    RMU, that do not recognize multiple schemas in one database. You
    cannot specify a stored name for a sequence in a database that
    does not allow multiple schemas.

14  –  WAIT

    Syntax options:

    WAIT | NOWAIT | DEFAULT WAIT

    Specifies what 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 does not wait
    for the next value.
Close Help