Query Plans for Subqueries

A SELECT statement nested in the WHERE clause of another SELECT statement, or in an INSERT, DELETE, or UPDATE statement, is called a subquery.

Subqueries can be correlated or uncorrelated. The value that a correlated subquery produces depends on a value produced by the outer SELECT statement. Any other kind of subquery is considered uncorrelated.

Because the result of the subquery might be different for each row that the database server examines, the subquery is executed for each row in the outer query when the current correlation values are different from the previous ones. The optimizer tries to use an index on the correlation values to cluster identical values together, but this process can be extremely time consuming.

The optimizer can often rewrite a correlated subquery to unnest it in one of the following ways:

• As a join query

  A join executes as fast as or faster than a correlated subquery. The optimizer might use other SQL operators, such as Group and insert, to ensure correct semantics of the query. These additional SQL operators can execute in parallel to improve the response time of the correlated subquery. For more information about SQL operators, refer to Interpreting SQL Operator Runtime Data in the Query Plan.

• As separate queries

  Two separate queries can execute faster than a correlated subquery because each query is optimized separately. The optimizer uses fragmented temporary tables to hold the interim results.

The optimizer can unnest most correlated subqueries if the rewritten query provides a lower cost. Even if the optimizer cannot unnest a correlated subquery, parallel execution helps improve performance.

For information on how correlated subqueries execute in parallel, refer to Correlated and Uncorrelated Subqueries. If possible, the application designer should avoid writing nested subqueries.

If the optimizer decides to rewrite the correlated subquery, the output of the SET EXPLAIN ON statement shows how the subquery is rewritten.