SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-6
Using the CONTROL QUERY Directive
•
A table scan can be avoided and the percentage of rows to be read is small
enough to make index access cheaper.
An index contains one or more columns defined as the index, plus the columns that
make up the primary key. An index benefits the query most when all the columns
needed by the query are located in the index.
Index-Only Access
Index-only access refers to an index that fully satisfies a query without accessing the
base table. That is, all columns that the query references can be found in the index.
Index-only access compares to base-table access through the primary key as follows:
•
For random access, an index-only access usually costs about the same as a
primary-key access of the base table.
•
For sequential access, an index-only scan is superior to a primary-key scan of the
base table, because the index row sizes are usually considerably smaller than the
base table row sizes (resulting in many more rows being retrieved per physical
I/O).
Base-Table Access Through an Index
Base-table access through an index means that the index row is located first, then the
row in the base table is accessed through its primary key.
If many rows must be accessed to satisfy a query, access to the base table through an
index can be more expensive than a full table scan. When accessing the base table
through an index, rows in the base table are read randomly, and some blocks
containing those rows might be read more than once.
Using the CONTROL QUERY Directive
To inform the SQL compiler that a particular query is interactive or batch, use the
CONTROL QUERY directive; for example:
CONTROL QUERY INTERACTIVE ACCESS ON
This directive influences the optimizer’s choice of access to a table, as follows:
•
If you specify INTERACTIVE ACCESS ON, you instruct the optimizer to return
rows by choosing an alternate index over sequential access. This strategy
optimizes the response time for returning the first few rows requested.
You might want to specify INTERACTIVE ACCESS ON, for example, when you
want only the first few rows in the result or if you know that very few rows will be
examined through an index.
•
If you specify INTERACTIVE ACCESS OFF, the optimizer optimizes the response
time for returning all rows requested.