Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:JQpX8.33248$Hj3.99987_at_newsfeeds.bigpond.com...
>
> "Galen Boyer" <galenboyer_at_hotpop.com> wrote in message
> news:ud6tuuxdo.fsf_at_grossprofit.com...
> > On Thu, 11 Jul 2002, richard.foote_at_bigpond.com wrote:
> > > Hey Galen, don't go and doubt me now, we're almost there buddy
> > > !!
> >
> > Throw me a rope!
>
> Here it is, catch. Have you got a good hold ? Please don't let go I don't
> want to lose you !!
>
> >
> > > This is not a good example as it makes no sense to write it as
> > > it has been.
> > >
> > > But if you agree that the var column in the subquery represents
> > > the var in T1 in the outer query,
> >
> > Yes.
> >
> > > which occurrence of var are we talking about. The answer is
> > > *the current one* as being processed in the outer query.
> > >
> > > Therefore we must execute the subquery checking for the current
> > > value of var for as many rows as we process in the outer
> > > query.
> >
> > This is what doesn't make any sense. As you do the T1 subquery,
> > you CAN'T look for values of var, cause var doesn't exist in T2.
> >
>
> In the correlated subquery, the value of the var in the subquery gets
> resolved to be the current value of var in the outer query. Therefore,
> assuming the first row in T1 has a value of 'X' for var then 'X' becomes
the
> resolved value of var in the inner query (select 'X' from T2). This will
> perform a FTS on T2 returning 'X' each time. Is 'X' in 'X', yes, the count
> increments. The second value of var in T1 is 'Y'. Therefore the subquery
> resolves itself to be 'select 'Y' from T2'. It will perform another FTS of
> T2 returning 'Y' each time. Is 'Y' in 'Y', yes count increments again.
> Therefore for each row in the outer query, Oracle will perform a FTS in
the
> subquery returning the var value for as many rows there are in T2 !!
>
> Obviously a nonsensical example but that's how correlated subqueries work.
> That's why it's good programming practice to use a table prefix for
> potentially amgiuous columns.
>
> > > A correlated subquery !! If there were 10 rows in T1, the
> > > subquery would be executed 10 times. The execution plans and
> > > stats would confirm this.
> >
> > I understand a correlated subquery, I just don't understand this
> > particular one.
>
> That's probably because it's such a silly example.
>
> >
> > > I noticed a posting by Tom Kyte in this tread. He points to a
> > > very similar example in his link.
> >
> > Haven't read his particular example.
>
> Have a read. There's a much clearer example, although not dissimilar to
this
> one.
>
> Good Luck
>
> >
> > Guess I'll go do that soon.
> > --
> > Galen Boyer
>
>

Richard, Thomas, Galen et al:

I see the point now, and it makes sense.

I think my original question was prompted by a viewpoint something like: "Thanks Oracle, but if I wanted a correlated subquery, I'd have asked for one, by aliasing the outer table as well as the table in the subquery. However, I didn't, so in this case I wanted an in-line view on the inner table only".

In case anyone is curious, I stumbled on this one because the column in the subquery to which I thought I was referring had been misspelled, and then in order to submit the issue to c.d.o.s. I reduced the query to EMP/DEPT.

Thanks again to those kind folks who contributed constructively.

Regards,
Paul Received on Sun Jul 14 2002 - 13:46:07 CDT