SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-4
How the Optimizer Processes Predicates
For more information about EXPLAIN, see Section 6, Analyzing Query Performance.
How the Optimizer Processes Predicates
A predicate is an expression that makes an assertion about data. This subsection
describes how predicates are classified by the optimizer, how they can be transformed
by SQL prior to evaluation, and how SQL evaluates predicates.
Classification of Predicates
This terminology is used to classify predicates by syntactic structure:
•
A join predicate is any predicate that refers to columns in two or more tables
(including the same table referenced more than once); for example:
t.col = u.col
•
An equijoin predicate relates columns using an equal (=) comparison operator.
•
A range predicate establishes an upper or lower limit on the value of a column. A
range predicate uses one of the comparison operations (<, <=, >=, or >) to
compare a column with the value of an expression; for example:
EMP.EMPNUM > :hv1
•
A multivalued (compound) predicate specifies more than one column or value on
each side of a predicate. It compares multiple columns with corresponding values.
•
A predicate set is a series of predicates that comprises only ANDs, BETWEENs,
and IN predicates. This is an example of a predicate set:
B BETWEEN 5 AND 10 AND B = 5 AND C = 10 AND C IN (5, 10)
Transformation of Predicates
SQL transforms some types of predicates into other forms that are more efficient but
logically equivalent to the original predicate. The modified query either reduces the
complexity of the query or improves the performance of the query. Transformation is
typically done with LIKE, BETWEEN, NOT, IN, and join predicates.
Transformation of Key Column Predicates and Predicate
Sets
For predicates that contain key columns, SQL uses an optimization method called the
MultiDimensional Access Method (MDAM). Based on the key predicates you specify,
MDAM considers all possible key values and attempts to read only those rows.
Duplicate key predicates are eliminated at run time. Whenever the cost of a direct
access method is less than the cost of a table scan, SQL could choose MDAM.