Re: union all vs. left outer join

<henrik.sorensen_at_balcab.ch> wrote in message news:dkqvf8$pmk$2_at_news.hispeed.ch...
> 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
>> Bytes=2240)
> ...snip...
>> There are just two hash joins, one merge join and one nested loop,
>> that's
>> all.
>
> 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.
>
> 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
>> demonstrate
>> that the second query performs better.
>
> I have already tried in DB2 on z/OS, but I could probably prepare a zip
> file
> with some more data.
> Is there a policy regarding attachment to mails on this news group ?

Please do, and send it via e-mail.

>
> Henrik
Received on Tue Nov 08 2005 - 23:42:22 CET