SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-12
Evaluation of Predicates
Simplifying Predicates
Sometimes the optimizer can determine the results of predicates at SQL compilation
time and then simplify or eliminate the predicates.
When the optimizer compares values known to be equal, it substitutes a NOT NULL
predicate if both these conditions occur:
•
The expression can be null.
•
The operator is one of the following: <=, =, or >=.
Otherwise, the comparison reduces to either true or false, depending on the operator.
Evaluation of Predicates
These three NonStop SQL/MP components participate in data retrieval and can also
evaluate predicates:
•
SQL executor, which usually evaluates predicates for a serial search against
nonkey columns
•
File system, which evaluates predicates for a serial search of a memory-resident
block of rows
•
Disk process, which evaluates predicates for searches with leftmost column
matches on defined ranges
The disk process obtains data first, then passes it to the file system, which in turn
passes the data to the SQL executor process. Section 2, The Optimizer, describes
these components in more detail.
To minimize resource use, SQL evaluates predicates as early as possible in this
processing chain. By evaluating predicates in the disk process or file process, SQL can
minimize the number of rows searched and the data movement between the
application and the disk process. Unwanted rows contribute nothing to the output but
add to the expense of the query. If unwanted rows can be eliminated early, by the disk
process for example, the query uses fewer resources to transmit the data to the user.
To determine where a predicate is evaluated in an existing query, use the EXPLAIN
utility, described in Section 6, Analyzing Query Performance.
The types of predicates you use determine where they are evaluated. The optimizer
analyzes predicates and assigns each one to the most appropriate role within the
execution plan, using these four predicate categories (in order of decreasing
efficiency):
•
Key predicate
•
Index predicate
•
Base-table predicate
•
Executor predicate