Re: union all vs. left outer join
Date: 08 Nov 2005 15:38:02 +0200
> Warning long mail ...
> For several days I have tried to figure out why two sql with an union all
> is much faster than using a single sql with left outer join.
> But I cannot get my head around this one. It is quite possible I am
> overlooking something simple...
> I have tried the following sql on both DB2 and Oracle, and didn't get the
> expected result in any of the cases. The explain of the query looked
> remarkable alike, so maybe there is something wrong with my understanding
> of how the joins should work.
> When I look at the explain for this query:
> SELECT *
> from A
> left outer join B on A.id = B.aid
> left outer join C on A.id = C.aid
> inner join D on
> d.bid = B.id
> or d.bid = C.bid
> inner join E on E.id = D.eid
> where E.m1 = 'A'
(Sorry, this is untested, but...) is the query plan and/or execution speed of the following alternative different?
left outer join B on A.id = B.aid
left outer join C on A.id = C.aid
inner join D on
d.bid = isnull(B.id,c.bid)
inner join E on E.id = D.eid
where E.m1 = 'A'
-tapio Received on Tue Nov 08 2005 - 14:38:02 CET