SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-134
Considerations for CREATE TABLE
Also, the SQL/MX Optimizer sets the SEQUENCE_GENERATOR_CACHE_INITIAL
value based on the cardinality estimate of the number of rows and number of ESPs
for the instance that assigns the generated value to the IDENTITY column value.
SEQUENCE_GENERATOR_CACHE_INITIAL is equal to the cardinality estimate of
the row divided by the number of ESPs.
The SQL/MX Optimizer setting can be overridden by setting non-default values for
CQDs SEQUENCE_GENERATOR_CACHE_INITIAL and
SEQUENCE_GENERATOR_CACHE_MAXIMUM. In other words, the default settings for
these CQDs cause the optimizer to use the cardinality estimate and number of
ESPs to set the SEQUENCE_GENERATOR_CACHE_INITIAL value.
If the CQD SEQUENCE_GENERATOR_CACHE_INITIAL is set to a value greater
than 1, gaps appear in the IDENTITY column values. The gaps are equal to the
value set for the CQD.
Example: The following sequence of SQL operations illustrates the gaps
produced in the IDENTITY column:
>>create table t1(a1 int unsigned GENERATED ALWAYS AS
IDENTITY ( START WITH
1 INCREMENT BY 1) NOT NULL,a2 int);
--- SQL operation complete.
>>CQD SEQUENCE_GENERATOR_CACHE_INITIAL '20';
--- SQL operation complete.
>>insert into t1 values(default,1);
--- 1 row(s) inserted.
>>insert into t1 values(default,2);
--- 1 row(s) inserted.
>>insert into t1 values(default,3);
--- 1 row(s) inserted.
>>select * from t1;
A1 A2
---------- -----------
1 1
21 2
41 3
It is highly recommended to use the User-Defined cache to gain familiarity with the
IDENTITY column and external sequence generator feature and then adapt the
Note. Adaptive-Cache is enabled only when the CQD value for
SEQUENCE_GENERATOR_CACHE is 0 (zero).