Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: union all vs. left outer join

Re: union all vs. left outer join

From: <>
Date: Fri, 11 Nov 2005 00:59:05 +0000
Message-ID: <dl0iim$vi6$>

vc wrote:

Thank you very much for the time and energy you have put into my problem.

> wrote:

...big snip...
> 25824 rows selected.

... snip snip...
> The plans are pretty similar with the 'union all' being slightly more
> efficient (by about 15% judging by the execution times). Both perform
> a number of hash [outer] joins without any index access. The second
> plan is slightly better because with your specific data you knew that
> you could transform (AB+)C+DE into (AC+)DE union all (AB+)DE and save
> one join operation. In general, such transformation would produce
> different results.

I know that A can be in either B or C, but never in both. I tried a predicate like this and c.aid is null and or and b.aid is null and but it didn't make any difference.
> Now, as to you questions:
> Having indexes does not necessarily mean that the optimizer will use
> them with the queries like that. E.g. a simple query like
> select * from a join b on

ok, fair enough.
Good test btw.
> will result in two full table scans even though there indexes on
> and b.aid because there's no point of going to the index and then to
> the respective row if we need to scan all the rows anyway. Similarly,
> it's faster to have a full scan of A because the index selectivity is
> low: with m1='A', you'll get 15K rows out of 400K. If the index
> selectivity were better the optimizer would use it. So, with your
> queries, the optimizer evaluated various access paths and chose ful
> table scans as less expensive in terms of IO and CPU.

but if you did a temporary table with just the qualifying aid's from B and C that you needed and did:

        select A.* from A inner join temp_aid T on I would be surprised if the optimizer choose to scan the whole table A.

But thanks again for sharing your insight. I am a bit surprised that the indexes are not used as I expected, but then again, I learned something.

Henrik Received on Thu Nov 10 2005 - 18:59:05 CST

Original text of this message