Re: union all vs. left outer join

From: <henrik.sorensen_at_balcab.ch>
Date: Tue, 08 Nov 2005 07:08:32 +0000
Message-ID: <dkpb3t$fij$1@news.hispeed.ch>

VC wrote:

> <henrik.sorensen_at_balcab.ch> wrote in message

>> 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.

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 - 01:08:32 CST