Many to many merge join on a unique lookup

GokhanVarol 2013-07-08 16:19:07

What is the reason the merge join is Many to many, even though the exists clause will generate a true false against a unique index.
Thank you

CREATE UNIQUE CLUSTERED INDEX CntyCd ON #CntyCd(CntyCd)
 
CREATE UNIQUE NONCLUSTERED INDEX Edition ON #CntyCd(CntyCd,Edition)
 
ALTER TABLE [tCommon].[UnvPclID] ADD CONSTRAINT [tCommon_UnvPclID_PKC] PRIMARY KEY CLUSTERED ([CntyCD], [Edition], [PclId], [PclSeqNbr]) WITH (SORT_IN_TEMPDB = ON, FILLFACTOR  = 95) ON [Common]
GO
CREATE NONCLUSTERED INDEX [IDX_UnvPclID] ON [tCommon].[UnvPclID] ([UnvPclID]) INCLUDE ([CntyCD], [Edition], [PclId], [PclSeqNbr]) WITH (SORT_IN_TEMPDB = ON, FILLFACTOR  = 95) ON [Common]
GO
 
 
....
FROM    ( SELECT    up.CntyCd
                  , up.Edition
                  , up.PclId
                  , up.PclSeqNbr
                  , up.UnvPclID
          FROM      [tCommon].[UnvPclId] up
          WHERE     EXISTS (SELECT 1 FROM #CntyCd c WHERE up.CntyCd = c.CntyCd AND up.Edition = c.Edition)
        ) up
...

alt text

link text8-2013+12-15-54+pm.jpg
link text

SQLkiwi 2013-07-12 00:53:48
A one-to-many merge join requires the 'one' side of the join input to be the upper input. The join hints in your query (e.g. LEFT MERGE JOIN) add an implied FORCE ORDER hint, forcing the order of inputs for all joins. Although the EXISTS semi-join is converted to an inner join by the optimizer, you have removed its freedom to reorder inputs and joins, so you end up with a merge join where the top input is the many side of the join. Hence, you get a many-to-many join. This query is a mess of hints – you really need to understand all the implications of hinting before writing such a thing.
GokhanVarol 2013-07-12 01:13:27
I agree, I did not like that plan at all. It was one of the first iterations me attempting to write that query. I still asked the question since I did not understand the cause of many to many.
Thank you