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

Phil Turmel pturmel-webdev at turmel.org
Fri Nov 16 12:19:48 CST 2007

Matt Warden wrote:
> On 11/16/07, Phil Turmel <pturmel-webdev at turmel.org> wrote:
>>I don't see in the execution plan any indication that your join
>>column is indexed (idn_header_data_exch) in the data table
>>(idn_header_data_exch).  (?!?!) Unless I've misread something,
>>that forces a full table scan in both halves of the union.
> Hi Phil, that column is the pk of both tables and you can see the use
> of its index here:

I did see this.

> You can probably read the txt file version better than my mangled
> inline version: http://mattwarden.com/explainplan.txt
> The index is scanned twice in the union all version.

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 

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.

>>FWIW, I don't bother to convert outer joins to unions, ever
>>(yet).  The loss of maintainability is just too expensive.  If I
>>ever run into a tough case that absolutely needs this, I'd
>>arrange for the FK column to be NULL when appropriate, and scan
>>on that in the single-table half of the union.
> Well that would only work if the relationship is just right, where the
> table you are joining on is on the right side of a 1-M, such that the
> FK is on the driving table.

It's not so much that it's 1-M, but rather that the table in the 
single-table portion of the union can be filtered on its own 
content.  Your example needs a subquery, which is computationally 
equivalent to a join (typically).

So your query plan doubled because you went from "outer join" to 
"(inner join) union (outer join)".



Need to contact me offlist?
   Drop -webdev or you probably won't get through.

More information about the thelist mailing list