主页 / dba / 问题 / 4511
Accepted
Richard
Asked: 2011-08-10 11:50:20 +0800 CST

我可以通过更新分区键在分区之间移动行吗？

  • 772

我认为这将是一个相当简单的问题，但实际上我很难找到答案。

问题：您能否通过简单地更新分区列使其跨越分区边界来将分区表中的数据行从一个分区移动到另一个分区？

例如，如果我有一个具有分区键的表：

CREATE TABLE SampleTable
(
    SampleID INT PRIMARY KEY,
    SampleResults VARCHAR(100) NOT NULL,
)

使用映射到主键的分区函数：

CREATE PARTITION FUNCTION MyPartitionFunc (INT) AS
RANGE LEFT FOR VALUES (10000, 20000);

我可以通过将 SampleID 从 1 更改为（例如）500,000 将一行从第一个分区移动到第三个分区吗？

注意：我将其标记为 sql server 2005 和 2008，因为它们都支持分区。他们的处理方式不同吗？

sql-server sql-server-2008

4 个回答

  1. Best Answer

    我没有要测试的 2005 服务器。然而，2008 年似乎按预期处理了这个问题：

    USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;

    您应该在更新之前在每个分区中看到一条记录，然后在第一个分区中看到两条记录。

    • 13

  2. 为了测试这一点，实验实际上需要对表进行分区。请参阅http://www.kodyaz.com/articles/how-to-partition-table-non-partitioned-table-sql-server-2008.aspx

    查询分区函数只是告诉你分区函数说什么。它没有说明数据存储在哪里。您可以设置一个分区函数并运行它，而无需实际对表进行分区，正如这里已经演示的那样。

    为了对表进行分区，您还必须创建文件组和使用分区函数将函数结果分配给文件组的分区方案。然后，您必须在使用该分区方案的表上放置一个聚集键。

    设置分区

    我不是命令行 SQL 方面的专家。我使用 SSMS 界面来设置文件组 pfg1（带有 pf1 文件）和 pfg2（带有 pf2 文件）。然后我声明了分区函数和方案：

    CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO

CREATE PARTITION SCHEME ps_IDRange1
AS PARTITION IDRange1
TO (pfg1, pfg2)
GO

    创建表和聚集索引

    CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE CLUSTERED INDEX PK_IDRanges
ON dbo.IDRanges(id) ON ps_IDRange1 (ID)
GO

    完成此操作后，当您查询 sys.partitions（我有 2005）时，您会看到该表现在有两个分区，而不仅仅是一个用于该表的分区。这表明我们已经为这个表完全实现了分区。

    select * from sys.partitions where object_id = object_id('IDRanges')

    partition_id object_id index_id partition_number hobt_id 行
-------- ----------- ----------- -------- -------- -------------------- --------
72057597780295680 770674389 1 1 72057597780295680 0
72057597780361216 770674389 1 2 72057597780361216 0

    现在我们有两个分区（每个分区都有一个行数），我们可以进行实验。

    插入行

    INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)

    检查 sys.partitions 以了解发生了什么。

    select * from sys.partitions where object_id = object_id('IDRanges')

    partition_id object_id index_id partition_number hobt_id 行
-------- ----------- ----------- -------- -------- -------------------- --------
72057597780295680 770674389 1 1 72057597780295680 1
72057597780361216 770674389 1 2 72057597780361216 1

    是的。每个分区中的一行。

    移动一行。

    UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17

    检查分区

    select * from sys.partitions where object_id = object_id('IDRanges')

    partition_id object_id index_id partition_number hobt_id 行
-------- ----------- ----------- -------- -------- -------------------- --------
72057597780295680 770674389 1 1 72057597780295680 2
72057597780361216 770674389 1 2 72057597780361216 0

    第一个分区现在有两行而不是 1，第二个分区有零行而不是两行。

    我认为这证实了由于修改分区表中的聚集键而自动移动了该行。

    • 13

  3. 老帖子，但很有帮助！！

    我通过在更新后截断分区 2 证明它确实在 SQL Server 2017 中移动，并且记录仍然存在于分区 1 中

    TRUNCATE TABLE IDRanges WITH (PARTITIONS(2))

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id object_id index_id partition_number hobt_id     rows
------------ --------- -------- ---------------- ----------- ----
7.20576E+16  34099162  1        1                7.20576E+16 2   
7.20576E+16  34099162  1        2                7.20576E+16 0
    • 0

  4. 我不认为这个答案是正确的。当您使用该值时

     $PARTITION.IDRange1([ID]) AS Partition

    您只是在重新计算分区应该是什么，而不是当前记录在哪里。

    你应该使用：

    select * from sys.partitions where object_id = object_id('IDRanges')

    在我对 sql 2005 的测试中，值发生了变化，但记录保持在同一个分区中。这可能会混淆统计信息和优化器，因为它将以多线程模式运行，期望分区在特定范围内。当它试图使用分区消除来仅查询相关分区时，它也将是完全错误的。我认为您需要删除并重新插入每条记录以使它们移动。

    • -2

相关问题