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

"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
Received on Thu Jul 11 2002 - 20:05:26 CDT