SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-22
Using OR Operators in Predicates
Using OR Operators in Predicates
SQL uses a feature called OR optimization for some queries with OR operations. OR
optimization uses more than one access path to obtain the data and eliminates
duplicate predicates at compile time to produce the result.
MDAM is another feature that works with OR operators. It provides some benefits that
the OR optimization feature does not:
•
MDAM eliminates duplicate key values in OR predicates at run time. It does so
before SQL/MP accesses any tables so there is no performance penalty.
•
MDAM can process multiple tables in a query and be used on the inner and outer
tables of nested joins and on the outer tables of sort merge, hash, and key-
sequenced merge joins.
•
WHERE clauses need not be in disjunctive normal form. That is, WHERE clauses
can have more than one level of OR operations.
MDAM is enabled by default.
Choosing Optimized OR Plans
SQL might use an optimized OR plan when all of these conditions are satisfied:
•
There are two or more search conditions connected by OR operators.
•
Each search condition contains predicates used as keys on an index. That is, the
predicates involve columns that belong to the key prefix of an index.
•
The search condition is in disjunctive normal form. That is, there is only one level
of OR operations; for example:
(P1 AND P2 AND P3) OR (P4 AND P5) OR (P6 AND P7 AND P8)
Note that the parentheses are not required; the AND operator has precedence
over the OR operator.
For execution plans that use OR optimization, the optimizer considers index-only
access for each index scan independently. Index-only access can, however, be used
only if the index contains all of the columns referenced in the entire query.
Additional indexes might enable OR optimization for additional columns.
Plans That Do Not Use OR Optimization
In general, OR optimization is not used in these cases:
•
A query involves more than one table.
•
Columns in the predicate are not part of a key prefix.
•
At least one single predicate—or set of predicates connected by AND operators—
contains only nonkey predicates.