為什麼 SQL Server 不優化 UNION？

考慮這些查詢（SQL Fiddle）：

查詢一：

SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;

查詢 2：

SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
SELECT * FROM Koo;

請注意，Koo 與 Boo/Foo 不重疊，因此最終結果是相同的。問題是為什麼第一個UNION / UNION組合沒有合併到單個 SORT 操作中？

查詢優化器確實有 n 元運算符，儘管執行引擎的數量要少得多。為了說明，我將使用您的表的簡化版本 - (SQL Fiddle)。

SELECT DISTINCT
   number
INTO foo
FROM master..spt_values
WHERE 
   number < 1000;

SELECT DISTINCT
   number
INTO boo
FROM master..spt_values
WHERE 
   number between 300 and 1005;

SELECT DISTINCT
   number
INTO koo
FROM master..spt_values
WHERE 
   number > 1006;

ALTER TABLE dbo.foo ADD PRIMARY KEY (number);
ALTER TABLE dbo.boo ADD PRIMARY KEY (number);
ALTER TABLE dbo.koo ADD PRIMARY KEY (number);

給定這些表和數據，讓我們看一下三向UNION查詢的輸入樹：

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_Union
   OUTPUT(COL: Union1006 )
   CHILD(QCOL: [f].number)
   CHILD(QCOL: [b].number)
   CHILD(QCOL: [k].number)
   LogOp_Project
       LogOp_Get TBL: dbo.foo(alias TBL: f)
       AncOp_PrjList 
   LogOp_Project
       LogOp_Get TBL: dbo.boo(alias TBL: b)
       AncOp_PrjList 
   LogOp_Project
       LogOp_Get TBL: dbo.koo(alias TBL: k)
       AncOp_PrjList 

邏輯聯合運算符具有一個輸出和三個子輸入。經過基於成本的優化後，選擇的物理樹是具有三個輸入的合併聯合：

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607);

PhyOp_MergeUnion
   PhyOp_Range TBL: dbo.foo(alias TBL: f)(1) ASC
   PhyOp_Range TBL: dbo.boo(alias TBL: b)(1) ASC
   PhyOp_Range TBL: dbo.koo(alias TBL: k)(1) ASC

優化器的輸出被重新設計成執行引擎（沒有 n 元合併聯合）可以處理的形式：

優化後重寫將 n 元展開PhyOp_MergeUnion為多個合併聯合運算符。請注意所有估計成本如何保持與“原始”聯合運營商相關聯 - 其他人的成本估計為零。

優化器對使用 n 元運算符的聯合的原因提供了一個解釋，解釋了為什麼它不考慮將第一個範例重寫為與第二個範例相同的計劃（三向聯合是單個樹節點）。

第二個原因是沒有強制執行“缺乏重疊”的限制。在約束到位之前，不能保證和之間的聯合不會產生重複，因此我們得到一個重複刪除計劃（在這種情況下為合併聯合）boo：koo

SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

添加以下約束可確保在不使查詢的記憶體計劃無效的情況下不會違反非重疊條件：

ALTER TABLE dbo.foo WITH CHECK ADD CHECK (number < 1000);
ALTER TABLE dbo.boo WITH CHECK ADD CHECK (number BETWEEN 300 AND 1005);
ALTER TABLE dbo.koo WITH CHECK ADD CHECK (number > 1006);

現在優化器可以安全地簡單地連接：

然而，即使有了這些約束，三路聯合查詢仍然顯示為三個聯合，因為優化器通常不會考慮拆分 n 元運算符來探索替代方案。n 元運算符對於控制搜尋空間非常有用；考慮到優化器的目標是快速找到一個好的計劃，將其拆分通常會適得其反。

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

當寫成UNIONandUNION ALL時，不能再使用 n 元運算符（類型不匹配），因此樹有單獨的節點：

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION ALL
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_UnionAll
   OUTPUT(COL: Union1007 )
   CHILD(COL: Union1004 )
   CHILD(QCOL: [k].number)

   LogOp_Union
       OUTPUT(COL: Union1004 )
       CHILD(QCOL: [f].number)
       CHILD(QCOL: [b].number)

       LogOp_Project
           LogOp_Get TBL: dbo.foo(alias TBL: f)
           AncOp_PrjList 

       LogOp_Project
           LogOp_Get TBL: dbo.boo(alias TBL: b)
           AncOp_PrjList 

   LogOp_Project
       LogOp_Get TBL: dbo.koo(alias TBL: k)
       AncOp_PrjList 

引用自：https://dba.stackexchange.com/questions/30165