[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:
INDEX FAST FULL SCAN PK_T_EXCH_MATCH_HEADER
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
http://mattwarden.com
This email proudly and graciously contributes to entropy.
More information about the thelist
mailing list