SQL Server 2005 分区表实践——建立分区表(partition table)

转载 2011年01月09日 15:34:00

SQL Server 2005 分区表实践——建立分区表(partition table)

[作/译者]:鹏城万里    [日期]:2008-07-11    [来源]:本站原创    [查看]: 2159

【鹏城万里】 发表于 www.sqlstudy.com

SQL Server 2005 分区表实践——建立分区表(partition table)

问题:有一个订单表 Orders,要转换成分区表,以订单日期 OrderDate 为分区列,目前含有订单日期为 1996-07-04 ~ 1998-05-06 的数据。可以在 SQL Server 2000 Northwind 数据库中找到 Orders 表,下面是简化了的表结构:

create table dbo.Orders
(
   OrderID     int          not null
  ,CustomerID  varchar(10)  not null
  ,EmployeeID  int          not null
  ,OrderDate   datetime     not null
  
  ,constraint PK_Orders primary key noclustered (OrderID, CustomerID)
)
go

create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go
1. 创建分区函数(partition function)

在创建分区函数的时候,我一般这样命名分区函数:'PF' + Table Name + 分区字段名 + 'Range',例如:'PF_Orders_OrderDateRange',如果字段名较长的话,则可以省略去,可以这样命名: 'PF_Orders_DateRange'

create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go

分区函数 'PF_Orders_OrderDateRange' 有 3 个边界值('1997-01-01', '1998-01-01', '1999-01-01'),这三个边界值(boundary value)组成了 4 个分区(partition),并且 “range right” 表明边界值属于右边的分区。下面使用存储过程 dbo.sp_show_partition_range 来查看分区范围:

exec dbo.sp_show_partition_range @partition_function = 'PF_Orders_OrderDateRange'
partition_function        partition   minval                    value     maxval
------------------------- ----------- ------------------------- --------  ------------------------
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

每个分区的最大值和最小值,一清二楚。获得 dbo.sp_show_partition_range 代码。

2. 创建分区方案(partition scheme)

分区方案定义了,分区表或者分区索引的每个分区的数据存放在哪个文件组上。试想,如果没有分区方案的话,如何实现把分区表中不同的分区存放在不同的文件组上?我们可以看下创建普通数据表的语法:

create table [table name] ... on [filegroup]

显然,普通表整个表的数据,只能存放在同一个文件组上。为了实现分区机制,才引入了分区方案这个概念。每个分区表只属于一个方案(scheme),因此分区方案可以这样命名:'PS' + Table Name

create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go

分区方案 PS_Orders 规定了,把分区表的 4 个分区分别存放在主文件组 primary 上。分区方案中指定的文件组数目,不能少于分区函数中划定的分区数目;但可以多于分区函数中划定的分区数目。多出的第 1 个文件组用来指定当分区表增加分区时,下一个分区所使用的文件组;多出的其他文件组将被忽略。

下面的例子,分区方案指定了 5 个文件组(多出了 1 个文件组)。

drop partition scheme PS_Orders
go

create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary],[primary], [primary],[primary])
go
Partition scheme 'PS_Orders' has been created successfully.
'PRIMARY' is marked as the next used filegroup in partition scheme 'PS_Orders'.

下面的例子,分区方案指定了 7 个文件组(多出了 3 个文件组)。

drop partition scheme PS_Orders
go

create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary],[primary], [primary],[primary],[primary],[primary])
go
Partition scheme 'PS_Orders' has been created successfully.
'PRIMARY' is marked as the next used filegroup in partition scheme 'PS_Orders'.
2 filegroup specified after the next used filegroup are ignored.

如果分区表所有的分区都将分配在同一个文件组 [primary] 上,那么可以使用下面更简洁的方法:

drop partition scheme PS_Orders
go

create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
all to ([primary])
go
3. 创建分区表(partition table)

创建了分区函数和分区方案,准备工作做完了,现在终于可以开始创建分区表了。创建分区表和普通表的语法大致相同,不同之处:普通表需要指定所存放的文件组,分区表需要指定分区方案。

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

根据订单表 Orders 查询时经常使用 OrderDate 范围条件来查询的特点,我们最好在 Orders.OrderDate 列上建立聚集索引(clustered index)。为了便于进行分区切换(partition swtich),大多数情况下,建议在分区表上建立分区索引。下面建立聚集分区索引:

