Oracle SQL subquery un-nesting Oracle Database Tips by Donald Burleson

Subquery performance has always been problematic for Oracle queries, and Oracle introduced global temporary tables to allow subqueries to be executed independently of the outer query, a powerful technique where you can hypercharge Oracle performance by re-writing subqueries to use temporary tables

But there are other ways to un-nest subqueries and starting with Oracle 9i, Oracle will automatically un-nest some sub-queries:

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:

• Uncorrelated IN subqueries
   
• IN and EXISTS correlated subqueries, as long as they do not contain aggregate functions or a GROUP BY clause

You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:

• You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery.
   
• You can unnest other subqueries by specifying the UNNEST hint in the subquery.

Also, the hidden hint _unnest_subquery enables un-nesting of correlated subqueries.