Certain forms of subqueries can be processed more efficiently when:
The query is flattened into a join query, or
The subquery result set is materialized as a first step, and the results are used in a second step with the rest of the outer query.
When the optimizer chooses one of these strategies, the query is not processed as a subquery, so you will not see the subquery message delimiters. The following sections describe showplan output for flattened and materialized queries.