SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-23
Using OR Operators in Predicates
•
At least one single predicate—or set of predicates connected by AND operators—
contains an executor predicate.
•
A group of predicates connected by AND operators has a high selectivity, which is
likely to occur when the operator is not an equal operator (=); instead, SQL might
choose to read the table sequentially to search for rows that satisfy the query. (For
more information about selectivity, see Section 5, Selectivity and Cost Estimates.)
If you do not see entries like Access Path 1 and Access Path 2 in your EXPLAIN
output, you are not getting OR optimization. One alternative might be to use the
UNION operator.
Examples of OR Optimization
Suppose that columns B and C are key columns of an index I on table T (A, B, C, D, E,
primary key A). If a query on T contains these predicates, then OR optimization might
be performed:
WHERE ( B = :hv1 AND C BETWEEN :hv2 AND :hv3 )
OR ( B = :hv1 AND C > :hv1 AND E < :hv1 )
OR A = :hv1
OR optimization would use index access on I for the first predicate, an index access
through I for the second predicate, and a primary-keyed access for the third predicate.
In the absence of an index, SQL reads the table sequentially to search for rows that
satisfy the query.
This example would not enable OR optimization:
WHERE (B = :hv1)
OR (B = :hv2)
AND (D = :hv3)
but this would enable OR optimization:
WHERE (B = :hv1 AND D = :hv3)
OR (B = :hv2 AND D = :hv3)
This example, with primary key A,B and index C,D, would also enable OR optimization:
WHERE ((C=10) AND ... )
OR ((A=10) AND ... )
OR (((A,B)>(10,10)) AND ...)
OR ((C>10) AND ... )
Note that this example would enable OR optimization because each set of predicates
separated by OR operators contains possible key predicates (either for the alternate
index or for the primary key) possibly connected by AND operators to additional index
or base table predicates. If you connected this list with an AND operator and another
predicate (either base table or index), then OR optimization would not be considered.