SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-15
Writing Efficient Predicates
can reduce the amount of data to be sorted or hashed for sort merge joins, hash joins,
or aggregate functions.
Executor Predicates
An executor predicate is a predicate that must be evaluated by the SQL executor
instead of by the disk process or file system.
Executor predicates are the least efficient type of predicate because they reject rows
only after the rows have already been handled by the disk process and the file system.
These predicates do not reduce the amount of physical I/O to the base table, but like
all types of predicates, executor predicates might reduce the number of rows
processed for a sort, correlated subquery, merge join, and so on, and therefore reduce
the total cost of the query.
These predicates are always evaluated by the SQL executor:
•
Correlated subquery predicates
•
Quantified (ANY, ALL, SOME) subquery predicates
•
IN and EXISTS subquery predicates
•
Merge-join and hash-join predicates
•
Predicates contained in these clauses:
°
HAVING clause
°
WHERE clause of a left join query that references columns from the table that
appears on the right of the keywords LEFT JOIN
•
Executor predicates connected by an OR operator:
(EMP.EMPNUM = :hv1) OR
(EXISTS (SELECT col1,col2 FROM TABLE1 WHERE col1 = 50) )
Writing Efficient Predicates
There are several guidelines that can help improve the performance of predicates.
These guidelines are summarized here and are described in the following subsections:
•
Use key predicates for positioning whenever possible.
•
Use join predicates to specify search conditions when joining multiple tables.
•
Use multivalued predicates when possible.
•
Understand when OR operations are optimized and when they are not.
•
Understand the performance implications of using the LIKE predicate.