When a query contains subqueries that are not flattened or materialized:
The showplan output for the outer query appears first. It includes the message “Run subquery N (at nesting level N)”, indicating the point in the query processing where the subquery executes.
For each nesting level, the query plans at that nesting level are introduced by the message “NESTING LEVEL N SUBQUERIES FOR STATEMENT N.”
The plan for each subquery is introduced by the message “QUERY PLAN FOR SUBQUERY N (at nesting level N and at line N)”, and the end of its plan is marked by the message “END OF QUERY PLAN FOR SUBQUERY N.” This section of the output includes information showing:
The type of query (correlated or uncorrelated)
The predicate type (IN, ANY, ALL, EXISTS, or EXPRESSION)