Subquery Join Node
The subquery join is specific to SQL because SQL allows subselects as part of a query. These nodes join rows from a query to matching rows of a contained subselect, thus allowing the subselect restrictions on the query to be evaluated.
A typical abbreviated example of a QEP diagram involving a subquery join is shown below:
SE join
/ \
proj-rest Tn
/
table
In this diagram, Tn identifies the QEP constructed to evaluate the subselect.
This node is displayed with the following information on a QEP diagram:
• A label identifying it as a subquery (SE) join, along with the column(s) on which processing is done
• Storage structure (which is usually heap)
• Total number of pages and tuples
• Query cost information
• Optionally, sort information (whether the data produced by this node is already sorted or requires sorting)
The following example shows a QEP diagram with a subquery join:
select * from arel a
where a.col2 = (
select col2 from brel b
where a.col1 = b.col1)
and col1 = 5;
QUERY PLAN 3,1, no timeout, of T1
Proj-rest
Heap
Pages 1 Tups 1
D1 C0
/
brel
Hashed(col1)
Pages 3 Tups 156
QUERY PLAN 4,2, no timeout, of main query
SE Join(col1)
Heap
Pages 1 Tups 1
D2 C0
/ \
Proj-rest T1
Heap Heap
Pages 1 Tups 1 Pages 1 Tups 1
D1 C0
/
arel
Hashed(col1)
Pages 70 Tups 156
In the QEP pane of the SQL Scratchpad window in VDBA, these two QEPs are shown in separate tabs.
Subquery joins are reasonably expensive because the subselect must be executed once for each row of the outer subtree. The query optimizer contains logic to flatten various types of subselects into normal joins with the containing query. This allows more optimal joins to be used to evaluate the subselect.
As an example of the subselect flattening enhancement features of the query optimizer, consider the following subselect:
select r.a from r where r.c =
(select avg(s.a) from s
where s.b = r.b and r.d > s.d);
Instead of two scans of table r, the query optimizer has eliminated a scan of table r by evaluating the aggregate at an interior QEP node. The QEP appears similar to the previous example:
QUERY PLAN 7,1, no timeout, of main query
Hash Join(b)
avg(s.a)
Heap
Pages 2 Tups 359
D133 C171
/ \
Proj-rest Proj-rest
Sorted(b) Heap
Pages 1 Tups 359 Pages 40 Tups 359
D65 C112 D32 C3
/ /
r s
Btree (b,c) Hashed(NU)
Pages 44 Tups 359 Pages 44 Tups 359