Re: union all vs. left outer join
Date: Tue, 8 Nov 2005 17:42:22 -0500
<henrik.sorensen_at_balcab.ch> wrote in message
> VC wrote:
>> 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'.
>> There is no materialzation gong on
>> 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
>> There are just two hash joins, one merge join and one nested loop,
> 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
> I have verified this on a large production sized database with the real
> data, and the full scan of A-B-C cannot be right.
>> As I said, you need to prepare a test case that
>> would have the same exec. plan as the production has and would
>> that the second query performs better.
> I have already tried in DB2 on z/OS, but I could probably prepare a zip
> with some more data.
> Is there a policy regarding attachment to mails on this news group ?
Please do, and send it via e-mail.
Received on Tue Nov 08 2005 - 23:42:22 CET