Correlated and Uncorrelated Subqueries

The optimizer can execute correlated and uncorrelated subqueries in parallel.

Parallel Execution of Nested Correlated Subqueries

A correlated subquery is a nested subquery whose WHERE clause refers to an attribute of a relation declared in the outer query. The optimizer evaluates the subquery for each row or combination of rows that the outer query returns.

The optimizer can unnest most correlated subqueries if the rewritten query provides a lower cost. For information about how the optimizer unnests correlated subqueries, refer to Query Plans for Subqueries.

If the optimizer cannot unnest a correlated subquery, the optimizer can speed execution of the query with parallel execution.

Both the outer query and the correlated subquery can take advantage of parallel execution. The database sever uses the SQL operator EXPRESSION to process the subquery and can use parallel execution for the other SQL operators in the query plan, such as SCAN.

For example, suppose you have the following correlated subquery:

SELECT ... FROM tab1, tab2
   WHERE tab1.b = tab2.b
      AND tab1.a = ( SELECT tab3.a 
                  FROM tab3
                  WHERE tab1.d = tab3.d) 

Figure 35 shows how the query plan might look for this sample correlated query. The SQL operator EXPRESSION evaluates the subquery on tables tab1 and tab3. If tab1 and tab2 are fragmented across dbspaces, the optimizer can create multiple instances of the SQL operators SCAN and JOIN to enable parallel execution.

Figure 35. Sample Query Plan for Correlated Subquery

Parallel Execution of Uncorrelated Subqueries

An uncorrelated subquery is a subquery whose WHERE clause does not depend on a value that is returned in the main query. The optimizer executes an uncorrelated subquery only once. For uncorrelated subqueries, only the first thread that makes the request actually executes the subquery. Other threads then use the results of the subquery and can do so in parallel.