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

NOTE: The WITH SHARED ACCESS option of MODIFY is supported only for both range and
hash-partitioned objects, and only when the partitioning key is a prefix of the clustering key.
For more information, see the SQL/MX Reference Manual.
These online operations can be long-running and so are subject to contention:
• Moving a partition
When using the WITH SHARED ACCESS option, MODIFY TABLE and MODIFY INDEX
allow concurrent access by DML statements throughout the entire operation except for
◦
the short commit phase of the operation. To maximize concurrent access while moving
a range partition, specify the WITH SHARED ACCESS option in MODIFY.
◦ Without the WITH SHARED ACCESS option, MODIFY allows concurrent access by DML
statements that use SELECT with BROWSE or SHARED access during an initial scan phase,
but locks out DML accesses during the remainder of the operation. Use this method if you
want to complete the partition move as soon as possible and if users do not require
concurrent access to the data.
• Creating a constraint
When you create a CHECK or NOT NULL constraint, NonStop SQL/MX confirms that
the column data in all table rows complies with, and does not violate, the constraint
◦
before adding it. Until this validation operation is completed, the table data can be read
but not updated.
◦ When you create a PRIMARY KEY, UNIQUE, or REFERENTIAL INTEGRITY constraint, an
index is created. Until the index has been fully created, the table data can be read but
not updated. REFERENTIAL INTEGRITY constraints both create an index and execute a
SELECT query that is a JOIN between two tables to ensure that the constraint is not violated.
• Creating an index
During a CREATE INDEX operation, you can run static SELECT queries on the table but
cannot perform recompilation. During a POPULATE INDEX operation, you can do both.
◦
Access to the table metadata is locked for the duration of the CREATE INDEX operation.
Access to the metadata is locked for a much shorter period during a POPULATE INDEX
operation.
◦ A CREATE INDEX operation must occur within a single TMF transaction. During a
POPULATE INDEX operation, the data movement portion occurs outside of any TMF
transaction.
• Updating statistics
◦ UPDATE STATISTICS allows concurrent access, during an initial scan phase by DML
statements that use SELECT with READ UNCOMMITED ACCESS, and does not lock the
table because data is not changed. However, the HISTOGRAM tables are locked because
data is inserted into and deleted from them.
• Trigger activation
Before you perform an import operation on a table, you must choose whether to use the
import option that turns off the triggers during the operation. If you do not turn off
triggers, they might be activated and delay the operation.
◦
◦ Before you perform a PURGEDATA operation, you must turn off all DELETE triggers.
For more information, see the SQL/MX Reference Manual. For more information, see “Minimizing
Contention” (page 295) and the WITH SHARED ACCESS option information for the MODIFY
command in the SQL/MX Reference Manual.
294 Enhancing SQL/MX Database Performance