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

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-3
Minimizing Contention
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 16-3 and the WITH SHARED
ACCESS option information for the MODIFY command in the SQL/MX Reference
Manual.
Minimizing Contention
When managing partitions, you can minimize contention by using the WITH SHARED
ACCESS option of the MODIFY TABLE or MODIFY INDEX statements. For example,
this MODIFY TABLE statement uses the WITH SHARED ACCESS option to provide
online execution on range-partitioned objects: