插入父行時未省略外鍵的索引視圖維護

TL; 博士;

給定一個索引視圖，該視圖JOIN在兩個表之間具有外鍵關係以及父表上的謂詞*。* 當插入外鍵的*父表時，編譯器將索引維護添加到計劃中，*即使可以證明不存在匹配的行。WHERE

**這是一個錯誤，還是錯過了優化？**或者我是否存在一些邏輯或代數謬誤？

設置

CREATE TABLE Parent (Id int identity primary key, SomeCol bit not null, OtherCol int not null);

CREATE TABLE Child (Id int identity primary key, ParentId int not null references Parent (Id) INDEX IX_Parent NONCLUSTERED);

CREATE VIEW dbo.vChild
WITH SCHEMABINDING
AS
SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
-- WHERE p.SomeCol = 0;  -- problem dependent on this line

CREATE UNIQUE CLUSTERED INDEX CX_vChild ON vChild (Id)

db<>fiddle with WHERE

db<>fiddle without WHERE

在這個階段，UPDATE視圖中的任何影響列以及視圖表中的任何一個DELETE都非常正確地觸發視圖維護。編譯器將獲取修改後的行，將它們捲起來並通過視圖的連接提供它們，將任何結果輸出到視圖的索引中。

對於對 的插入也可以這樣說Child，因為可能已經存在一行Parent（符合 的WHERE），因此新Child行可能符合聯接的條件。

問題

**插入時Parent，證明不需要做索引維護。**由於外鍵關係，匹配行還不能存在Child，因此插入中沒有符合視圖條件的行。

如果您要執行以下腳本，您將看到未完成任何視圖維護。

INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100);

粘貼計劃

很明顯，編譯器可以推斷這裡不需要視圖維護。

**但是，**如果您WHERE p.SomeCol = 0在視圖定義中取消註釋該行，您會突然得到視圖維護。因此，向視圖添加另一列，它不是連接列並且沒有外鍵關係，會導致這種情況。儘管應用了相同的關係邏輯，但仍應證明插入不符合視圖的條件，因為外鍵列仍然存在。

粘貼計劃

奇怪的是，編譯器仍然可以辨識插入不符合視圖條件的某些情況（儘管這個特定範例是自動參數化的）。

在這裡，編譯器辨識SomeCol失敗WHERE，並且不需要進行索引維護。

INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100);

粘貼計劃

優化器不會做你描述的那種推理。

相反，它依賴於一組標準的常用、易於實現、快速檢查的功能，如矛盾檢測和冗餘連接刪除，以產生有用的簡化。

正是這些模組化特徵之間的相互作用，才會產生明顯複雜的行為，人們有時會誤認為是廣泛的優化和深度的語義分析。

您觀察到的行為可以通過參考這些標準優化器功能以及使用delta algebra維護索引視圖的方式來解釋。

---

此查詢僅涉及執行計劃中的子表，因為父行保證存在：

SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId

當您在 Parent 表上添加謂詞時，這在邏輯上不再可能：

SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
WHERE p.SomeCol = 0;  -- problem dependent on this line

相同的底層機制負責刪除通過 delta 代數產生的維護子樹中的連接。當調試輸出包括：

Full Join removed for table TBL: dbo.Parent

對於插入，產生的增量與上述範例性查詢非常相似。對於更新、刪除或合併，增量可能不同，因為維護視圖可能需要不同的資訊。（在您的特定範例中，很難看到 Parent 表上可能會更新什麼，但問題主要是關於插入。）

---

當該WHERE子句存在時，該謂詞的過濾器將出現在計劃的維護部分中。

對於此語句，檢測到 SomeCol = 0 上的過濾器與插入中指定的 SomeCol 值之間存在矛盾。這種矛盾導致整個維護子樹被保證為空，因此被刪除：

INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100);

對於這個說法，沒有矛盾，但是Filter是多餘的，所以去掉了。維護子樹不保證為空，因此不會被刪除：

INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100);

如果您使案例如局部變數或參數而不是文字，過濾器將重新出現。

給出的例子不是簡單的參數化的。它有資格考慮，但參數化並不確定是否安全。不要被@1文本中存在的標記所誤導。請參閱為什麼具有 FULL 優化的計劃顯示簡單的參數化？

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