SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-123
Considerations for CREATE TABLE
SEQUENCE_GENERATOR_CACHE_INITIAL default of “1”
SEQUENCE_GENERATOR_CACHE_INCREMENT default of “10”
SEQUENCE_GENERATOR_CACHE_MAXIMUM default of “10000”
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.
It is highly recommended to use the User-Defined cache to gain familiarity with the
IDENTITY column feature and then adapt the Adaptive Cache based on application
needs. The CQD values for the Adaptive Cache must be carefully chosen by weighing
the performance implications and the scalability requirements of the application.
Gaps in IDENTITY column values
The INSERT query plan that generates IDENTITY column values has the capability to
cache next values depending on the number of rows in the INSERT statement.
Caching ability is provided to reduce the bottleneck at the SG Table for parallel
INSERT operations. Caching also reduces the overhead of fetching and updating the
CURRENT_VALUE in the SG Table.
Gaps can occur in the sequence of the IDENTITY column values if the unused values
in cache are lost. Unused cache values will be lost under the following scenarios:
•
When an error occurs during an INSERT and transaction rollback occurs
•
The process (ESP or MASTER Executor) housing the cache shuts down either as
a result of query completion or from process failure
•
System failures such as CPU halts
•
If the internal transaction updating the SG Table was committed, but the INSERT
with the user transaction fails.
It is recommended that the user choose a cache value that meets the performance
needs of their application while minimizing the potential for large gaps in the sequence.
Note. Adaptive-Cache is enabled only when the CQD value for
SEQUENCE_GENERATOR_CACHE is 0 (zero).