Does DB2 process "ALL" subquery by "nested iteration" strategy, or
using its own special algorithm?

For example, assume r(A, B, C) and s(D, E, F) are two relations,
consider the following query:
Q1: select r.*
from r
where r.A> 5 and
r.C > all (select s.D
from s
where r.B = s.E and s.F<10)

We are planning to unnest this query first by performing outer join and
then process the intermediate result by our approach:
Q2: select r1.*, s1.*
from (select r.*
from r
where r.A > 5) r1
left outer join
(select s.*
from s
where s.F<10) s1
on r.B = s.E

If DB2 evaluate query Q1 by "nested iteration" approach, query Q2
should perform faster than Q1.
However, when I ran them in DB2, Q1 is a little bit faster than Q2. So
I think DB2 has its own algorithm to evaluate "ALL" subquery, is it
true?


thanks

If you are talking about db2 udb for LUW, you can check the rewirte part
from the output of db2exfmt utility.

"LazyAnt" <soporte_altex@hotmail.com> wrote in message
news:f698f49c.0406071456.35daf166@posting.google.c om...
> Does DB2 process "ALL" subquery by "nested iteration" strategy, or
> using its own special algorithm?
>
> For example, assume r(A, B, C) and s(D, E, F) are two relations,
> consider the following query:
> Q1: select r.*
> from r
> where r.A> 5 and
> r.C > all (select s.D
> from s
> where r.B = s.E and s.F<10)
>
> We are planning to unnest this query first by performing outer join and
> then process the intermediate result by our approach:
> Q2: select r1.*, s1.*
> from (select r.*
> from r
> where r.A > 5) r1
> left outer join
> (select s.*
> from s
> where s.F<10) s1
> on r.B = s.E
>
> If DB2 evaluate query Q1 by "nested iteration" approach, query Q2
> should perform faster than Q1.
> However, when I ran them in DB2, Q1 is a little bit faster than Q2. So
> I think DB2 has its own algorithm to evaluate "ALL" subquery, is it
> true?
>
>
> thanks