Using Sequences

The CREATE SEQUENCE statement creates a new sequence number generator that can subsequently be used by multiple users to generate unique BIGINT data types. As with materialized views and tables, once you create the sequence object, sequence values can be retrieved from any element of the database.

The values are retrieved from the sequence in blocks and cached in order to reduce the overhead of performing a globally coordinated update on the sequence object every time a value is retrieved. While the values returned from a sequence in TimesTen Scaleout are guaranteed to be unique, they are not guaranteed to be sequential.

The BATCH clause is specific to TimesTen Scaleout. The batch value configures the range of unique sequence values stored in the element. Each element has its own batch. An element will get a new batch when its local batch is consumed. There is one element that owns the sequence and is responsible for allocating batch sequence blocks to other elements.

Sequence values are unique, but across elements the values might not be returned in monotonic order. Within a single element, sequence values are in monotonic order. But over time, across elements, sequence values are not returned monotonically. However, the monotonic property is guaranteed within an element.

If your application records events and tags each event with a sequence value, the application cannot assume that event 100, for example, happened after event 80. If your application needs to make this assumption, then set BATCH to 1. However, there is substantial communication overhead if you set BATCH to 1.

In summary, unless the BATCH value is set to 1, the order of sequence values is not guaranteed to be maintained across all elements. However, no matter what the batch value is, the uniqueness of the sequence value is guaranteed to be maintained across all elements. In addition, the order of sequence values is guaranteed to be maintained within an element.

You can change the default batch value of an existing sequence by issuing the ALTER SEQUENCE statement. The batch value is the only alterable clause. See CREATE SEQUENCE and ALTER SEQUENCE in Oracle TimesTen In-Memory Database SQL Reference for more information. Use the DROP SEQUENCE statement to drop a sequence. See DROP SEQUENCE in Oracle TimesTen In-Memory Database SQL Reference.

Understanding Batch Allocation

Deciding what to set for the batch value depends on these considerations:

If you set the value to 1, sequence values are issued in monotonic order, no matter how many elements exist. However, there is substantial communication overhead with a value of 1, which results in a detrimental impact on performance. Unless absolutely necessary, do not set the value to 1 as it will directly impact the performance of your system.
If you set the value greater than 1, unique sequence values are not issued in strict order across all elements. If your connection retrieves multiple values from a sequence, there is no guarantee that the values will be consecutive or contiguous. If multiple connections retrieve values from a sequence, there may be gaps in the range of values retrieved.
You should consider setting batch to a high value to avoid excessive communication among elements (unless it is necessary to set the batch value to 1 for the proper functioning of your application).
The unique sequence value within the batch boundary cannot be greater than MAXVALUE. For example, if a sequence increments by 1, has a batch value of 3, and a maximum value of 5, the first batch includes 1, 2, and 3. The second batch includes 4 and 5 only.
The batch value must be greater or equal to the cache value.
If you do not specify a batch value, the default is 10 million. Each element starts with its own set of 10 million values. If the 10 million values are used up, the element gets 10 million more. The minimum and maximum values and the number of unique values are determined by the MINVALUE, MAXVALUE, and INCREMENT BY values.
Each element in a replica set has different batches.

Examples of batch assignment:

Illustrate Batch Assignment for Three Elements

This example creates the myseq sequence with a batch value of 100. Then, from the connection that is connected to element 1, the example issues a SELECT...NEXTVAL query. The example then issues a second and third SELECT...NEXTVAL query from the connection that is connected to element 2 and the connection that is connected to element 3 respectively. The example illustrates the allocation of batch assignment for each element. In this example:

Element 1 receives a batch of 1-100.
Element 2 receives a batch of 101-200.
Element 3 receives a batch of 201-300.

From the connection that is connected to element 1 (demonstrated by SELECT elementId# FROM dual), create the myseq sequence specifying a batch value of 100. Then, issue a SELECT...NEXTVAL query. Observe the value 1 is returned.

Command> SELECT elementId# FROM dual;
< 1 >
1 row found.
Command> CREATE SEQUENCE myseq BATCH 100;
Command> SELECT myseq.NEXTVAL FROM dual;
< 1 >
1 row found.

From the connection that is connected to element 2, first verify the connection to element 2, then issue a SELECT...NEXTVAL query. Observe the value 101 is returned.

Command> SELECT elementId# FROM dual;
< 2 >
1 row found.
Command> SELECT myseq.NEXTVAL FROM dual;
< 101 >
1 row found.

From the connection that is connected to element 3, first verify the connection to element 3, then issue a SELECT...NEXTVAL query. Observe the value 201 is returned.

Command> SELECT elementId# FROM dual;
< 3 >
1 row found.
Command> SELECT myseq.NEXTVAL FROM dual;
< 201 >
1 row found.

Illustrate a Second Batch Assignment for Three Elements

This example creates the myseq2 sequence with a batch value of 100. Then, from the connection that is connected to element 1, the example issues a SELECT...NEXTVAL query. The example then issues a second and third SELECT...NEXTVAL query from the connection that is connected to element 3 and the connection that is connected to element 2 respectively. The example illustrates the allocation of batch assignment for each element. In this example:

Element 1 receives a batch of 1-100.
Element 3 receives a batch of 101-200.
Element 2 receives a batch of 201-300.

From the connection that is connected to element 1 (demonstrated by SELECT elementId# FROM dual), create the myseq2 sequence specifying a batch value of 100. Then, issue a SELECT...NEXTVAL query. Observe the value 1 is returned.

Command> SELECT elementId# FROM dual;
< 1 >
1 row found.
Command> CREATE SEQUENCE myseq2 BATCH 100;
Command> SELECT myseq2.NEXTVAL FROM dual;
< 1 >
1 row found.

From the connection that is connected to element 3, first verify the connection to element 3, then issue a SELECT...NEXTVAL query. Observe the value 101 is returned.

Command> SELECT elementId# FROM dual;
< 3 >
1 row found.
Command> SELECT myseq2.NEXTVAL FROM dual;
< 101 >
1 row found.

From the connection that is connected to element 2, first verify the connection to element 2, then issue a SELECT...NEXTVAL query. Observe the value 201 is returned.

Command> SELECT elementId# FROM dual;
< 2 >
1 row found.
Command> SELECT myseq2.NEXTVAL FROM dual;
< 201 >
1 row found.