在依据需求建立好分区表之后,就要实现对分区表的管理维护。主要内容就是两点:
1. 利用滑动窗口方案(Sliding Window Scenario),实现分区表与数据移动中间表的互切。
2. 分区表本身的结构变更管理。
当然以上两点,都是理论上的点。我工作中主要是遇到两种需求:
1. 过时分区数据的快速归档管理:即把某些不活跃分区的数据切到数据归档的表中去。
2. 分区表由于数据增长需要添加新分区来处理数据:即已分区的表它所有的数据都会处于活动状态,需要新的分区来承载新增的数据。
这两种需求将会在下面的代码体现出来。分区表沿用我上一篇中的表Product,但是这里称为Products。因为上一篇中的表,测试系统在用,我不能移动数据,就只好克隆出一张表。
创建一张表结构和主键聚集索引跟Products一模一样的表Products_Tmp(用来做数据移转的中间临时表);这张表还必须跟要移出的分区位于同一个文件组(否则将不能应用Partition Switch进行快速切换)。
首先在新文件组上创建存档表Products_Archive,用于存档后面中间临时表的数据。
USE [master] GO ALTER DATABASE [TEST] ADD FILEGROUP [FG_TEST_Products_Archive] GO ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_TEST_Products_Archive_data_1', FILENAME = N'D:\Data\FG_TEST_Products_Archive_data_1.ndf' , SIZE = 50MB , FILEGROWTH = 10% ) TO FILEGROUP [FG_TEST_Products_Archive] GO USE TEST GO CREATE TABLE [dbo].[Products_Archive]( [ID] [int] NOT NULL, [PName] [nvarchar](100) NULL, [AddDate] [datetime2](3) NULL )ON [FG_TEST_Products_Archive]; GO
接下来,就是移转数据的操作了。封装成存储过程,方便调用。本来是想把导数据也写在里面,可是考虑到生产环境数据量较大,会采用其它的导数据方案,就舍弃了。
CREATE PROCEDURE usp_TransferPartitionData_ForArchive @PartitonNumber INT --要移转分区编号 AS DECLARE @filegroup NVARCHAR(100)=N'', --要移转分区所在文件组 @SQL NVARCHAR(4000)=N'', --创建中间临时表的动态语句 @rangeValue INT; --要移转分区边界值上限 SET @rangeValue=(SELECT CAST(VALUE AS INT) FROM sys.partition_range_values WHERE boundary_id=@rangeValue); --这里的赋值,使用我上篇中的自定义函数fn_GetFileForPartition. SET @filegroup=(SELECT [FILEGROUP_NAME] FROM dbo.fn_GetFileForPartition(N'Sch_Product_ID',@rangeValue)); SET @SQL=N'CREATE TABLE [dbo].[Products_Tmp]( [ID] [int] NOT NULL, [PName] [nvarchar](100) NULL, [AddDate] [datetime2](3) NULL, CONSTRAINT [PK_Products_Tmp] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )ON '+QUOTENAME(@filegroup); IF OBJECT_ID('dbo.Products_Tmp') IS NOT NULL RAISERROR('已经存在数据库对象Products_Tmp!',16,1); ELSE BEGIN --PRINT @SQL; EXEC(@SQL); --把指定分区中的数据切到Products_Tmp ALTER TABLE dbo.Products SWITCH PARTITION @PartitonNumber TO dbo.Products_Tmp; END --因为数据量少又是堆表,直接使用INSERT INTO导入数据。 --如果是大数据量的话可能就会使用BULK+BCP或者SSIS来做。 --INSERT INTO [dbo].[Products_Archive] --([ID],[PName],[AddDate]) -- SELECT [ID],[PName],[AddDate] FROM [dbo].[Products_Tmp]; --DROP TABLE [dbo].[Products_Tmp GO --移转数据,并删除Products_Tmp。 EXEC usp_TransferPartitionData_ForArchive 1;--指定移转第一个分区 --因为数据量少又是堆表,直接使用INSERT INTO导入数据。 --如果是大数据量的话可能就会使用BULK+BCP或者SSIS来做。 INSERT INTO [dbo].[Products_Archive] ([ID],[PName],[AddDate]) SELECT [ID],[PName],[AddDate] FROM [dbo].[Products_Tmp]; DROP TABLE [dbo].[Products_Tmp];
现在旧数据移出去了,但是每天还有新数据进来,最后一个分区越来越大,所以我们要加入新文件组来承接这个分区。但是这里,就不新建文件组,而是使用在上面移转数据时”空”出来的PRIMARY文件组。
首先分区既然空了,就要从分区表中移除它。然后再将其做为新文件组加进分区表。其实下面的代码也可以整合到上面的SP里去的。
DECLARE @rangeValue INT, --要删除分区边界值的上限 @splitValue INT,--要分割给新分区用的边界值下限 @filegroup NVARCHAR(100)=N'', --要合并的分区所属的文件组 @SQL NVARCHAR(4000);--动态语句 --合并空分区,也就是删除分区。 SET @rangeValue=80000; ALTER PARTITION FUNCTION fn_Partition_Product_ID() MERGE RANGE (@rangeValue); --修改架构,添加文件组 SET @filegroup=(SELECT [FILEGROUP_NAME] FROM dbo.fn_GetFileForPartition(N'Sch_Product_ID',80000)); SET @SQL=N'ALTER PARTITION SCHEME Sch_Product_ID NEXT USED '+QUOTENAME(@filegroup)+N';'; --PRINT @SQL; EXEC(@SQL); --增加新分区,来接受新数据 SET @splitValue=380000; ALTER PARTITION FUNCTION fn_Partition_Product_ID() SPLIT RANGE (@splitValue);