mcmillan
Site Admin
Joined: 18 May 2003
Posts: 867
Location: India
|
|
|
|
I hope this info will guide you:
| Quote: |
Definition: A correlated subquery refers to at least one column of the outer query.
Any predicate that contains a correlated subquery is a stage 2 predicate.
Example: In the following query, the correlation name, X, illustrates the subquery's reference to the outer query block.
SELECT * FROM DSN8610.EMP X
WHERE JOB = 'DESIGNER'
AND EXISTS (SELECT 1
FROM DSN8610.PROJ
WHERE DEPTNO = X.WORKDEPT
AND MAJPROJ = 'MA2100');
What DB2 does: A correlated subquery is evaluated for each qualified row of the outer query that is referred to. In executing the example, DB2:
1. Reads a row from table EMP where JOB='DESIGNER'.
2. Searches for the value of WORKDEPT from that row, in a table stored in memory.
The in-memory table saves executions of the subquery. If the subquery has already been executed with the value of WORKDEPT, the result of
the subquery is in the table and DB2 does not execute it again for the current row. Instead, DB2 can skip to step 5.
3. Executes the subquery, if the value of WORKDEPT is not in memory. That requires searching the PROJ table to check whether there is any
project, where MAJPROJ is 'MA2100', for which the current WORKDEPT is responsible.
4. Stores the value of WORKDEPT and the result of the subquery in memory.
5. Returns the values of the current row of EMP to the application.
DB2 repeats this whole process for each qualified row of the EMP table.
Notes on the in-memory table: The in-memory table is applicable if the operator of the predicate that contains the subquery is one of the
following operators:
<, <=, >, >=, =, <>, EXISTS, NOT EXISTS
The table is not used, however, if:
There are more than 16 correlated columns in the subquery
The sum of the lengths of the correlated columns is more than 256 bytes
There is a unique index on a subset of the correlated columns of a table from the outer query |
|
|
