SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-17
Positioning With Key Predicates
Examples of Key Predicates
This query specifies a begin key and uses a host variable. If an index exists on the
column LAST_NAME and if SQL uses the index to perform the search, then this query
performs better than if SQL sequentially reads every row in the table, starting with the
first row.
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE LAST_NAME >= :hvar1 ;
The query returns this result:
LAST_NAME FIRST_NAME SALARY
--------- ---------- ---------
GREEN ROGER 175500.00
HENDERSON BEN 65000.00
HOWARD JERRY 137000.10
. . .
. . .
WINTER PAUL 90000.10
--- 17 row(s) returned.
The next query specifies a begin and end key, and probably performs better.
Specification of both a begin and end key increases the likelihood that SQL will choose
index access instead of a table:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE LAST_NAME >= :hvar1
AND LAST_NAME <= :hvar2 ;
The likelihood of index access is higher for the second example because the selectivity
is lower for the second example. Selectivity is estimated as .33 for the first example
and .33 * .33 = .11 for the second example. For more information about selectivity, see
Section 5, Selectivity and Cost Estimates.
The query returns the same result:
LAST_NAME FIRST_NAME SALARY
--------- ---------- ---------
GREEN ROGER 175500.00
HENDERSON BEN 65000.00
HOWARD JERRY 137000.10
. . .
. . .
WINTER PAUL 90000.10
--- 17 row(s) returned.