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.