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

amount of swap file space is not available, an SQL/MX compilation might fail, or an executing
statement might return an “insufficient memory” error.
See the discussion on managing kernel-managed swap files in the Kernel-Managed Swap Facility
(KMSF) Manual for more information on how to use NSKCOM to monitor kernel-managed swap
file use, how to monitor swap file usage by process, and how to change your KMSF configuration.
To identify and avoid possible memory contention between either the application or the master
executor and other system components in process space such as QIO, see the discussion on
configuring the QIO subsystem in the QIO Configuration and Management Manual.
Optimizing Index Use
An index on a table provides an alternate access path that differs from the inherent access path
(clustering key). Indexes improve application performance for data retrieval operations. When
compiling a statement, the SQL/MX optimizer selects the execution plan for a statement by choosing
the best access path to the data. If an index exists, the SQL/MX optimizer evaluates using the
index.
Indexes can also improve performance by eliminating the need for the DP2 process to access the
underlying table. If the query can be satisfied by the columns contained in the index, the underlying
table will probably not be accessed.
When evaluating whether to use an index or a table scan, NonStop SQL/MX compares the number
of base table scan I/Os and the I/Os for index access. The use of sequential cache for a scan
increases the performance of the scan and increases the likelihood of its use.
Index-only access is faster than a table scan. A sort prevented by index access must be looked at
closely, however, because the cost of a scan plus a sort might be less than the cost of index and
base table access. For more information, see the SQL/MX Query Guide.
For more information, see “Understanding and Planning SQL/MX Tables” (page 29).
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.
Optimizing Index Use 305