SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-83
Second Formulation
The plan consists of three steps: a scan of the ODETAIL table, a join of the PARTS and
ODETAIL tables, and a scan of the PARTS table.
The PARTS table is scanned twice, because the FROM clause of QUERY 1 specifies
an implicit join operation:
FROM ODETAIL O, PARTS P
In step 3, the optimizer chooses aggregate evaluation by the disk process (DP2),
which is the most efficient aggregate evaluation method.
Second Formulation
Now suppose that you reformulate the query to eliminate the unnecessary join as
follows:
PREPARE QUERY2 FROM
SELECT ORDERNUM
FROM ODETAIL O
WHERE O.PARTNUM = 5100
AND QTY_ORDERED <
(SELECT AVG(QTY_AVAILABLE)
FROM PARTS P
WHERE P.PARTNUM = 5100) ;
---------------------------------------------------------------------------
Plan step 3
Characteristic : Executes once per row retrieved in plan step 2
---------------------------------------------------------------------------
Operation 3.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 1 out of 4 columns
Access path 1 : Primary
SBB for reads : Not used
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
P.PARTNUM = 5100
Pred. selectivity : Expect to select 100% of rows from table
Executor pred. : None
DP2 aggregate : Computed for each group
AVG ( QTY_AVAILABLE )
Table selectivity : Expect to select 100% of rows from table
Expected row count: 28 rows after the scan
Operation cost : 2
Total cost : 50
Example 6-51. EXPLAIN Plan for QUERY1 (page2of2)