Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: union all vs. left outer join

From: <henrik.sorensen_at_balcab.ch>
Date: Fri, 11 Nov 2005 00:59:05 +0000
Message-ID: <dl0iim$vi6$1@news.hispeed.ch>


vc wrote:

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

> henrik.sorensen_at_balcab.ch 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.

true.
I know that A can be in either B or C, but never in both. I tried a predicate like this

    a.id=b.aid and c.aid is null and d.bid=b.id or a.id=c.aid and b.aid is null and d.bid=c.bid 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 a.id=b.aid

ok, fair enough.
Good test btw.
>
>
> will result in two full table scans even though there indexes on a.id
> 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 A.id=T.aid 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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US