SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-22
Query With Lower and Upper Bound
Query With Lower and Upper Bound
The query for the EXPLAIN plan in Example 6-11 specifies both a lower bound
(>=50,000) and an upper bound (<=100,000):
EXPLAIN
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY >= 50000
AND SALARY <= 100000 ;
The total cost of the query is 2.
Note that predicate and table selectivity are both approximately 38.5 percent. For the
range predicates, selectivity is calculated as follows:
100000 - 50000
selectivity = --------------------------------
SECONDHIGHVALUE - SECONDLOWVALUE
Example 6-11. EXPLAIN Plan for Lower and Upper Bounded Predicates
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 : Primary
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
( SALARY >= 50000 ) AND ( SALARY <= 100000 )
Pred. selectivity : Expect to select 38.5048% of rows from table
Executor pred. : None
Table selectivity : Expect to select 38.5048% of rows from table
Expected row count: 22 rows after the scan
Operation cost : 2
Total cost : 2