SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-7
Selecting an Access Path When an Index Is Not
Available
You should specify INTERACTIVE ACCESS OFF when query or batch processing
a large number of rows.
The default value is OFF.
The CONTROL QUERY directive is most valuable in application use when host-
variable values are not available at compilation time. Because the values are not
available, the optimizer chooses a query execution plan that assumes a large number
of rows will be returned and so might overestimate the resources required for a smaller
interactive query.
The optimizer sometimes chooses sequential access of the base table over an
alternate-index access. This is because an alternate-index access causes a row to be
retrieved from the underlying base table through an additional random I/O request.
Use the CONTROL QUERY directive to turn MDAM off only if you find the query runs
better without it. Using the CONTROL QUERY directive to enable MDAM does not
force MDAM and has no effect because MDAM is enabled by default. For more
information on MDAM, see Transformation of Predicates on page 3-4.
You can also use the CONTROL QUERY directive to enable or disable hash joins. For
more information, see Specifying a Join Method on page 3-43. For a complete
description of the CONTROL QUERY directive, see the SQL/MP Reference Manual.
The CONTROL QUERY directive stays in effect until the end of an SQLCI session, the
end of a program, or until the directive is reentered.
In a host language program, specific placement rules might apply to the CONTROL
QUERY directive. For more information, see the SQL/MP Programming Manual for
your host language.
Selecting an Access Path When an Index Is Not Available
SQL can access required data even if some system resource—an index, for
example—is not available.
For example, suppose that the table TPHONE is on the volume $PHONE; an index on
PHONE_NUMBER is stored on the volume $NUMBER. Consider this query:
SELECT LAST_NAME, FIRST_NAME, PHONE_NUMBER FROM TPHONE
WHERE PHONE_NUMBER = "725-6000" ;
Suppose further that SQL has chosen to use the index on $NUMBER to retrieve the
requested data.
If the volume $NUMBER is not available at run time, SQL attempts to locate an
alternate path to the data by performing an automatic recompilation—unless you have
specified the NORECOMPILE option. (If the NORECOMPILE option is in effect, SQL
cannot automatically recompile the program, and an error is returned for this SQL
statement.)
At compile time, if any information is unavailable, the SQL compiler sets a flag
indicating that the query must be recompiled at run time. A valid SQL object is still