BIWORK 分区表滑动窗口阅读与实践笔记

基于上一篇  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

注 : 已经不记得原博客地址了, 这篇日志是基于别人的分析成果之上加上自己亲自实践, 思考, 重新添加了一些代码和注释. 在注解和结论验证方面按照自己的理解做出了还算比较细致的说明,对刚接触表滑动分区概念的朋友们相信会有很大的帮助. 我也是通过这种方式学习和掌握了表移动分区的一些基础操作步骤和处理方式.