SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-8
Understanding Unexpected Access Paths
produced because information might be available for other queries in the same
program.
At run time, the SQL executor recompiles the query when it encounters the flag that
indicates a recompile is necessary. The SQL executor instructs the SQL compiler to
ignore unavailable information during this compile. If all information is now available,
the most efficient access plan can be selected. If some information is still not available,
the SQL compiler attempts to identify the most efficient access plan based on the
information that is available.
If the chosen index is still not available (for example, the communications line to a
node is down, or the volume containing the chosen index is down after the
recompilation), the executor instructs the optimizer to choose the primary key of the
table as the access path. This instruction enables the application to access the data
whenever the data is available, even if an optimal path is unavailable.
For more information about compiling programs and the RECOMPILE and
NORECOMPILE options, see the SQL/MP Programming Manual for your host
language.
Understanding Unexpected Access Paths
Sometimes the optimizer does not choose the preferred or expected access path. If
this happens, check these:
•
Did a CONTROL TABLE or CONTROL QUERY directive instruct the compiler to
take a different access path?
•
Was index-only access not possible because of the columns that must be
retrieved?
•
Does a WHERE clause indicate base-table access?
•
Does the data have an uneven distribution not recognized by the compiler?
•
Would the request cause the Halloween problem?
Control Directive Is Specified
Either a CONTROL QUERY INTERACTIVE ACCESS ON directive instructed the
compiler to take a path that did not require sorting, or a CONTROL TABLE directive
was used to force a particular path.
Note. Compilation rules differ for dynamic queries. For details, see the SQL/MP Programming
Manual for your host language.