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

Matt Warden mwarden at gmail.com
Sun Nov 18 21:17:35 CST 2007


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

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list