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-17
Maximizing Parallel Index Maintenance
For more information about determining when to use indexes, see Section 4,
Understanding and Planning SQL/MX Tables.
Maximizing Parallel Index Maintenance
Indexes are automatically updated whenever you insert a row into the underlying table
or whenever you change a column of the index. You can update multiple indexes in
parallel.
NonStop SQL/MX performs index maintenance on multiple rows (not individual rows,
as in NonStop SQL/MP), making the process faster but also allowing indexes and their
tables to temporarily diverge by multiple rows. The consequences are:
•
READ UNCOMMITTED ACCESS queries might obtain different data, depending
on the access path.
•
Errors that occur during index maintenance might require a more complicated
cleanup of the index.
To take maximum advantage of parallel index updates, put a table’s indexes on
separate volumes and configure them on separate CPUs to eliminate contention of
parallel operations on indexes serviced by the same DP2 process.
Using Co-located Indexes
NonStop SQL/MX supports co-located indexes, where an index is partitioned across
the same disk volumes as its underlying table.
The primary advantage of co-located indexes is reduction in message traffic. To
perform index maintenance, a single message can be sent to the DP2 process for the
disk volume on which both the index and table partition are located.
The primary disadvantage of co-located indexes is the increased disk arm movement
and resulting latency from accessing all index and table data on the same physical
disk, especially if the index and table partitions are large. This issue might not exist for
small index and table partitions located on the same disk volume.
Another disadvantage of co-located indexes is that partition management of indexes is
not automatic. If a table partition is moved to another disk, you must remember to
move its corresponding index partition.
Performing FUP RELOADs to Generate More
Accurate Query Plans
As tables and indexes are subjected to repeated INSERT and DELETE operations,
they become more fragmented and less efficiently organized. Over a period of weeks,
it can become impossible for MXCMP to accurately estimate the costs for different
query plans for those tables. In effect, the B-trees for the tables become too deep and
asymmetrical.