Re: union all vs. left outer join
Date: Fri, 11 Nov 2005 00:59:05 +0000
Message-ID: <dl0iim$vi6$1_at_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.