Re: Co-Related Sub-Query

From: Keith B. McKendry <Keith_McKendry_at_csg.mot.com>
Date: 1995/05/25
Message-ID: <9505251057.ZM21615_at_MFGMPC>#1/1

>This is my understanding of a Co-related Subquery:
>
>Select * from table1 where val in
> (select table2.val from table2 where table2.val = table1.val)
>
>This can obviously be better done with a join, but the point is, the
>resultant
>set of the inner query must be reevaluated for every table1.val.
 

>My confusion comes with a query like this:
>
>Select * from table1 where val NOT IN
> (select distinct table2.val from table2);
>
>I have been told this is treated as a co-related subquery. Is this
>true?
>Why? Can't the inner resultant set just be generated once, and that
>set
>used in the evaluation of each row of the outer select? Forget about
>the
>assumptions of the size of either resultant set, reprocessing the
 inner
>query every time is redundant processing.

Right or wrong I can understand Oracle implementing the query as a co-related subquery. Consider a table2 with 2+ million rows:

	If read-in once and held, all 2+ million must remain in memory;
	If processed for every row in table1, memory is freed.

What if there is not enough room for the 2+ million rows (and other users on system)?

As far as performance differences between the use of IN vs. NOT IN goes,
IN statements can stop searching once it finds a match--NOT IN statements must read the entire list.

-- 
"All opinions are my own and do not reflect those of my employer nor those of my clients," the consultant disclaimed.

Keith McKendry         |      73071,3233_at_compuserve.com
SSC,  Inc.             |      
                       |
Please ignore address in header and submit responses to compuserve address.

Received on Thu May 25 1995 - 00:00:00 CEST