[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:
>
> INDEX FAST FULL SCAN PK_T_EXCH_MATCH_HEADER
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
definitions?
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)".
HTH,
Phil
--
Need to contact me offlist?
Drop -webdev or you probably won't get through.
More information about the thelist
mailing list