SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-2
Minimizing Contention
•
Moving a partition
°
When using the WITH SHARED ACCESS option, ALTER TABLE PARTONLY
MOVE and ALTER INDEX PARTONLY MOVE allow concurrent access by
DML statements throughout all the entire operation except for the short commit
phase of the operation. To maximize concurrent access while moving a
partition, specify the WITH SHARED ACCESS option in your ALTER TABLE
statement.
°
Without the WITH SHARED ACCESS option, ALTER TABLE PARTONLY
MOVE and ALTER INDEX PARTONLY MOVE allow 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. This is the preferred method if you wish to complete the partition
move as soon as possible and users do not require concurrent access to the
data.
•
Creating a constraint
CREATE CONSTRAINT 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 a later update phase.
•
Updating statistics
UPDATE STATISTICS 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 a later update phase.
For more information about concurrency between DDL and DML operations, see
“Concurrency” in the SQL/MP Reference Manual. For more information about the
WITH SHARED ACCESS option, see the subsection, Minimizing Contention, and the
“WITH SHARED ACCESS” entry in the SQL/MP Reference Manual.
Minimizing Contention
When creating an index or moving a partition, you can minimize contention by using
the WITH SHARED ACCESS option of the CREATE INDEX, ALTER INDEX, or ALTER
TABLE statements. For example, this CREATE INDEX statement uses the WITH
SHARED ACCESS option:
CREATE INDEX EMPL2
ON EMPL (JOBCODE) CATALOG PERSNL
WITH SHARED ACCESS
NAME CR_IND_EMP2
COMMIT BY REQUEST;
The WITH SHARED ACCESS option can also be used in embedded programs.
Note. The WITH SHARED ACCESS option does not support two-way splits.