When Db2 correlates and de-correlates subqueries

Correlated and non-correlated subqueries have different processing advantages. Db2 transforms subqueries to whichever type is most efficient, especially when a subquery cannot be transformed into a join.

Begin program-specific programming interface information. Db2 might transform a correlated query to a non-correlated, or de-correlate the subquery, to improve processing efficiency. Likewise, Db2 the might correlate a non-correlated subquery. When a correlated and non-correlated subquery can achieve the same result, the most efficient way depends on the data.

Db2 chooses to correlate or de-correlate subqueries based on cost. Correlated subqueries allow more filtering to be done within the subquery. Non-correlated subqueries allow more filtering to be done on the table whose columns are being compared to the subquery result.

Db2 might correlate a non-correlated subquery, or de-correlate a correlated subquery, that cannot be transformed into a join to improve access path selection and processing efficiency.

Example:

Db2 can transform the following non-correlated subquery into a correlated subquery:

SELECT * FROM T1 
  WHERE T1.C1 IN (SELECT T2.C1 FROM T2, T3 
                    WHERE T2.C1 = T3.C1)

Can be transformed to:

SELECT * FROM T1 
  WHERE EXISTS (SELECT 1 FROM T2, T3 
                  WHERE T2.C1 = T3.C1 AND T2.C1 = T1.C1)

Some queries cannot be transformed from one form to another. Most set functions and grouping functions make it difficult to transform a subquery from one form to another. Expressions that can prevent such transformation include:

Set functions and grouping functions

Most set functions and grouping functions make it difficult to transform a subquery from one form to another.

Example:

In the following query, the non-correlated subquery cannot be correlated to T1 because it would change the result of the SUM function. Consequently, only the non-correlated form of the query can be considered.

SELECT * FROM T1 
WHERE T1.C2 IN (SELECT SUM(T2.C2) FROM T2, T3 
						 WHERE T2.C1 = T3.C1
						 GROUP BY T2.C1)
Correlated ranges and <> comparisons

Some range comparisons involving correlated columns make it difficult to de-correlate the subquery. This is because when a correlated subquery is de-correlated we might have to remove duplicates in order to consider the virtual table in the outer position (see “Early Out” Processing). This duplicate removal requires a set of “equal-join” predicate columns as the key. Without equal-join predicates the early out process breaks down and doesn't work. That means the virtual table can only be considered in correlated form (as the inner table of the join).

Example:

Db2 cannot de-correlate the following query and use it to access T1 because removing duplicates on the T2.C2 subquery result does not guarantee that the range predicate correlation does not qualify multiple rows from T1.

SELECT * FROM T1 
  WHERE EXISTS (SELECT 1 FROM T2, T3 
                  WHERE T2.C1 = T3.C1 AND T2.C2 > T1.C2 AND T2.C2 < T1.C3)

End program-specific programming interface information.