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

Phil Turmel pturmel-webdev at turmel.org
Mon Nov 19 20:48:22 CST 2007


Hi Matt,

Sorry for the delay... one of those days.

Matt Warden wrote:
> 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.

You're right.  Once Oracle decides to scan the whole table, the index is
pointless.

> 
>> 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.
>>

This part of the discussion prompted me to review Oracle's documentation
on query optimization, particular in regards to access paths, and
looping joins vs. hash joins.[1] More below.

> ...
>> 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?
> 

Based on the Oracle docs, the only outer join cases I can think of that
are likely to optimize better with a union might be:

1) a right outer join on a 1-M relationship (yeah, the "1" is the
optional part) where the result set is a small fraction of the "M" table, or
2) an outer join on one or more highly-filtered subqueries.

The Oracle optimizer is going to do a full table scan or equivalent on
the larger dataset of the two tables in a join, unless you meet some
basic criteria:

1) You have optimization statistics for the tables involved,
2) Indexes are available for all of the join conditions,
3) only a small fraction of the total data in the table is involved

So, to have a union outperform an outer join, each part of the union
must not trigger a table scan, where the outer join would.

Now that I've looked closer, I think my gut reaction to this kind of
optimization (not worth it) has further support.  I'd try optimizer
hints before I converted a join to a union.

HTH,

Phil

[1]
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#sthref1281



More information about the thelist mailing list