SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-13
Evaluation of Predicates
If a query execution plan changes (because of new statistics or a new index, for
example) the category of a predicate might change.
These paragraphs describe the four evaluation categories of predicates.
Key Predicates
A key predicate is a begin key or an end key that defines a lower or upper bound on
key columns for sequential retrieval. A begin key establishes an initial row position
within a table or index; the end key establishes a stopping point. Rows are read
sequentially (in ascending or descending order) as long as the end-key predicate
remains true.
In this query, the WHERE...AND... clause defines lower and upper bounds for the
search, assuming there is an index on the LAST_NAME column:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE LAST_NAME >= JONES
AND LAST_NAME <= SMITH ;
Key predicates can greatly reduce the resources needed for a query. Specify key
predicates to avoid the expense of reading and examining an entire table or index. Key
predicates are evaluated by the disk process before returning data to the file system.
If a query specifies a range predicate or an equality predicate for any key column, SQL
considers MDAM. When this happens, data outside the bounds need not be read from
disk or handled in any way.
For more information on key predicates, see Positioning With Key Predicates on
page 3-16.
Index Predicates
An index predicate is any predicate, other than a begin-key or end-key predicate, that
is applied to the rows of an alternate index. Whenever a scan operation uses alternate
index access, all possible predicates are applied to index rows before accessing the
base table. If an index predicate evaluates to false or unknown, SQL does not access
the corresponding base table row.
Index predicates are evaluated for every index row within the bounds defined by the
begin-key and end-key predicates. Index predicates can reduce the number of rows
read from the base table, thus avoiding physical I/O operations.
Index predicates are evaluated by the file system or the disk process, depending on
the type of I/O buffering chosen:
•
If single-row access (no sequential block buffering) or virtual sequential block
buffering (VSBB) is chosen, the disk process evaluates index predicates, returning
only those rows for which the predicates are true.