Re: union all vs. left outer join
Date: Tue, 08 Nov 2005 07:08:32 +0000
Message-ID: <dkpb3t$fij$1_at_news.hispeed.ch>
VC wrote:
> <henrik.sorensen_at_balcab.ch> wrote in message
For the second query where the OR join is done with 'union all', none of the
have a problem using the indexes to produce the resultsets.
Do you see it differently ?
Now my problem with the 'union all' vs the 'left outer join', is really
first of all performance, and also the maintenance of the query. Obviously
in my production system there are much more columns, and reference tables
that are joined to the resultset. And further the query is wrapped inside a
view.
Henrik
Received on Tue Nov 08 2005 - 08:08:32 CET
>> The test case is reduced from a production system where each table have
>> 200'000 records, and hence the materialization of the tables A-B-C is
>> performing very very badly.
>
> You need to put together a reproducible test case which would demonstrate
> that the first query is indeed slower than the second. What you've posted
> does not show that.
Ok fair enough...
But even with this little testcase, for the first query when looking at the
explain from both DB2 and Oracle, (and it seems also postgreSQL thanks to
Stefan Rybacki), the all have problem with the OR join condition. They all
will materialize the table A-B-C, which in my case means 200'000 records.
These 200'000 records are then joined to the D-E tables.
For some reason, perhaps perfectly valid reason, the OR condition causes
this strange behavior. I am looking for either an other way to do the query
with the OR join, or an explanation for the way that this query gets
interpreted.