Re: union all vs. left outer join
Date: Tue, 08 Nov 2005 22:02:03 +0000
Message-ID: <dkqvf8$pmk$2_at_news.hispeed.ch>
VC wrote:
>
> There is no materialzation gong on
true, but I am looking for an explanation why the Oracle (and DB2 for that
matter) cannot create a list of consisting of B.aid and C.aid
should be built and used for an indexed lookup on A.
After all it doesn't make sense to scan the whole table, when not necessary.
> As I said, you need to prepare a test case that
I have already tried in DB2 on z/OS, but I could probably prepare a zip file
with some more data.
> What do you mean by 'will materilize' ?
well in this case the explain show a full scan of the whole tables A-B-C.
This cannot be right. Afterall the explain shows that for the much smaller
table E, an index was used to find the row with E.m1='A'.
> in Oracle:
>
> SQL> SELECT *
> 2 from A
> 3 left outer join B on A.id = B.aid
> 4 left outer join C on A.id = C.aid
> 5 inner join D on
> 6 d.bid = B.id
> 7 or d.bid = C.bid
> 8 inner join E on E.id = D.eid
> 9 where E.m1 = 'A'
> 10 /
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15 Card=32 Bytes=2240)
...snip...
> There are just two hash joins, one merge join and one nested loop, that's
> all.
> would have the same exec. plan as the production has and would demonstrate
> that the second query performs better.
Is there a policy regarding attachment to mails on this news group ?
Henrik Received on Tue Nov 08 2005 - 23:02:03 CET