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

Matt Warden mwarden at gmail.com
Fri Nov 16 11:31:29 CST 2007

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:


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.

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

Thanks for your input, Phil. I agree with your assessment of the
maintainability impact.

Matt Warden
Cincinnati, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list