SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-24
EXPLAIN Plan for DISTINCT
•
Index and table selectivity are both approximately 26 percent. For the range
predicates, selectivity is calculated as follows:
7000 - 5000
selectivity = --------------------------------
SECONDHIGHVALUE - SECONDLOWVALUE
•
The selectivity is significantly lowered because of the addition of the upper bound;
that is, the optimizer expects the number of rows returned will be smaller.
EXPLAIN Plan for DISTINCT
This query uses DISTINCT to remove duplicate rows from the result:
SELECT DISTINCT PARTNUM, PRICE
FROM PARTS
WHERE PARTNUM < 7000 AND PARTNUM > 5000 ;
The total cost of the query is 1.
The plan contains one step, which scans the PARTS table. Access is by primary key.
Example 6-13. EXPLAIN Plan for SELECT DISTINCT
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 2 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.0254% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 26.0254% of rows from table
Expected row count: 7 rows after the scan
Operation cost : 1
Total cost : 1