Re: union all vs. left outer join

From: <henrik.sorensen_at_balcab.ch>
Date: Tue, 08 Nov 2005 22:02:03 +0000
Message-ID: <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 ?

Henrik Received on Tue Nov 08 2005 - 23:02:03 CET

Original text of this message