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.