SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-12
Specifying an Access Path
If you specify the SYSTEM option, SQL chooses the access path; this option is the
default. If, for example, you specified CONTROL TABLE * ACCESS PATH PRIMARY
to force primary key access during an SQLCI session, use CONTROL TABLE *
ACCESS PATH SYSTEM to restore the default access path selection mechanism.
Whenever you force MDAM by using the CONTROL TABLE directive, you must specify
an access path. You should not use ACCESS PATH SYSTEM with MDAM ON or in
conjunction with CONTROL TABLE *. Only ACCESS PATH options PRIMARY and
INDEX are valid for MDAM. For more information on MDAM, see Transformation of
Predicates on page 3-4.
Selecting an index for an UPDATE query could result in a Halloween situation. For
more information, see The Halloween Problem Could Occur on page 4-10.
If a forced path is not available, the query does not run. If you want a program to
choose an alternate path, check for error codes.
If you suspect that you might benefit from the use of one of these options, check your
application with and without the CONTROL option, using actual Measure statistics from
production data.
If you use one of the options, you might want to change this directive later for reasons
such as:
•
The query might not be able to use a more efficient index that might be created in
the future
•
The query might not be able to benefit from future enhancements to SQL
•
Changes to the database structure (such as dropping an index) can require
recompilation when the option is in use
Therefore, make any occurrences of it easy to find and change, using one or more of
these alternatives:
•
Make sure the directive only applies to the statement and table intended. Return
the specified table to SYSTEM method directly after the statement.
•
Isolate this directive in its own section and perform it from the inline application
code.
•
Place all statements affected by this directive in separate modules, called as
services by other modules.
Confirm all use of this option with data from the Measure product and verify its use
periodically to account for changes in data distributions and volumes. Reevaluate its
effectiveness with each new version of NonStop SQL/MP.
Caution. If you use the CONTROL TABLE ACCESS PATH option to specify a primary or
index path, you override the optimizer’s standard cost estimates (described in Section 5,
Selectivity and Cost Estimates) and therefore, might cause performance degradation instead of
enhancement. If you use this option, you must have a thorough understanding of the SQL
optimizer. Use it only if the optimizer does not produce the optimal plan.