SQL分区消除

Question

我当前正在测试分区配置，使用实际执行计划来识别 RunTimePartitionSummary/PartitionsAccessed 信息。

当针对分区列使用文字运行查询时，分区消除工作正常（使用 = 和 <=）。但是，如果查询连接到查找表，则分区列 <= 到查找表中的列，并使用另一个条件限制查找表（以便仅返回一行，就像它是文字一样) 不会发生消除。

仅当连接条件为 <= 而不是 = 时，才会发生这种情况，即使结果相同。反转逻辑并在之间使用也不起作用，使用交叉应用函数也不起作用。

编辑：（重现步骤）

好的，开始吧！

--Create sample function
CREATE PARTITION FUNCTION pf_Test(date) AS RANGE RIGHT FOR VALUES ('20110101','20110102','20110103','20110104','20110105') 
--Create sample scheme
CREATE PARTITION SCHEME ps_Test AS PARTITION pf_Test ALL TO ([PRIMARY])
--Create sample table
CREATE TABLE t_Test
    (
        RowID int identity(1,1)
        ,StartDate date NOT NULL
        ,EndDate date NULL
        ,Data varchar(50) NULL
    )
ON ps_Test(StartDate)
--Insert some sample data
INSERT INTO t_Test(StartDate,EndDate,Data)
VALUES
    ('20110101','20110102','A')
    ,('20110103','20110104','A')
    ,('20110105',NULL,'A')
    ,('20110101',NULL,'B')
    ,('20110102','20110104','C')
    ,('20110105',NULL,'C')
    ,('20110104',NULL,'D')

--Check partition allocation
SELECT *,$PARTITION.pf_Test(StartDate) AS PartitionNumber FROM t_Test

--Run simple test (inlcude actual execution plan)
SELECT 
    *
    ,$PARTITION.pf_Test(StartDate)
FROM t_Test
WHERE StartDate <= '20110103' AND ISNULL(EndDate,getdate()) >= '20110103'
--<PartitionRange Start="1" End="4" />

--Run test with join to a lookup (with CTE for simplicity, but doesnt work with table either)
WITH testCTE AS
    (
        SELECT convert(date,'20110101') AS CalendarDate,'A' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110102') AS CalendarDate,'B' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110103') AS CalendarDate,'C' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110104') AS CalendarDate,'D' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110105') AS CalendarDate,'E' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110106') AS CalendarDate,'F' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110107') AS CalendarDate,'G' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110108') AS CalendarDate,'H' AS SomethingInteresting
        UNION ALL
        SELECT convert(date,'20110109') AS CalendarDate,'I' AS SomethingInteresting
    )

SELECT
    C.CalendarDate
    ,T.*
    ,$PARTITION.pf_Test(StartDate)
FROM t_Test T
    INNER JOIN testCTE C
        ON T.StartDate <= C.CalendarDate AND ISNULL(T.EndDate,getdate()) >= C.CalendarDate
WHERE C.SomethingInteresting = 'C'    --<PartitionRange Start="1" End="6" />

--So all 6 partitions are scanned despite only 2,3,4 being required, as per the simple select. 

--编辑以使结果范围相同以确保公平测试

Answer 1

查询扫描所有分区是有意义的。

所有分区都涉及谓词 T.StartDate <= C.CalendarDate，因为查询规划器不可能知道 C.CalendarDate 可能采用哪些值。