如何消除分區視圖中的表

我無法獲得將正常表連接到分區視圖以消除不滿足分區列上的謂詞的表的查詢。特別是，我對 LEFT OUTER JOIN 到分區視圖並且我的謂詞涵蓋一系列值的情況感興趣。當我修改查詢以使用 INNER JOIN 或將謂詞限制為單個值時，表被正確消除。這是一個展示該問題的腳本。我在 SQL Server 2016 SP1 中對此進行了測試。

--Create 2 tables for prices; 1 for 2017 and 1 for 2018
CREATE TABLE Price_2017
(
   PriceDate DATE NOT NULL,
   PriceValue FLOAT NOT NULL
)
GO

ALTER TABLE Price_2017 ADD CONSTRAINT PK_Price_2017 PRIMARY KEY(PriceDate);
GO

ALTER TABLE Price_2017 WITH CHECK ADD CONSTRAINT CK_Price_2017
CHECK (PriceDate >= '2017-01-01' AND PriceDate <= '2017-12-31');
GO

ALTER TABLE Price_2017 CHECK CONSTRAINT CK_Price_2017;
GO

CREATE TABLE Price_2018
(
   PriceDate DATE NOT NULL,
   PriceValue FLOAT NOT NULL
)
GO

ALTER TABLE Price_2018 ADD CONSTRAINT PK_Price_2018 PRIMARY KEY(PriceDate);
GO

ALTER TABLE Price_2018 WITH CHECK ADD CONSTRAINT CK_Price_2018
CHECK (PriceDate >= '2018-01-01' AND PriceDate <= '2018-12-31');
GO

ALTER TABLE Price_2018 CHECK CONSTRAINT CK_Price_2018;
GO

--Create a partitioned view for all dates
CREATE VIEW Price_All AS
SELECT p.PriceDate, p.PriceValue
FROM dbo.Price_2017 p
UNION ALL 
SELECT p.PriceDate, p.PriceValue
FROM dbo.Price_2018 p;

--Create some prices
INSERT INTO Price_2017 (PriceDate, PriceValue) VALUES('2017-01-01',1);
INSERT INTO Price_2017 (PriceDate, PriceValue) VALUES('2017-01-02',2);

INSERT INTO Price_2018 (PriceDate, PriceValue) VALUES('2018-01-01',10);
INSERT INTO Price_2018 (PriceDate, PriceValue) VALUES('2018-01-02',20);

--Create another table that we will relate to prices
CREATE TABLE Purchase
(
   PurchaseDate DATE NOT NULL,
   Quantity INT NOT NULL
)
GO

ALTER TABLE Purchase ADD CONSTRAINT PK_Purchase PRIMARY KEY(PurchaseDate);
GO

--Put some stuff in the other table
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2017-01-01', 1);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2017-01-02', 2);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2017-01-03', 3);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2018-01-01', 4);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2018-01-02', 5);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2018-01-03', 6);

--Test Queries

--These are all good; the execution plan includes only necessary tables
SELECT * FROM Price_All WHERE PriceDate = '2017-01-01';
SELECT * FROM Price_All WHERE PriceDate = '2018-01-01';
SELECT * FROM Price_All WHERE PriceDate BETWEEN '2017-01-01' AND '2017-01-02';
SELECT * FROM Price_All WHERE PriceDate BETWEEN '2018-01-01' AND '2018-01-02';

--Good; doesn't seek in the 2018 table
--https://www.brentozar.com/pastetheplan/?id=BJ8RBqlYG
SELECT pu.PurchaseDate, pu.Quantity, pr.PriceValue
FROM Purchase pu
LEFT JOIN Price_All pr
ON pr.PriceDate = pu.PurchaseDate
WHERE pu.PurchaseDate = '2017-01-01';

--Good; doesn't seek in the 2018 table
--https://www.brentozar.com/pastetheplan/?id=SkOEUqgKG
SELECT pu.PurchaseDate, pu.Quantity, pr.PriceValue
FROM Purchase pu
INNER JOIN Price_All pr --notice, inner join
ON pr.PriceDate = pu.PurchaseDate
WHERE pu.PurchaseDate BETWEEN '2017-01-01' AND '2017-01-02'; --notice, range of dates

--Bad; seeks in both price tables
--https://www.brentozar.com/pastetheplan/?id=BkU8UcxFM
SELECT pu.PurchaseDate, pu.Quantity, pr.PriceValue
FROM Purchase pu
LEFT OUTER JOIN Price_All pr --notice, left join
ON pr.PriceDate = pu.PurchaseDate
WHERE pu.PurchaseDate BETWEEN '2017-01-01' AND '2017-01-02'; --notice, range of dates

我包含了最後三個查詢的執行計劃的連結。只有最後一個查詢有問題（它在 2017 和 2018 表中執行查找，而它應該只在 2017 年查找）。在我的生產數據庫中，這會導致性能非常差，並且阻礙了我推出日期特定表以處理目前儲存在單個表中的 10+ 十億行的維護成本的能力。如何讓 SQLServer 在最後一個查詢中只使用 2017 表？

SQL Server 確實正在消除 2018 分區，即使它出現在計劃中。

計劃中的啟動表達式過濾器在執行時消除了不需要的 2018 表。在打開實際執行計劃的情況下執行查詢後，將滑鼠懸停在 Price_2018 表的搜尋上：

請注意，“執行次數”為 0。這意味著 SQL Server 在執行時消除了查找。

您還可以執行查詢STATISTICS IO ON以查看僅觸及 2017 表：

SET STATISTICS IO ON;
GO
SELECT pu.PurchaseDate, pu.Quantity, pr.PriceValue
FROM Purchase pu
LEFT OUTER JOIN Price_All pr --notice, left join
ON pr.PriceDate = pu.PurchaseDate
WHERE pu.PurchaseDate BETWEEN '2017-01-01' AND '2017-01-02'; --notice, range of dates

統計 IO 輸出顯示：

(2 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Price_2017'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Purchase'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Price_2018 表不在該列表中 - 這意味著它沒有被觸及。

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