SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-20
EXPLAIN Plans for Bounded Predicates
The optimizer chooses index-only access rather than access to the base table through
the primary key:
Access path 1 : Alternate\SQL1.$DATA8.PERSNL.XEMPNAME,
index only
Because the index is in the same key sequence as the ORDER BY request
(LAST_NAME, FIRST_NAME), a sort is not required before returning the rows in the
requested order. Note that the total cost of the query is now 2 (compared to 3 for the
plan choosing primary access to the table).
EXPLAIN Plans for Bounded Predicates
Example 6-10 on page 6-21 and Example 6-11 on page 6-22 show EXPLAIN plans for
two queries that specify bounds in the search condition. Both queries retrieve data
from the EMPLOYEE table (primary key EMPNUM).
Query With Lower Bound
The query for the EXPLAIN plan in Example 6-10 on page 6-21 specifies a predicate
with a lower bound (WHERE SALARY >= 50000):
Example 6-9. EXPLAIN Plan Choosing Index-Only Access
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PERSNL.EMPLOYEE
with correlation name EMPLOYEE
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 3 out of 6 columns
Access path 1 : Alternate \SQL1.$DATA8.PERSNL.XEMPNAME, index only
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from index
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 57 rows after the scan
Operation cost : 2
Total cost : 2