12. Using the Query Optimizer : Types of Nodes in a QEP : Join Nodes in a QEP : Subquery Join Node
 
Share this page                  
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