Nested subqueries

Nested subqueries can be explicitly described in abstract plans:

Abstract plans allow you to affect the query plan for the subquery, and to change the attachment point for the subquery in the outer query.

The nested operator specifies the position of the subquery in the outer query. Subqueries are “nested over” a specific derived table. The optimizer chooses a spot where all the correlation columns for the outer query are available, and where it estimates that the subquery needs to be executed the least number of times.

The following SQL statement contains a correlated expression subquery:

select * 
from t1, t2
where c11 = c21
    and c21 > 100
    and c12 = (select c31 from t3 
                where c32 = t1.c11)

The abstract plan shows the subquery nested over the scan of t1:

( g_join 
    ( nested 
        ( i_scan i_c12 t1 ) 
        ( subq 1 
            (t_scan ( table t3 ( in ( subq 1 ) ) ) )
         ) 
    ) 
    ( i_scan i_c21 t2 ) 
)