SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-23
EXPLAIN Plan for Key Predicates
EXPLAIN Plan for Key Predicates
The EXPLAIN plan shows a query that specifies both a begin-key and an end-key
predicate.
The query retrieves data from the PARTS table. PARTNUM is the primary key. The
query follows:
EXPLAIN
SELECT PARTNUM, PRICE, QTY_AVAILABLE
FROM PARTS
WHERE PARTNUM BETWEEN 5000 AND 7000 ;
The total cost of the query is 1.
The plan includes this information:
•
The BETWEEN operator is converted to a range predicate, “PARTNUM >= 5000
AND PARTNUM <= 7000.”
•
The WHERE predicate is specified against PARTNUM, the primary key. The
predicate specifies a range of values, so there is both a begin-key and an end-key
predicate:
Begin key pred. : PARTNUM >= 5000
End key pred. : PARTNUM <= 7000
Example 6-12. EXPLAIN Plan for Key Predicates
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.SALES.PARTS
with correlation name PARTS
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 3 out of 4 columns
Access path 1 : Primary
SBB for reads : Virtual
Begin key pred. : PARTNUM >= 5000
End key pred. : PARTNUM <= 7000
Index selectivity : Expect to examine 26.0514% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 26.0514% of rows from table
Expected row count: 7 rows after the scan
Operation cost : 1
Total cost : 1