SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-32
Correlated Subquery
The plan consists of two steps:
•
Plan step 1 is a scan of the ordone table.
•
Plan step 2 includes a scan of the ordtwo table and is an evaluation of the
subquery:
SELECT B FROM ordtwo
WHERE ordone.A = ordtwo.B
Plan step 2 executes once per row retrieved in plan step 1, which indicates that the
subquery is evaluated for every row retrieved from plan step 1 and is therefore a
correlated subquery.
Executor pred. : On rows retrieved by the scan
NOT ( EXISTS ( .. result of plan step 2 ) )
Pred. selectivity : Expect to select 60% of rows from table
Table selectivity : Expect to select 60% of rows from table
Expected row count: 1 row after the scan
Operation cost : 1
---------------------------------------------------------------------------
Plan step 2
Characteristic : Executes once per row retrieved in plan step 1
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA7.REG1.ORD2
with correlation name O2
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 0 out of 2 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
O2.B = O1.A
Pred. selectivity : Expect to select 100% of rows from table
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 1 row after the scan
Operation cost : 1
Total cost : 3
Example 6-18. EXPLAIN Plan for Correlated Subquery (page 2 of 2)