BIWORK 分区表阅读与实践笔记

BIWORK 分区表阅读与实践笔记2014-03-23 cnblogs BIWORK
/***************************************************************BIWORK 分区表阅读与实践笔记 Note: 示例中使用到了SQL Server 2000的 Demo Database, 可以从此链接中下载 http://www.microsoft.com/en-us/download/confirmation.aspx？id=23654在检查删除Partition Function 以及Partition Scheme 时,要注意Partition Scheme 引用了Partition Function, 所有需要先删除Partition Scheme. 同理,引用了Partition Scheme 的表应该先删除掉.引用关系: TABLE -> PARTITION SCHEME -> PARTITION FUNCTION***************************************************************/IF OBJECT_ID（"dbo.Orders"）IS NOT NULLDROP TABLE OrdersGO IF EXISTS （SELECT * FROM sys.partition_schemes WHERE name = "PS_Orders"）DROP PARTITION SCHEME PS_OrdersGO IF EXISTS （SELECT * FROM sys.partition_functions WHERE name = "PF_Orders_OrderDateRange"）DROP PARTITION FUNCTION PF_Orders_OrderDateRangeGO /***************************************************************1. 如果在不需要对数据库进行物理分组的情况下,比如分区表还是享用同一个文件组,那么应该可以从创建分区函数开始*** 创建分区函数*** 确定分区键列的类型（DATETIME）以及分区的边界值: （""1997-01-01","1998-01-01","1999-01-01""）*** N个边界值确定N+1 个分区*** RIGHT - 第一个分区的所有值都小于VAL < 1997-01-1第二个分区的值范围是1997-01-01 <= VAL < 1998-01-01***************************************************************/CREATE PARTITION FUNCTION PF_Orders_OrderDateRange（DATETIME）ASRANGE RIGHT FOR VALUES（ "1997-01-01", "1998-01-01", "1999-01-01"）GO EXEC dbo.sp_show_partition_range @partition_function = "PF_Orders_OrderDateRange"/***************************************************************显示分区函数的分区情况,PARTITION FUNCTION,PARTITION,MinVal,VALUE,MaxVal PF_Orders_OrderDateRange1NULL <= val <1997-01-01 00:00:00.000PF_Orders_OrderDateRange21997-01-01 00:00:00.000<= val <1998-01-01 00:00:00.000PF_Orders_OrderDateRange31998-01-01 00:00:00.000<= val <1999-01-01 00:00:00.000PF_Orders_OrderDateRange41999-01-01 00:00:00.000<= val <NULL****************************************************************/ /***************************************************************2. 创建了分区函数后,便可以创建分区方案*** 因为在上一个分区函数中有个边界值,4个分区,并且并没有其它的数据库文件组,所以当分区方案应用到具体的分区函数时所有的分区都是指向PRIMARY 文件组***************************************************************/CREATE PARTITION SCHEME PS_OrdersASPARTITION PF_Orders_OrderDateRangeTO （[primary],[primary],[primary],[primary]）GO /***************************************************************3. 创建分区表时要应用分区方案,并提供具体的分区键列ON 分区函数（分区键列）****************************************************************/CREATE TABLE dbo.Orders（ OrderID INT NOT NULL, CustomerIDVARCHAR（10） NOT NULL, EmployeeIDINT NOT NULL, OrderDate DATETIMENOT NULL）ON PS_Orders（OrderDate）GO /******************************************************************4. 在创建分区表后,需要创建聚集分区索引 *** 根据订单表Orders 查询时经常使用OrderDate 范围条件来查询的特点,*** 我们最好在Orders.OrderDate 列上建立聚集索引（clustered index）.*** 为了便于进行分区切换（partition swtich）大多数情况下,建议在分区表上建立分区索引。*******************************************************************/CREATE CLUSTERED INDEX IXC_Orders_OrderDate ON dbo.Orders（OrderDate）GO /*******************************************************************5. 为分区表创建主键 如果主键不包含分区键列,将会出现以下错误信息: Msg 1908, Level 16, State 1, Line 2Column "OrderDate" is partitioning column of the index "PK_Orders".Partition columns for a unique index must be a subset of the index key.Msg 1750, Level 16, State 0, Line 2Could not create constraint. See previous errors. 原因:主键实际上是个唯一索引,但分区表在建立唯一索引（分区索引）的时候,分区列必须是唯一索引的一部分.因为SQL Server 不但要保证索引在各个分区是唯一的,还要保证在整个表中是唯一的.********************************************************************/ALTER TABLE dbo.Orders ADD CONSTRAINT PK_OrdersPRIMARY KEY（OrderID, CustomerID,OrderDate）GO /************************************************************************查看分区表Orders 上的索引: IXC_Orders_OrderDate|clustered located on PS_Orders|OrderDatePK_Orders|nonclustered,unique,primary key located on PS_Orders|OrderID, CustomerID, OrderDate************************************************************************/EXEC sp_helpindex "dbo.Orders" /**********************************************************************6. 从SQL Server 2000 NorthWind 导入测试数据***********************************************************************/INSERT INTO dbo.OrdersSELECT OrderID, CustomerID, EmployeeID, OrderDateFROM Northwind.dbo.Orders /************************************************************************7. 查看分区表各分区数据情况（数据行数，最大最小OrderDate 值）*************************************************************************/SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.OrdersGROUP BY $PARTITION.PF_Orders_OrderDateRange（OrderDate）ORDER BY PARTITIONGO/************************************************************************在目前的测试数据中,并没有大于1999年的数据,所以在上面的查询结果中并没有看到第个分区的信息: PARTITION,ROWS,MinVal,MaxVal11521996-07-04 00:00:00.0001996-12-31 00:00:00.00024081997-01-01 00:00:00.0001997-12-31 00:00:00.00032701998-01-01 00:00:00.0001998-05-06 00:00:00.000*************************************************************************/ -- 插入一条测试数据INSERT INTO dbo.Orders VALUES（11111,"TEST",1,"2000-10-10 10:10:10:100"） -- 再次查询SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.OrdersGROUP BY $PARTITION.PF_Orders_OrderDateRange（OrderDate）ORDER BY PARTITION/************************************************************************查询结果显示了个分区的信息PARTITION,ROWS,MinVal,MaxVal11521996-07-04 00:00:00.0001996-12-31 00:00:00.00024081997-01-01 00:00:00.0001997-12-31 00:00:00.00032701998-01-01 00:00:00.0001998-05-06 00:00:00.000412000-10-10 10:10:10.1002000-10-10 10:10:10.100*************************************************************************/GO /************************************************************************************** 切换分区表的一个分区到普通数据表**************************************** Partition to Table ****************************************/ /*************************************************************************1. 首先建立普通数据表Orders_1998，该表用来存放订单日期为1998 年的所有数据2. 分区到普通表的切换，最好满足以下的前提条件: a. 普通表必须建立在分区表切换分区所在的文件组上ON [PRIMARY] b. 普通表的表结构跟分区表的一致 c. 普通表上的索引要跟分区表一致（聚集索引,非聚集索引） d. 普通表必须是空表，不能有任何数据*************************************************************************/IF OBJECT_ID（"Orders_1998"） IS NOT NULLDROP TABLE Orders_1998GO CREATE TABLE dbo.Orders_1998（ OrderID INT NOT NULL, CustomerIDVARCHAR（10） NOT NULL, EmployeeIDINT NOT NULL, OrderDate DATETIMENOT NULL）ON [PRIMARY]GO -- 添加聚集索引,和分区表一致CREATE CLUSTERED INDEX IXC_Orders1998_OrderDate ON dbo.Orders_1998（OrderDate）GO -- 添加主键,和分区表一致ALTER TABLE dbo.Orders_1998 ADD CONSTRAINT PK_Orders_1998PRIMARY KEY（OrderID,CustomerID,OrderDate）GO /***************************************************************************** 开始切换分区表Orders 第三个分区的数据（1998年的数据）到普通表Orders_1998** 关键字- SWITCH PARTITION [NUMBER] TO [History Table]***************************************************************************/ALTER TABLE dbo.Orders SWITCH PARTITION 3 TO dbo.Orders_1998 /***************************************************************************查询源分区表结果分区号为的数据已经没有了11521996-07-04 00:00:00.0001996-12-31 00:00:00.00024081997-01-01 00:00:00.0001997-12-31 00:00:00.000412000-10-10 10:10:10.1002000-10-10 10:10:10.100***************************************************************************/SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.OrdersGROUP BY $PARTITION.PF_Orders_OrderDateRange（OrderDate）ORDER BY PARTITION /***************************************************************************查询存档表结果32701998-01-01 00:00:00.0001998-05-06 00:00:00.000***************************************************************************/SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.Orders_1998GROUP BY $PARTITION.PF_Orders_OrderDateRange（OrderDate）ORDER BY PARTITION /*************************************************************************************切换普通表数据到分区表的一个分区中**********************************Table to Partition ************************//*************************************************************************上面我们已经把分区表Orders 第三个分区的数据切换到普通表Orders_1998 中了,现在我们再切换回来:**************************************************************************/ALTER TABLE dbo.Orders_1998 SWITCH PARTITION 3 TO dbo.Orders/*************************************************************************错误信息:Msg 4911, Level 16, State 2, Line 1Cannot specify a partitioned table without partition number in ALTER TABLESWITCH statement. The table "SSISDemoDB.dbo.Orders" is partitioned.原因:实际上应该是将dbo.Orders_1998 表中的数据SWITCH 到dbo.Orders 表的Partition 分区中.而不能说是将dbo.Orders_1998 的分区的数据SWITCH 到dbo.Orders 全表中**************************************************************************/ ALTER TABLE dbo.Orders_1998 SWITCH TO dbo.Orders PARTITION 3/*************************************************************************错误信息:Msg 4982, Level 16, State 1, Line 1ALTER TABLE SWITCH statement failed. Check constraints of source table"dbo.Orders_1998" allow values that are not allowed by range defined bypartition 3 on target table "dbo.Orders". 原因:表dbo.Orders 的数据经过分区函数的分区列定义, 各个分区的数据实际上已经经过了数据约束检查,符合分区边界范围（Range）的数据才会录入到各个分区中.但是在历史表/存档表dbo.Orders_1998中的数据实际上是没有边界约束的,比如完全可以手动的插入一条年的数据,这样一来在进行SWITCH时肯定是不会成功的.所以在SWITCH时,先进行了约束性检查,尽管没有不符合规范的数据,但是有潜在的威胁. 所以在SWITCH之前,先为dbo.Orders_1998添加一个检查约束,并再次SWITCH,成功！**************************************************************************/ALTER TABLE dbo.Orders_1998 ADD CONSTRAINT CK_Orders1998_OrderDateCHECK（OrderDate>="1998-01-01" AND OrderDate<"1999-01-01"） ALTER TABLE dbo.Orders_1998 SWITCH TO dbo.Orders PARTITION 3/***************************************************************************查询源分区表结果,分区的数据已经从dbo.Orders_1998 回来了11521996-07-04 00:00:00.0001996-12-31 00:00:00.00024081997-01-01 00:00:00.0001997-12-31 00:00:00.00032701998-01-01 00:00:00.0001998-05-06 00:00:00.000412000-10-10 10:10:10.1002000-10-10 10:10:10.100***************************************************************************/SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.OrdersGROUP BY $PARTITION.PF_Orders_OrderDateRange（OrderDate）ORDER BY PARTITION /***************************************************************************查询存档表结果,没有任何数据,已经成功SWITCH to Orders 表的PARTITION 3***************************************************************************/SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.Orders_1998GROUP BY $PARTITION.PF_Orders_OrderDateRange（OrderDate）ORDER BY PARTITION/****************************************************************************所以在进行存档表的数据向分区表迁移过程中（TABLE TO PARTITION）,相比（PARTITION TO TABLE）多一个条件:普通表必须加上和分区数据范围一致的约束条件.*****************************************************************************/ /*************************************************************************************切换分区表数据到分区表 *********************************************PARTITION TO PARTITION**********************************/ /*************************************************************************-- 新的存档分区表在结构上和源分区表是一致的,包括分区函数和分区方案,但是需要重新创建,不能简单地直接使用dbo.Orders 表上的分区函数和分区方案,因为他们之间有绑定关系. **************************************************************************/IF OBJECT_ID（"OrdersArchive"） IS NOT NULLDROP TABLE OrdersArchiveGO IF EXISTS （SELECT * FROM sys.partition_schemes WHERE name = "PS_OrdersArchive"）DROP PARTITION SCHEME PS_OrdersArchiveGO IF EXISTS （SELECT * FROM sys.partition_functions WHERE name = "PF_OrdersArchive_OrderDateRange"）DROP PARTITION FUNCTION PF_OrdersArchive_OrderDateRangeGO CREATE PARTITION FUNCTION PF_OrdersArchive_OrderDateRange（DATETIME）ASRANGE RIGHT FOR VALUES（ "1997-01-01", "1998-01-01", "1999-01-01"）GO CREATE PARTITION SCHEME PS_OrdersArchiveAS-- 分区Scheme和分区函数绑定了PARTITION PF_OrdersArchive_OrderDateRangeTO （[primary],[primary],[primary],[primary]）GO CREATE TABLE dbo.OrdersArchive（ OrderID INT NOT NULL, CustomerIDVARCHAR（10） NOT NULL, EmployeeIDINT NOT NULL, OrderDate DATETIMENOT NULL）-- 表和分区Scheme绑定了ON PS_OrdersArchive（OrderDate）GO CREATE CLUSTERED INDEX IXC_OrdersArchive_OrderDate ON dbo.OrdersArchive（OrderDate） ALTER TABLE dbo.OrdersArchive ADD CONSTRAINT PK_OrdersArchivePRIMARY KEY（OrderID, CustomerID,OrderDate）GO /*********************************************************************************开始切换分区**********************************************************************************/ALTER TABLE dbo.Orders SWITCH PARTITION 1TO dbo.OrdersArchive PARTITION 1ALTER TABLE dbo.Orders SWITCH PARTITION 2TO dbo.OrdersArchive PARTITION 2ALTER TABLE dbo.Orders SWITCH PARTITION 3TO dbo.OrdersArchive PARTITION 3/***************************************************************************查询源分区表结果,只会有分区的数据412000-10-10 10:10:10.1002000-10-10 10:10:10.100***************************************************************************/SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.OrdersGROUP BY $PARTITION.PF_Orders_OrderDateRange（OrderDate）ORDER BY PARTITION /***************************************************************************查询存档表结果,已经成功转移11521996-07-04 00:00:00.0001996-12-31 00:00:00.00024081997-01-01 00:00:00.0001997-12-31 00:00:00.00032701998-01-01 00:00:00.0001998-05-06 00:00:00.000***************************************************************************/SELECT PARTITION = $PARTITION.PF_OrdersArchive_OrderDateRange（OrderDate）, ROWS= COUNT（*）, MinVal= MIN（OrderDate）, MaxVal= MAX（OrderDate）FROM dbo.OrdersArchiveGROUP BY $PARTITION.PF_OrdersArchive_OrderDateRange（OrderDate）ORDER BY PARTITION /***************************************************************************总结: 分区表分区切换并没有真正去移动数据,而是SQL Server 在系统底层改变了表的元数据。因此分区表分区切换是高效,快速,灵活的.利用分区表的分区切换功能,我们可以快速加载数据到分区表.卸载分区数据到普通表,然后TRUNCATE 普通表,以实现快速删除分区表数据,快速归档不活跃数据到历史表。****************************************************************************/
注 : 已经不记得原博客地址了, 这篇日志是基于别人的分析成果之上加上自己亲自实践, 思考, 重新添加了一些代码和注释. 在注解和结论验证方面按照自己的理解做出了还算比较细致的说明,对刚接触表分区概念的朋友们相信会有很大的帮助. 我也是通过这种方式学习和掌握了表分区的一些基础操作步骤和处理方式.