主页 / dba / 问题 / 340067
Accepted
Artashes Khachatryan
Asked: 2024-06-07 14:29:23 +0800 CST

当表按计算列分区时按分区号过滤

  • 772

我创建了 2 个相同的表，由整数列分区。在其中一个表中，计算列，在另一个表中，不计算列。

当我使用计算列查询表并使用聚集索引对单个分区进行排序（假设输出数据已经排序，因此不需要额外的排序）时，它会扫描整个表。

CREATE PARTITION FUNCTION pf_test(int) AS RANGE RIGHT FOR VALUES(1, 2, 3, 4)
GO
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO([PRIMARY])
GO
CREATE TABLE test_computed
(
    ID BIGINT NOT NULL, 
    ID_C AS CAST(ID % 4 AS INT) PERSISTED, 
    PRIMARY KEY CLUSTERED (ID, ID_C) ON ps_test(ID_C)
) ON ps_test(ID_C)
GO
    
CREATE TABLE test_not_computed
(
    ID BIGINT NOT NULL, 
    ID_C INT NOT NULL, 
    PRIMARY KEY CLUSTERED (ID, ID_C) ON ps_test(ID_C)
) ON ps_test(ID_C)
GO
    
INSERT INTO test_computed(ID)
SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY GETDATE())
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
GO
    
INSERT INTO test_not_computed(ID, ID_C)
SELECT TOP 1000000 
    ROW_NUMBER() OVER(ORDER BY GETDATE()), 
    ROW_NUMBER() OVER(ORDER BY GETDATE()) % 4
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
GO

数据相同，但每个查询的执行计划不同。

SELECT TOP 100 *
FROM test_computed
WHERE $partition.pf_test(ID_C) = 1
ORDER BY ID DESC
    
SELECT TOP 100 *
FROM test_not_computed
WHERE $partition.pf_test(ID_C) = 1
ORDER BY ID desc

计划

真实的表有数十亿行；我们使用分区来避免扫描整个表。

SQL Server 版本是

Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64)
2024 年 3 月 29 日 23:02:47 版权所有 (C) 2019 Microsoft Corporation
Developer Edition（64 位），Windows Server 2022 Standard 10.0（内部版本 20348） :)

sql-server

1 个回答

  1. Best Answer

    计算列的早期扩展会干扰将$partition谓词向下推入扫描。

    稍后在编译过程中，扩展将匹配回持久计算列，但那时为时已晚。您最终会得到计算标量和过滤器的组合，而不是对分区 ID 进行简单的搜索。逻辑的崩溃也会导致人类认为不必要的排序。

    如果没有完全支持的解决方案，那么这是一种不幸的行为。

    要查看不扩展计算列所产生的差异，您可以尝试使用跟踪标志 176 进行查询：

    SELECT TOP (100) 
    TC.*
FROM dbo.test_computed AS TC
WHERE 
    $PARTITION.pf_test(TC.ID_C) = 1
ORDER BY 
    TC.ID DESC
OPTION (QUERYTRACEON 176);

    那么执行计划就是：

    与 TF176 一起计划

    我在文章“正确保留计算列”中对此进行了介绍。

    • 9

相关问题