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-135
Considerations for CREATE TABLE
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.
Gaps in sequence generator values
There can be gaps in sequence numbers acquired by a session if two sessions
concurrently increment the same sequence. This is because sequence numbers are
generated by different sessions and one session cannot get the sequence number
generated by another session. After a sequence value is generated by a session, that
session can continue to access the value even when the sequence is incremented or
decremented by other sessions.
The gaps can also be generated because of caching next values as explained in Gaps
in IDENTITY column values.
SQL/MX Extensions to CREATE TABLE
This statement is supported for compliance with ANSI SQL:1999 Entry Level. SQL/MX
extensions to the CREATE TABLE statement are [NOT] DROPPABLE, ASCENDING,
DESCENDING, STORE BY, LOCATION, PARTITION, ATTRIBUTE, and LIKE clauses.