SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-3
Understanding the Implications of Concurrency
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 about concurrency between DDL and DML operations, see the
SQL/MX Reference Manual. For more information about the WITH SHARED ACCESS
option of MODIFY, see Minimizing Contention on page 15-4 and the WITH SHARED
ACCESS option information for the MODIFY command in the SQL/MX Reference
Manual.