SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-31
Correlated Subquery
The plan consists of two steps:
•
Plan step 1 includes 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
Plan step 2 executes once before plan step 1, which indicates that the subquery is
evaluated once before the outer query and that this is a noncorrelated subquery.
Correlated Subquery
This query and EXPLAIN plan are for a correlated subquery:
EXPLAIN
SELECT *
FROM ordone o1
WHERE NOT EXISTS ( SELECT B
FROM ordtwo o2
WHERE o1.A = o2.B ) ;
The total cost for this query is 3.
Example 6-18. EXPLAIN Plan for Correlated Subquery (page 1 of 2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA7.REG1.ORD1
with correlation name O1
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 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. : None