Sequence Generator

The sequence generator is a service that generates a sequence of integer numbers. Every IDENTITY column you add to a table requires its own Sequence Generator (SG). The SG is responsible for several tasks, including obtaining and supplying values to the IDENTITY column as necessary.

Each IDENTITY column requires an associated, dedicated Sequence Generator (SG). When you add an IDENTITY column, the system creates an SG that's runs on the client with the application. Information about all attributes for every SG is added to a system table, SYS$SGAttributesTable. You can see the contents of this system table using a simple query such as this:

SELECT * FROM SYS$SGAttributesTable

For other commands:

Differences in Commands	Description
`SHOW TABLES`	Returns a list of tables
`DESCRIBE TABLE name1`	Shows the schema of table name1
`SELECT * FROM table_name`	Shows the data rows of table_name

Syntax

sequence_generator_attributes ::=
   (START WITH signed_int) | 
   (INCREMENT BY signed_int) | 
   (MAXVALUE signed_int) | (NO MAXVALUE) | 
   (MINVALUE signed_int) | (NO MINVALUE) | 
   (CACHE INT) | (NO CACHE) | 
   CYCLE | (NO CYCLE)

Semantics

Oracle NoSQL Database only supports sequence generators that are attached to identity columns. See Using the IDENTITY Column.The numbers in the generated sequence depend on the attributes of the sequence generator attributes.

Following are the SG attributes that you can optionally specify when you create an IDENTITY column, or change later using the ALTER TABLE statement.

Attribute	Type	Description
`START WITH`	Integer	The first value in the sequence. Zero (0) is permitted as a `Start With` value. Default value: `1`
`INCREMENT BY`	Integer	The next value in the sequence is generated by adding INCREMENT BY value to the current value of the sequence. The increment value can be a positive number or a negative number. Zero (0) is not permitted as an `Increment By` value. Specifying a negative number for `Increment By` decrements value from the current value of the sequence. Default value: `1`
`MINVALUE`	Integer	The lower bound of the IDENTITY values that the SG supplies. You can either specify `MINVALUE` or `NO MINVALUE`, but not both. Default value: `-2^31`, which is the minimum value of the INTEGER datatype.
`NO MINVALUE`	Integer	Specifies that there is no lower bound of the IDENTITY values that the SG supplies. SG uses the minimum value of the INTEGER datatype, which is `-2^31`, as the lower bound of the IDENTITY values. You can either specify `MINVALUE` or `NO MINVALUE`, but not both.
`MAXVALUE`	Integer	The upper bound of the IDENTITY values that the SG supplies. You can either specify `MAXVALUE` or `NO MAXVALUE`, but not both. Default value: `2^31-1`, which is the maximum value of the INTEGER datatype.
`NO MAXVALUE`	Integer	Specifies that there is no upper bound of the IDENTITY values that the SG supplies. SG uses the maximum value of the INTEGER data type, which is 2^31-1, as the upper bound of the IDENTITY values. You can either specify `MAXVALUE` or `NO MAXVALUE`, but not both.
`CACHE`	Integer	The value of this attribute specifies the count of sequence numbers that will be generated every time a request is made to the sequence generator. These requests originate at the Oracle NoSQL Database clients and are serviced by the sequence generator, which "lives" at the server. Specifically, the numbers generated in each request are sent back to the client and are cached there. Whenever a client needs to assign a value to an IDENTITY column, the next sequence number from the cache is consumed. When cache empties, a request for another batch of CACHE sequence numbers is sent to the sequence generator. If more than one client accesses a table with an IDENTITY column defined for unique values this way, each client is assigned contiguous value sets to its SG cache. These sets do not overlap with other client sets. For example, Client1 is assigned values `0001 – 1000`, while Client2 has `1001 – 2000`, and so on. Thus, as each client adds rows to the table, the IDENTITY values can run as 0001, 1001, 0002, 1002, 1003, and so on, as both clients use their own cache when adding rows. The IDENTITY column values are guaranteed to be unique, but not necessarily contiguous, because each client has its own set of cache values, and adds rows at different speeds and times. You can either specify `CACHE` or `NO CACHE`, but not both. Default value: `1000`
`NO CACHE`	Integer	Specifies that local cache is not being used by the SG. You can either specify `CACHE` or `NO CACHE`, but not both.
`CYCLE or NO CYCLE`	Boolean	Determines whether or not SG continues to generate values after reaching either the maximum or minimum value for the datatype of the IDENTITY column. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value. If you specify `CYCLE` attribute,the SG uses the total number of values that can be generated for an IDENTITY column of a specific datatype (INTEGER, LONG, or NUMBER), unless you specify MAXVALUE to set a different limit. Once the sequence generator reaches the end of the cycle and then recycles through the sequence generator to generate values that were generated in the previous cycle. If you specify `NO CYCLE` attribute, Oracle NoSQL Database guarantees that each IDENTITY column value is unique, but not necessarily sequential. For example, if you set MaxValue as `10000`, and multiple clients add rows to the table, each client is assigned a certain amount of values to use. Default Value: `NO CYCLE` Note: If you specify the `CYCLE` attribute, all of the existing values for the IDENTITY column are used again, potentially overwriting current values in place. Creating a column as GENERATED ALWAYS AS IDENTITY, and using the SG `NO CYCLE` attribute is the ONLY way to maintain unique IDENTITY column values.

Following are internal SG attributes. You cannot specify any of these when you create or add an IDENTITY column. Each is derived from how you create the IDENTITY field. For example, one internal attribute is SGName, which is the column name you give the IDENTITY field.

Attribute	Type	Description
`SGType`	String	[`INTERNAL` \| `EXTERNAL`]. The IDENTITY column you create, or add to a table with a DDL statement. The default is `INTERNAL`.
`SGName`	String	Name of the IDENTITY field you create and with which the SG is associated.
`Datatype`	String	Sequence Generator datatype that you specified as part of the `CREATE TABLE` statement for the IDENTITY column. Each IDENTITY column can be any numeric type: INTEGER, LONG, or NUMBER.
`SGAttrVersion`	Long	This is an internal attribute that you cannot set. It is here for future usage.