SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-13
Interpreting an EXPLAIN Plan
Table Scans
Full table scans can affect performance adversely—especially if the table is quite large.
To check for costly table scans, look for:
•
Index selectivity of 100 percent
•
No begin-key predicates, end-key predicates, or MDAM predicates
Access Path
Access can be by primary key or alternate index. Remember that an alternate index
consists of all the columns defined for the index plus the column (or columns) that
make up the primary key.
For example, the CONTROL QUERY INTERACTIVE ACCESS ON directive forces the
optimizer to consider index access. The directive indicates to the optimizer that only a
few rows are needed by the query and not the complete qualifying set. If the WHERE
clause contains columns that are part of an index prefix, the index will be used despite
selectivity and other considerations. If the EXPLAIN listing seems to indicate an
otherwise illogical choice, you should look for this directive.
For more information about access path, see Optimizing the Access Path on page 4-4.
Sort Operations
Always check for sorts and high sort costs.
Sorts can occur as a result of ordering requests (specifying an ORDER BY, GROUP
BY, or DISTINCT clause) or as a result of unanticipated join ordering requirements. For
more information, see Minimizing Sort Costs for Ordering and Grouping Operations on
page 3-54.
Correlated Subqueries
Correlated subqueries often impact performance adversely.
For queries containing subqueries, review the EXPLAIN plan for correlated subquery
characteristics. If the plan gives this message about the subquery, a correlated
subquery is present:
Executes once per row retrieved by operation
For more information, see Optimizing Subqueries on page 3-51.
Key Predicates and MDAM Predicate Sets
The EXPLAIN plan lists all begin-key and end-key predicates. Key predicates narrow
the range of searching. Such conditions reduce the number of rows processed by the
disk process; therefore, the query executes faster.