主页 / dba / 问题 / 146786
Accepted
TaylorN
Asked: 2016-08-15 20:26:18 +0800 CST

更改现有分区表

  • 772

我有一个已按标识符 (CustomerId) 分区的大表。我已经创建了新客户，并且想要向表中添加新分区，并且想要确认执行此操作的正确方法。我的前任设置了这个并且不再可用，所以我无法确认。

目前有 60 个不同的 CustomerIds（比如说 1-60）的分区，每个分区都有一个文件组和文件。我已经为新客户 (61-68) 创建了新的文件组和文件，并且需要对该表中已有的现有数据进行分区。因此，表中已有的数据（customerId 为 61-68）需要移动到单独的分区中。

我尝试使用分区向导，但找不到我想要的选项。据我所知，我需要运行类似：

ALTER PARTITION SCHEME PS_Details  NEXT USED Filegroup61
ALTER PARTITION FUNCTION PF_Details ()  SPLIT RANGE (61)

我正在寻找有人为我指出正确的方向，以实现此类事情的最佳实践。我是否将数据从表中取出，放入存储在 Filegroup61 中的新表中，然后切换分区？不太确定如何解决这个问题。

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    拆分非空分区需要大约 4 倍于正常 DML 的日志记录。假设表和索引是对齐的，您可以通过创建具有原始边界的分区临时表来避免对大型表进行过多的日志记录。然后切换入问题分区，拆分原函数的空分区，重新分区暂存表，将修复的分区切换回。下面是一个示例脚本。如果您使用RANGE LEFT分区函数并指定您的实际文件组，则需要对此进行调整。

    --create staging partition function and scheme
--with same boundaries and filegroups as original
CREATE PARTITION FUNCTION PF_CustomerId_Staging (int)
    AS RANGE RIGHT FOR VALUES();
ALTER PARTITION FUNCTION PF_CustomerId_Staging()
    SPLIT RANGE(NULL);
DECLARE @CustomerId int = 0;
WHILE @CustomerId <= 60
BEGIN
    ALTER PARTITION FUNCTION PF_CustomerId_Staging()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
CREATE PARTITION SCHEME PS_CustomerId_Staging
    AS PARTITION PF_CustomerId_Staging ALL TO ([PRIMARY]);
GO

--create staging table with same schema
CREATE TABLE dbo.CustomerData_Staging(
      CustomerId int NOT NULL
    , CustomerDataId int IDENTITY NOT NULL
    , CustomerData int NOT NULL
    , CONSTRAINT PK_CustomerData_Staging PRIMARY KEY CLUSTERED (CustomerId, CustomerDataId)
        ON PS_CustomerId_Staging(CustomerId)
    );
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData) ON PS_CustomerId_Staging(CustomerId);
GO

--switch partition with multiple customers to staging table
ALTER TABLE dbo.CustomerData
SWITCH PARTITION $PARTITION.PF_CustomerId(60)
TO dbo.CustomerData_Staging PARTITION $PARTITION.PF_CustomerId_Staging(60);

--split original partition function (empty partitions)
DECLARE @CustomerId int = 61;
WHILE @CustomerId <= 68
BEGIN
    ALTER PARTITION SCHEME PS_CustomerId NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION PF_CustomerId()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--repartition staging table and indexes with same function/scheme
CREATE UNIQUE CLUSTERED INDEX PK_CustomerData_Staging ON dbo.CustomerData_Staging(CustomerId, CustomerDataId)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);

--switch remediated partitions back into main table
DECLARE @CustomerId int = 60;
WHILE @CustomerId <= 68
BEGIN
    ALTER TABLE dbo.CustomerData_Staging
        SWITCH PARTITION $PARTITION.PF_CustomerId(@CustomerId)
        TO dbo.CustomerData PARTITION $PARTITION.PF_CustomerId(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--drop staging objects
DROP TABLE dbo.CustomerData_Staging;
DROP PARTITION SCHEME PS_CustomerId_Staging;
DROP PARTITION FUNCTION PF_CustomerId_Staging;
GO
    • 3

  2. 未来，分区表的最佳实践建议您需要在表的左侧和右侧保留空分区，以便您可以切换数据，而不是拆分已经包含数据的分区。

    从这个链接，这里是来自@GailShaw（微软认证大师）关于这个主题的引述

    http://www.sqlservercentral.com/Forums/Topic1646905-391-1.aspx

    不幸的是，如果您要拆分分区并且现有数据属于新分区，您将不得不接受这需要时间，并且您需要确保有足够的日志空间供 SQL 移动数据。
    或者，您可以根据需要创建一个带有分区的新表并复制数据。这也需要时间，但您可以分批进行以减少日志影响。无论哪种方式，数据都必须移动，无法创建分区，也无法自动移动符合新分区条件的数据。

    Gail Shaw 微软认证大师：SQL Server

    • 1

相关问题