[thelist] Truth about Outer Joins vs. Union All in Oracle?
Matt Warden
mwarden at gmail.com
Sun Nov 18 21:17:35 CST 2007
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.
More information about the thelist
mailing list