BIWORK 分区表滑动窗口阅读与实践笔记
2012-12-07 18:07 BIWORK 阅读(696) 评论(0) 编辑 收藏 举报基于上一篇 http://www.cnblogs.com/biwork/archive/2012/12/07/BIWORK_Table_Partition_Notes.html 继续了解 Sliding Window 滑动窗口的处理
Note: 示例中使用到了SQL Server 2000的Demo Database, 可以从此链接中下载
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654
/****************************************************************
-- Order 分区表
******************************************************************/
IF OBJECT_ID('dbo.Orders')IS NOT NULL
DROP TABLE Orders
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_Orders')
DROP PARTITION SCHEME PS_Orders
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PF_Orders_OrderDateRange')
DROP PARTITION FUNCTION PF_Orders_OrderDateRange
GO
-- 创建分区函数
CREATE PARTITION FUNCTION PF_Orders_OrderDateRange(DATETIME)
AS
RANGE RIGHT FOR VALUES
(
'1996-01-01',
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
GO
-- 创建分区方案
CREATE PARTITION SCHEME PS_Orders
AS
PARTITION PF_Orders_OrderDateRange
ALL TO ([primary])
GO
-- 创建分区表
CREATE TABLE dbo.Orders
(
OrderID INT NOT NULL,
CustomerID VARCHAR(10) NOT NULL,
EmployeeID INT NOT NULL,
OrderDate DATETIME NOT NULL
)
ON PS_Orders(OrderDate)
GO
CREATE CLUSTERED INDEX IXC_Orders_OrderDate ON dbo.Orders(OrderDate)
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders
PRIMARY KEY(OrderID, CustomerID,OrderDate)
GO
INSERT INTO dbo.Orders
SELECT OrderID,
CustomerID,
EmployeeID,
OrderDate
FROM Northwind.dbo.Orders
GO
/****************************************************************
*** Order Archive 归档分区表,归档那些不经常查询不活跃的历史数据
*** 本示例中需要归档的数据1996年和年
******************************************************************/
IF OBJECT_ID('dbo.OrdersArchive')IS NOT NULL
DROP TABLE OrdersArchive
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_OrdersArchive')
DROP PARTITION SCHEME PS_OrdersArchive
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PF_OrdersArchive_OrderDateRange')
DROP PARTITION FUNCTION PF_OrdersArchive_OrderDateRange
GO
-- 创建分区函数
CREATE PARTITION FUNCTION PF_OrdersArchive_OrderDateRange(DATETIME)
AS
RANGE RIGHT FOR VALUES
(
'1996-01-01',
'1997-01-01'
)
GO
-- 创建分区方案
CREATE PARTITION SCHEME PS_OrdersArchive
AS
PARTITION PF_OrdersArchive_OrderDateRange
ALL TO ([primary])
GO
-- 创建分区表
CREATE TABLE dbo.OrdersArchive
(
OrderID INT NOT NULL,
CustomerID VARCHAR(10) NOT NULL,
EmployeeID INT NOT NULL,
OrderDate DATETIME NOT NULL
)
ON PS_OrdersArchive(OrderDate)
GO
CREATE CLUSTERED INDEX IXC_OrdersArchive_OrderDate ON dbo.OrdersArchive(OrderDate)
GO
ALTER TABLE dbo.OrdersArchive ADD CONSTRAINT PK_OrdersArchive
PRIMARY KEY(OrderID, CustomerID,OrderDate)
GO
/************************************************************************
查看分区表分区函数的分区范围
Orders -
PF_Orders_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 5 1999-01-01 00:00:00.000 <= val < NULL
OrdersArchive
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < NULL
************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.Orders'
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
GO
/***************************************************************************
查询源分区表结果1996年以前的数据没有,所以分区是空的
2 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
3 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
4 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.Orders
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/***************************************************************************
查询存档表结果 没有数据
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.OrdersArchive
GROUP BY $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate)
ORDER BY PARTITION
/*************************************************************************
运用滑动窗口机制,把分区表Orders 分区数据迁移入OrdersArchive
窗口滑动的步骤:
1. 在OrdersArchive 分区表增加一个空闲分区
2. 移动Orders 一个分区到相应的OrdersArchive 分区
3. 删除Orders 中的空闲分区
移动订单日期为1996 年的分区数据
**************************************************************************/
-- 为OrderArchive 分区表的新增分区指定存放位置
ALTER PARTITION SCHEME PS_OrdersArchive NEXT USED [PRIMARY]
GO
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
在Split之前OrdersArchive 的分区情况
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
-- 在OrderArchive 新增一个分区(用来存放1997 年数据)
ALTER PARTITION FUNCTION PF_OrdersArchive_OrderDateRange()
SPLIT RANGE('1998-01-01')
GO
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
在Split之后OrdersArchive 的分区情况
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
-- 移动Orders 1996 年数据到OrderArchive
ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersArchive PARTITION 2
GO
/***************************************************************************
查询源分区表结果1996年位于分区 已经被Switch 到OrderArchive的分区了
所以不会再有分区的数据
3 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
4 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.Orders
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/***************************************************************************
查询存档表结果 2分区存储了年的数据是从Orders 表的分区Switch 过来的
2 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.OrdersArchive
GROUP BY $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate)
ORDER BY PARTITION
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
在Switch后OrdersArchive 的分区情况
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.Orders'
/***************************************************************************
在Switch后Orders 的分区情况
PF_Orders_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 5 1999-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
/****************************************************************************
-- 合并Orders 空闲分区(1996 年数据)
*****************************************************************************/
ALTER PARTITION FUNCTION PF_Orders_OrderDateRange()
MERGE RANGE('1996-01-01')
GO
EXEC dbo.sp_show_partition_range 'dbo.Orders'
/****************************************************************************
-- 合并Orders 空闲分区(1996 年数据)后, Orders 分区情况
-- 之前分区相当于被合并到分区中成为新的分区
PF_Orders_OrderDateRange 1 NULL <= val < 1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1999-01-01 00:00:00.000 <= val < NULL
*****************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
-- 合并Orders 空闲分区(1996 年数据)后, OrdersArchive 分区情况没有影响
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.Orders
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/***************************************************************************
查询上面源分区表结果现在位于分区的是年的数据,因为之前的分区已经给
合并了,所以分区的边界变成了 NULL < Val <= 1997
2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.OrdersArchive
GROUP BY $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate)
ORDER BY PARTITION
/***************************************************************************
查询存档表结果
2 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
***************************************************************************/
/*************************************************************************
运用滑动窗口机制,把分区表Orders 分区数据迁移入OrdersArchive
窗口滑动的步骤:
1. 在OrdersArchive 分区表增加一个空闲分区
2. 移动Orders 一个分区到相应的OrdersArchive 分区
3. 删除Orders 中的空闲分区
练习移动订单日期为1997 年的分区数据
1. 在为OrdersArchive 分区表增加一个空闲分区的步骤
a. 为OrderArchive 分区表的新增分区指定存放位置
b. 利用Split来分隔分区即新增了分区
**************************************************************************/
-- 步骤一:增加新的空闲分区
-- a. 指定新分区的存放位置,实则修改分区方案
ALTER PARTITION SCHEME PS_OrdersArchive NEXT USED [PRIMARY]
GO
-- b. 新增分区,现在最大的已知的分区键列值是
ALTER PARTITION FUNCTION PF_OrdersArchive_OrderDateRange()
SPLIT RANGE('1999-01-01')
-- 步骤二:移动Orders 一个分区到相应的OrdersArchive 分区
ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersArchive PARTITION 3
-- 步骤三：删除Orders 中的空闲分区
ALTER PARTITION FUNCTION PF_Orders_OrderDateRange()
MERGE RANGE('1997-01-01')
EXEC dbo.sp_show_partition_range 'dbo.Orders'
/****************************************************************************
PF_Orders_OrderDateRange 1 NULL <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1999-01-01 00:00:00.000 <= val < NULL
*****************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 5 1999-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.Orders
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/***************************************************************************
2 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.OrdersArchive
GROUP BY $PARTITION.PF_OrdersArchive_OrderDateRange(OrderDate)
ORDER BY PARTITION
/***************************************************************************
2 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
3 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
***************************************************************************/
/*************************************************************************
运用滑动窗口机制,把分区表Orders 分区数据迁移入OrdersArchive
窗口滑动的步骤:
1. 在OrdersArchive 分区表增加一个空闲分区
2. 移动Orders 一个分区到相应的OrdersArchive 分区
3. 删除Orders 中的空闲分区
练习移动订单日期为1998 年的分区数据
1. 在为OrdersArchive 分区表增加一个空闲分区的步骤
a. 为OrderArchive 分区表的新增分区指定存放位置
b. 利用Split来分隔分区即新增了分区
**************************************************************************/
-- 步骤一:增加新的空闲分区
-- a. 指定新分区的存放位置,实则修改分区方案
ALTER PARTITION SCHEME PS_OrdersArchive NEXT USED [PRIMARY]
-- b. 利用Split来增加新分区
ALTER PARTITION FUNCTION PF_OrdersArchive_OrderDateRange()
SPLIT RANGE('2000-01-01')
-- 步骤二:移动Orders 一个分区到相应的OrdersArchive 空闲分区
ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersArchive PARTITION 4
-- 步骤三:删除Orders 的空闲分区
ALTER PARTITION FUNCTION PF_Orders_OrderDateRange()
MERGE RANGE('1998-01-01')
GO
注 : 已经不记得原博客地址了, 这篇日志是基于别人的分析成果之上加上自己亲自实践, 思考, 重新添加了一些代码和注释. 在注解和结论验证方面按照自己的理解做出了还算比较细致的说明,对刚接触表滑动分区概念的朋友们相信会有很大的帮助. 我也是通过这种方式学习和掌握了表移动分区的一些基础操作步骤和处理方式.
