Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

correleted subqueries

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
elayarajack

New User


Joined: 15 Feb 2004
Posts: 8

PostPosted: Fri Feb 20, 2004 12:25 pm    Post subject: correleted subqueries
Reply with quote

hi,

can any one can give me a clear concept of correleted subqueries and sibqueries with suitable examples.
Back to top
View user's profile Send private message

mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1203
Location: India

PostPosted: Fri Feb 20, 2004 2:38 pm    Post subject: Re
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Which type is more efficient Nested S... ashikrim DB2 2 Thu Aug 31, 2006 4:29 pm
No new posts What is EXIST, ANY, ALL in subqueries fayum DB2 1 Wed Mar 03, 2004 4:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us