sql Merge Join

最新推荐文章于 2022-11-11 09:29:33 发布

weixin_30532759

最新推荐文章于 2022-11-11 09:29:33 发布

阅读量111

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Nina-piaoye/archive/2012/06/01/2529472.html

本文深入探讨了 SQL Server 中的 Merge Join 算法，包括其工作原理、通用案例、多对多关系场景下算法应用，并通过具体实例展示如何在查询中强制使用 Merge Join。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if object_id('dbo.Table1') is not null drop table Table1
GO
CREATE TABLE Table1 (Table1_id int primary key CLUSTERED, name char(10))
GO
if object_id('dbo.Table2') is not null drop table Table2
GO
CREATE TABLE Table2 (
  Table2_id int primary key NONCLUSTERED, 
  Table1_id int,
  name char(10))
GO
CREATE CLUSTERED INDEX indTable2 ON Table2 (Table1_id)
GO

DECLARE @i int
SELECT @i = 1
WHILE @i < 1000
  BEGIN
    INSERT INTO Table1 VALUES (@i, LTRIM(str(@i)))
    SELECT @i = @i + 1
  END
GO

DECLARE @i int
SELECT @i = 1
WHILE @i < 1000
  BEGIN
    INSERT INTO Table2 VALUES (@i, @i, LTRIM(str(@i)))
    SELECT @i = @i + 1
  END
GO

SET SHOWPLAN_TEXT ON
GO
SELECT a.Table1_id, b.Table1_id FROM Table1 a INNER JOIN Table2 b
  ON a.Table1_id = b.Table1_id
GO
SET SHOWPLAN_TEXT OFF
GO

This is the algorithm of the Merge join (the description of its work in general case, for many-to-many relationship):

while (not Table1.eof) and (not Table2.eof) do
  begin
    while Table2.Table1_id > Table1.Table1_id do Table1.MoveToNextRecord();
    value = Table1.Table1_id;
    while Table2.Table1_id < value do Table2.MoveToNextRecord();
    RID = Table1.RowID();
    while Table2.Table1_id = value do
      begin
        while Table1.Table1_id = value do
          begin
            < SELECT Table1.Table1_id, Table2.Table1_id > 
            Table1.MoveToNextRecord();
          end
        Table1.MoveTo(RID);
        Table2.MoveToNextRecord();
      end
  end

Note. If the joined tables are small (contain only one data page, for example), and at least one of the joined tables have index on the column that joins the tables, then SQL Server will use Nested-Loop join instead of Merge join or Hash join (usually).

Because the query optimizer usually selects the best execution plan for a given select statement, it is not necessary to enforce the desirable join type, but sometimes it can be useful. You can enforce the desirable join type by using the OPTION clause.

This is the example to enforce Merge join:

USE pubs
GO
SET SHOWPLAN_TEXT ON
GO
SELECT a.au_id FROM authors a JOIN titleauthor b
   ON a.au_id = b.au_id OPTION (MERGE JOIN)
GO
SET SHOWPLAN_TEXT OFF
GO

This is the result:


StmtText
------------------------------------------------------------------------------------------------
SELECT a.au_id FROM authors a JOIN titleauthor b
   ON a.au_id = b.au_id OPTION (MERGE JOIN)

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------
|--Merge Join(Inner Join, MERGE:([a].[au_id])=([b].[au_id]), RESIDUAL:([a].[au_id]=[b].[au_id]))
     |--Clustered Index Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a]), ORDERED)
     |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [b]), ORDERED)

(3 row(s) affected)

转载于:https://www.cnblogs.com/Nina-piaoye/archive/2012/06/01/2529472.html