[thelist] Truth about Outer Joins vs. Union All in Oracle?

[Matt Warden]

Phil,

Thanks again for your response. See below:

On 11/16/07, Phil Turmel <pturmel-webdev at turmel.org> wrote:
> What I don't see is an index scan of PK_T_EXCH_DATA_HEADER
>                                                 ^^^^
> I have no idea why.  I would have expected to see it in BOTH
> versions of your query.  Could you share your table and index
> definitions?

I wouldn't expect that the DB would use the index. It has to check
every row of T_EXCH_DATA_HEADER one-by-one; I would think converting
to and from rowid would just be extra computation in this case.

> What I also see is "HASH JOIN RIGHT ANTI" as the implementation
> of your "where not exists ()" clause.  Oracle chose to convert
> your subquery into a join.
>
...
> So your query plan doubled because you went from "outer join" to
> "(inner join) union (outer join)".

So, when people talk about outer join vs. union all, are they talking
about unioning an uncorrelated subquery and an IN clause?

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.