當 where 子句在連接中使用 OR 過濾多個表時，會發生表掃描而不是索引搜尋

我們有一個應用程序生成的查詢，該查詢使用一個視圖，該視圖有兩個通過 LEFT OUTER 連接連接的表。當僅從一個表（任一表）中按欄位過濾時，會發生索引搜尋，並且速度相當快。當 where 子句使用 OR 包含兩個表中欄位的條件時，查詢計劃切換到表掃描並且不使用任何索引。

被過濾的所有四個欄位都在它們各自的表上建立索引。

快速查詢計劃，我從一個表中過濾 3 個欄位：https ://www.brentozar.com/pastetheplan/?id=Hym_4PRSO

我過濾四個欄位的慢查詢計劃……三個來自一個表，一個來自另一個表：https ://www.brentozar.com/pastetheplan/?id=r1dVNDRHO

理想情況下，我想了解為什麼會發生這種情況以及如何推動查詢引擎利用所有索引。

我考慮過聯合，但不幸的是，這個遺留系統正在使用ntext無法聯合的值。還要注意兩個表之間的連接是一對一的，所以我真的希望優化器利用索引，但也許它不知道？

在我看來，它正在執行掃描，因​​為如果 T1011 中的條件成立，它很可能需要 T553 中的行。另一方面，如果 T553 的任何條件成立，則需要 T1011 中的行。

因此，索引必須能夠處理在 T553 中查找行，然後從 T1011 中提取相關行，並在 T1011 中查找行並從 T553 中提取相關行。查詢優化器認為，最好的辦法可能是進行良好的連接並過濾結果。

為了使其更好，請使用 UNION 的兩種方式通過查詢。這應該有助於它決定以首選方式執行此操作，並且您可以根據需要優化聯盟的每一方。

就像是：

SELECT *, ROW_NUMBER....
FROM
(
SELECT *
FROM T1617
WHERE
 C1402001100 LIKE @P0
 OR C200000001 LIKE @P2
 OR C200000020 LIKE @P3
UNION
SELECT *
FROM T1617
WHERE C260100004 LIKE @P1
) t

---

用於SELECT CAST(ntextCol as nvarchar(max)), ...可以聯合的東西。

要記住的一件事是，操作員不僅會在兩個數據集之間，而且還會在它們內部UNION刪除重複數據。但是，根據搜尋中涉及的對象，這裡有 PK。如果它確實適用於此，答案將是在視圖的每一側包含獨特的東西，然後將其留在外部查詢中。UNION

我們有一個應用程序生成的查詢，該查詢使用一個視圖，該視圖有兩個通過 LEFT OUTER 連接連接的表。當僅從一個表（任一表）中按欄位過濾時，會發生索引搜尋，並且速度相當快。

當選擇僅涉及一個表中的屬性時，將選擇（又名過濾器，謂詞）推送到內部連接下方是有效的（= 保證始終產生正確的結果）。

將這樣的選擇向下推到外連接的**保留側也是有效的。拒絕空值（如您的那樣）的單表選擇也可以向下推到外部連接的**非保留（空填充）側。這是有效的，因為拒絕空值會將外連接變成內連接，所以前面的規則適用。

一旦謂詞低於連接，它可能會匹配一個或多個索引。

注意：SQL Server 不能對多個屬性使用析取（“或”）選擇來直接使用 b-tree 索引進行查找。它通過索引聯合轉換實現了這個結果。此重寫定位與任何單獨的析取測試匹配的行集，使用每個屬性的單獨索引，然後刪除重複項。此轉換僅適用於單個表，不適用於屬性到屬性的比較。

當 where 子句使用 OR 包含兩個表中欄位的條件時，查詢計劃切換到表掃描並且不使用任何索引。

引用兩個連接表的分離選擇不能直接推到連接下方——它必須在連接時或連接後進行評估。連詞（“and”）謂詞還有其他有效的轉換，但您沒有這些轉換。

---

雖然無法直接推送引用兩個表的析取選擇*，*但可以重寫查詢以實現相同的結果。SQL Server 不包含此轉換，因此您必須手動執行它。需要明確的是，這不是基數估計的問題，也不是優化器選擇的任何其他輸入問題——SQL Server 就是做不到。

Rob Farley 的回答中顯示了一種這樣的手動重寫。如那裡所述，在使用 時必須小心保留所有語義UNION，並且重複數據刪除方面的成本可能很高。

---

Oracle 的“或擴展”特性中包含另一個有效的重寫。這使用UNION ALL而不是UNION，因此ntext不會出現不可比較的問題。由於不需要重複刪除，因此最終結果的執行成本也可能更低：

基本思想是通過顯式排除與任何上述條件匹配的行來保證 的組件UNION ALL是不相交的。

在三值邏輯中正確地獲得所需的否定可能很棘手。OracleLNNVL為此目的使用內置的。如果測試的謂詞為false或未知，則LNNVL(test)返回true ，如果測試的謂詞為true ，則返回**false。我已經在 SQL Server 中使用.LNNVL``IIF

SELECT *
FROM T1617
WHERE C260100004 LIKE @P1

UNION ALL

SELECT *
FROM T1617
WHERE
   --- Exclude rows found in the previous step
   0 = IIF(C260100004 LIKE @P1, 1, 0)
   -- New tests
   AND
   (
       C1402001100 LIKE @P0
       OR C200000001 LIKE @P2
       OR C200000020 LIKE @P3
   )

現在，問題表明您不關心保留外連接的確切語義，因此可以通過用IIF簡單（但不太正確）替換（正確）測試來進行小幅改進NOT。這會拒絕空值，因此外連接被簡化為內連接，並且NOT可以將測試下推到 T1011 上的 seek 作為 non-sargable 謂詞：

SELECT *
FROM T1617
WHERE C260100004 LIKE @P1

UNION ALL

SELECT *
FROM T1617
WHERE
   (
       NOT C260100004 LIKE @P1
   )
   AND
   (
       C1402001100 LIKE @P0
       OR C200000001 LIKE @P2
       OR C200000020 LIKE @P3
   )

線上展示

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