SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-82
First Formulation
Example 6-51. EXPLAIN Plan for QUERY1 (page1of2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.SALES.ODETAIL
with correlation name O
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. : 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
O.PARTNUM = 5100
Pred. selectivity : Expect to select 3.7037% of rows from table
Executor pred. : None
Table selectivity : Expect to select 3.7037% of rows from table
Expected row count: 3 rows after the scan
Operation cost : 3
---------------------------------------------------------------------------
Plan step 2 : Perform an Inner Join
Join strategy : Nested Join
Characteristic : Joins a row resulting from plan step 1
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA8.SALES.PARTS
with correlation name P
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 4 columns
Access path 1 : Alternate \SQL1.$DATA8.SALES.XPARTDES, 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. : On rows retrieved by the scan
QTY_ORDERED < AVG ( QTY_AVAILABLE ) .. result of plan
step 3
Pred. selectivity : Expect to select 33.33% of rows from table
Table selectivity : Expect to select 33.33% of rows from table
Expected row count: 25 rows after the join
Operation cost : 2