SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-16
Positioning With Key Predicates
Positioning With Key Predicates
Key predicates can greatly reduce the resources required for a query. To specify a key
predicate, use a WHERE clause that restricts the search based on the primary key or
an index. You can do this, for example, using these predicates:
•
An equality predicate (=) on a key or index column
•
Range (begin-key and end-key) predicates on key or index columns
•
A BETWEEN predicate on a key column
•
An IS NULL predicate on a key column
•
A LIKE predicate on a key column that uses a literal beginning with anything but
the pattern match symbol (% or _)
•
IN predicate with a value-list on key columns
•
AND predicates
•
OR predicates that match leftmost key or index columns
If the leading key column or columns are missing from the predicate, MDAM allows
tables and indexes to be accessed through an index:
WHERE KEY3 < 10
AND KEY4 = 6
The EXPLAIN utility lists begin-key and end-key predicates or MDAM key predicates. If
your EXPLAIN output says NONE for either the begin-key or end-key predicate or for
both, then consider adding bounds to the query to improve performance.
Use equality predicates for keys or partial keys where possible. SQL uses key
positioning for all leftmost key columns that have equality predicates.
These predicates cannot be used to specify begin-key and end-key conditions:
•
EXISTS predicates
•
IN predicates with a subquery providing the list
•
LIKE predicates that use a literal beginning with a pattern match symbol (% or _) or
that use a host variable
•
Quantified predicates (SOME, ANY, ALL)
•
Predicates with arithmetic expressions
If both the left and right arguments of a comparison predicate reference the same
table, the compiler does not use the predicate in a begin-key or end-key for index
access.