Nested subqueries can be explicitly described in abstract plans:
The abstract plan for the subquery is provided.
The location at which the subquery attaches to the main query is specified.
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 abstract plan 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 )
)