create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
on PS_Orders(OrderDate)

如果没有指定 “on PS_Orders(OrderDate)”,默认建立的聚集索引和分区表的分区方案相同。

另外 Orders 分区表需要在(OrderID, CustomerID)上建立主键。我们知道主键实际上是个唯一索引,但分区表在建立唯一索引(分区索引)的时候,分区列必须是唯一索引的一部分。为什么要这样子呢?因为 SQL Server 不但要保证索引在各个分区是唯一的,还要保证在整个表中是唯一的。

alter table dbo.Orders add constraint PK_Orders primary key (OrderID, CustomerID, OrderDate)

如果不指定 OrderDate 的话,则会出现错误:1908

Msg 1908, Level 16, State 1, Line 1
Column '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 1
Could not create constraint. See previous errors.

查看分区表 Orders 上的索引:

exec sp_helpindex 'dbo.Orders'
index_name            index_description                                      index_keys
--------------------- ------------------------------------------------------ ------------------------------
IXC_Orders_OrderDate  clustered located on PS_Orders                         OrderDate
PK_Orders             nonclustered, unique, primary key located on PS_Orders OrderID, CustomerID, OrderDate
4. 向分区表中填充数据
insert into dbo.Orders
select OrderID, CustomerID, EmployeeID, OrderDate
  from dbo.Orders_From_SQL2000_Northwind

数据表 dbo.Orders_From_SQL2000_Northwind,是从 SQL Server 2000 中 Northwind.Orders 迁移过来的。

5. 查看分区表各分区数据情况(数据行数,最大最小 OrderDate 值)
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
partition   rows        minval                  maxval
----------- ----------- ----------------------- -----------------------
1           152         1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
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

(3 row(s) affected)

从以上结果集中可以看出:分区表 Orders 的 3 个分区中已经填入了数据。这里要注意 $partition.partition_function_name(expression) 这个函数,或许像我一样:一辈子没见过这样的函数:)

相关文章推荐

SQL Server 2008-建立分区表(Table Partition)

数据库结构和索引的是否合理在很大程度上影响了数据库的性能,但是随着数据库信息负载的增大,对数据库的性能也发生了很大的影响。可能我们的数据库在一开始有着很高的性能,但是随着数据存储量的急速增长—例如订单...

SQL Server 2008-建立分区表(Table Partition)

数据库结构和索引的是否合理在很大程度上影响了数据库的性能,但是随着数据库信息负载的增大,对数据库的性能也发生了很大的影响。可能我们的数据库在一开始有着很高的性能,但是随着数据存储量的急速增长—例如订单...

SQL Server 2005中的分区表(五):添加一个分区

所谓天下大事,分久必合,合久必分,对于分区表而言也一样。前面我们介绍过如何删除(合并)分区表中的一个分区,下面我们介绍一下如何为分区表添加一个分区。     为分区表添加一个分区,这种情况是时常会 发...

SQL server 2005 切换分区表

SQL server 2005 切换分区表 在日常工作中经常需要用到分区表切换的问题,笔者在此列出几种常见的分区表切换的问题,供参考。 一、分区表的切换无外乎以下三种形式: 1.将一个分区中的数...

SQL Server 2005 中的分区表和索引原理

摘要 :SQL Server 2005 中基于表的分区功能为简化分区表的创建和维护过程提供了灵活性和更好的性能。追溯从逻辑分区表和手动分区表的功能到最新分区功能的发展历程,探索为什么、 何时以...

SQL Server 2005 中的分区表和索引

SQL Server 2005 发布日期 : 3/24/2005 | 更新日期 : 3/24/2005 Kimberly L. Tripp SQLskills.com 的创始人 适用于:...

SQL Server 2005中的分区表(五):添加一个分区

所谓天下大事,分久必合,合久必分,对于分区表而言也一样。前面我们介绍过如何删除(合并)分区表中的一个分区,下面我们介绍一下如何为分区表添加一个分区。      为分区表添加一个分区,这种情况是时常会...

SQL Server 2005 中的分区表和索引 --MSDN

SQL Server 2005 中的分区表和索引 SQL Server 2005 发布日期 : 3/24/2005 | 更新日期 : 3/24/2005 Kimberl...

SQL Server 2005中的分区表

  • 2015-10-19 20:17
  • 1.13MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)