Subqueries use the following optimizations to improve performance:
Flattening – converting the subquery to a join
Materializing – storing the subquery results in a worktable
Short circuiting – placing the subquery last in the execution order
Caching subquery results – recording the results of executions
The following sections explain these strategies.
See “showplan messages for subqueries” on page 119 in the Performance and Tuning: Monitoring and Analyzing for Performance for an explanation of the showplan messages for subquery processing.
