Re: Co-Related Sub-Query

On Wed, 24 May 1995 stowe_at_mcs.net wrote:

> Date: Wed, 24 May 1995 14:34:12 -0500
> From: stowe_at_mcs.net
> To: Dave Erickson <erickson_at_stimpy.mfa.com>
> Subject: Re: Co-related Sub-Query
>
> > Dave Erickson <erickson_at_mfa.com> writes:
> > Folks:
> > A while ago some of us were having a discussion as a result of
> > somebody's "not in" query. A few people proposed solutions that had explicit
> > co-related subqueries in them, and reported better results than the "not-in"
> >
> > This confused me, so I finally got back to some notes I had taken from a
> > meeting with an Oracle consultant, and found out the Oracle treats
> > "not-in" statements as if they were co-related subqueries! Now, I don't
> > know what exactly "treats" means, but I'm guessing it means that the
> > sub-query is re-evaluated per each row in the outer query?
>
> This is generally, but not always, correct, as an examination of the PLAN_TABLE will indicate for a
> given query.
>
> > Does anyone know why this would be implemented this way?
>
> Generally, because use of the NOT IN operator implies that the set "outside" the set is greater
> than the set "inside." In other words, Oracle must compare the value against the entire result set
> of the IN operator to determine if the NOT clause is satisfied. Using merge/join operations are
> often more effiecient than full table scans for each iteration of the IN operand, so the assumption
> usually results in better performance.
>
I guess I'm still confused. First of all, I'm not sure the assumption that the outer resultant set is greater than the inner resultant set is a justified one. (I know the poster of this isn't the originator of the assumption.) I can think of equally valid circumstances for either set being bigger than the other. Secondly, maybe my understanding of the treatment of co-related subqueries is wrong. I thought it meant that the inner resultant set must be generated anew for each row of the outer set being evaluated, as the criteria for the inner set is dependent upon the value of the outer set currently being evaluated. (That sentence ran in circles because it's so cyclical :-)

Ie, 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.

I'm willing to believe I have a basic misunderstanding of one of these concepts somewhere in here. Can someone straighten me out? The selects above are just examples of concept. Please don't reply with more efficient options, because I'm just trying to get a handle on this one issue.

TIA,
Dave

David B Erickson, Project Leader     * 
McHugh Freeman                       * "YIP YIP YIP YIP YIP YIP YIP YIP YIP"
erickson_at_mfa.com                     *                           -Dino