SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-19
Specifying Multivalued Predicates
Specifying Multivalued Predicates
You can influence the selection of an access path—and eliminate extra scanning—by
specifying more than one column or value on each side of a predicate. Such a
predicate, called a multivalued predicate, compares multiple columns with
corresponding values.
This strategy can be especially useful for key prefixes. If key columns are supplied
sequentially (as in col1 = x AND col2 = y), SQL might read the entire table,
possibly scanning unnecessary rows several times. Instead, if the keys are a prefix for
an index, you can use the multivalued predicate construct to specify the set of keys as
a group, eliminating extra scanning.
The columns in a multivalue predicate can be used as keys if a corresponding
multicolumn index exists.
For example, suppose that you want to list all employee rows with names that come
after “JONES, JOHN”; you might write this query, assuming that LAST_NAME and
FIRST_NAME are key columns:
SELECT *
FROM employee
WHERE last_name > "JONES"
OR (last_name = "JONES"
AND first_name > "JOHN") ;
This query essentially searches for all JONES entries after JOHN JONES and then
searches for all rows past LAST_NAME = JONES. Because an OR operator is used,
this query might cause SQL to read the entire table, with possible rescanning of
unneeded rows.
You can write a more efficient query by combining the two key columns in a single,
multivalued predicate that represents the same conditions, as follows:
SELECT *
FROM EMPLOYEE
WHERE LAST_NAME, FIRST_NAME > "JONES", "JOHN" ;
The columns in the multivalued predicate are used as keys if a multicolumn index
exists on LAST_NAME, FIRST_NAME. The statement is equivalent to the earlier one,
but is more efficient and more compact.
Column Order Considerations
A multivalue predicate can be matched with a group of key columns for use as a begin-
key or end-key predicate only if all of the key columns have the same ordering
attribute: either all must be ascending, or all must be descending, as defined in the
KEY specifications of CREATE TABLE and the CREATE INDEX statements.
To maximize the performance of multivalue key predicates, avoid using the DESC
option in KEY specifications in the CREATE TABLE and CREATE INDEX statements,
or define all key columns as DESC.