SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-18
Specifying Join Predicates
Using Range Predicates for Positioning
A range predicate on a column of the key can be used for key positioning, but
subsequent key columns cannot be used for key positioning and instead are evaluated
as index or base table predicates.
In this example, PKEY1, PKEY2, PKEY3, and PKEY4 are the first four columns of the
primary key:
SELECT PKEY1, PKEY2, PKEY3, PKEY4, COL1 FROM TABLE1
WHERE PKEY1 = :hv1
AND PKEY2 = :hv2
AND PKEY3 >= :hv3 AND PKEY3 <= :hv4
AND PKEY4 > :hv5
SQL uses PKEY1, PKEY2, and PKEY3 as key predicates. PKEY4 > :hv5 cannot be a
key predicate, because it follows an expression with a range predicate. Instead, it
becomes a base table predicate.
For an alternative to specifying key columns in separate clauses, see Specifying
Multivalued Predicates on page 3-19. For information on predicates with missing key
columns, see Index Predicates on page 3-13.
Defining Key Predicates With Multiple Columns
For tables and alternate indexes with keys that consist of multiple columns, specify
predicates with a key prefix (either a full or partial key, starting with the leftmost key
column). In this example, the PARTLOC table has a primary key consisting of
LOC_CODE and PARTNUM. There is no index on PARTLOC. This query causes a
scan of the entire table:
SELECT PARTNUM, QTY_ON_HAND
FROM PARTLOC
WHERE PARTNUM > 10 ;
If you add a value for the leftmost part of the key (defined as LOC_CODE), or provide
an index on PARTNUM, SQL can retrieve the data more efficiently.
Include leftmost key column values, if known, in every predicate.
Specifying Join Predicates
In general, specify a join predicate when you request a join operation. (For a
description of join predicates, see Combining Data From More Than One Table on
page 1-51.) To broaden the types of join strategies SQL can use, include an equal
operator in the join predicate. For more information, see Writing Efficient Joins on
page 3-39.