34. SQL - 分区表

cxin

　　分区表概述：
　　大凡在应用系统和数据库系统中行走江湖多年的朋友，都会面临数据统计、分析以及归档的问题，企业信息化进程加速了各种数据的极具增长，商务智能(BI)的出现和实施着实给信息工作者和决策者带来了绝妙的体验，但从 OLTP 向 OLAP系统加载数据是很头疼的事，常常需要数分钟或数小时,解决这一问题的技术之一便是分区表，一旦实施了分区表，这样的操作往往只需几秒钟，太让人兴奋了。而大型表或索引经过分区后更容易进行管理，因为这样可以快速高效地管理和访问数据子集，同时维护数据集合的完整性。分区表的数据分布于一个数据库中的多个文件组单元中，数据是按水平方式分区的（数据分区的多种方式会在分区表前传中阐述），因此一个表的某些行映射到某个分区，而另外一些行映射到另外某个分区，以此类推。当对数据进行查询或更新时，表将被视为单个逻辑实体，所以在数据访问层你会感觉和访问普通表一样，而好处就在于可以查询想要的某个分区，而不必扫描整个表。有一点必须明白，单个表的所有分区都必须位于同一个数据库中。
　　分区表支持和标准表相关的所有属性和功能，包括约束、默认值、标识和时间戳值以及触发器等。决定是否实现分区主要取决于表当前的大小或将来的大小以及对表执行查询和维护操作的完善程度。
　　通常，如果某个大型表同时满足下列两个条件，则可能适于进行分区：
　　○1 、该表包含或将包含以多种不同方式使用的大量数据
　　○2 、维护开销超过了预定义的维护期
　　例如，
　　如果对当前年份或当前月份的数据主要执行SELECT 、INSERT、UPDATE 和DELETE 操作，而对以前年份或以前月份的数据主要执行SELECT 查询，则如果按年份或月份对表进行分区，表的管理要容易些，因为此时对表的维护操作只针对一个数据子集。如果该表没有分区，那么就需要对整个数据集执行这些操作，这样就会消耗大量资源。所以常常根据日期和分类对表进行分区，当然利用某个标识列ID 也是很好的选择。例如，电子商务数据库的某张表可能包含了近6 年的数据，但是只定期访问本年度或某个月的数据，那么就可以按年份或月份分区，而另外一张表包含了近几十种类型商品的订单，那么此时可以为每种类型商品分一个区。
　　一般而言，衡量大型表是以数据为标准的，但对于适合分区的大型表，衡量大型表更重要的是对数据访问的性能，如果对于某些表的访问和维护有较严重的性能问题，就可以视为大型表，就应该考虑通过更好的设计和分区来解决性能问题。
　　创建分区表必须经过如下三个步骤：
　　○1 、 创建分区函数
　　○2 、 创建映射到分区函数的分区方案
　　○3 、 创建使用该分区方案的分区表
　　1)、分区函数：
　　分区函数是数据库中的一个独立对象，它将表的行映射到一组分区，所以分区函数解决的是HOW 的问题，即表如何分区的问题。创建分区函数时，必须指明数据分区的边界点以及分区依据列，这样便知道如何对表或索引进行分区。分区函数的创建语法如下：
　　CREATE PARTITION FUNCTION partition_function_name(input_parameter_type)
　　AS RANGE [ LEFT | RIGHT]
　　FOR VALUES ( [boundary_value [ ,...n ] ] )
　　[ ; ]
　　2)、分区方案
　　对表和索引进行分区的第二步是创建分区方案。分区方案定义了一个特定的分区函数将使用的物理存储结构（其实就是文件组），或者说是分区方案将分区函数生成的分区映射到我们定义的一组文件组。所以分区方案解决的是Where 的问题，即表的各个分区在哪里存储的问题。
　　分区方案的创建语法如下：
　　CREATE PARTITION SCHEMEpartition_scheme_name
　　AS PARTITION partition_function_name
　　[ ALL ] TO ( { file_group_name|[PRIMARY ] } [ ,...n ] )
　　[ ; ]
　　分区方案例子1：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将每个分区映射到不同文件组。代码如下：
　　create partition function MyPF1(int)
　　as range left
　　for values(500000,1000000,1500000)
　　go
　　create partition scheme MyPS1
　　as partition MyPF1
　　to (fg1, fg2, fg3, fg4)
　　分区方案例子2：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将多个分区映射到同一个文件组。代码如下：
　　create partition function MyPF2(int)
　　as range left
　　for values(500000,1000000,1500000)
　　go
　　create partition scheme MyPS2
　　as partition MyPF2
　　to (fg1, fg1, fg1, fg2)
　　分区方案例子3：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将所有分区映射到同一个文件组。代码如下：
　　create partition function MyPF3 (int)
　　as range left
　　for values(500000,1000000,1500000)
　　go
　　create partition scheme MyPS3
　　as partition MyPF3
　　all to (fg1)
　　分区方案例子4：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案指定了“NEXT USED”文件组。代码如下：
　　create partition function MyPF4(int)
　　as range left
　　for values(500000,1000000,1500000) --4个分区
　　go
　　create partition scheme MyPS4
　　as partition MyPF4
　　to (fg1, fg2, fg3, fg4, fg5) --5 个文件组
　　那么文件组fg5 将自动被标记为“NEXT USED”文件组。
　　分区方案例子5：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案指定了“[primary]”文件组。代码如下：
　　create partition function MyPF5(datetime)
　　range right
　　for values('2008/01/01', '2009/01/01')
　　go
　　create partition scheme MyPS5
　　as partition MyPF5
　　to([primary], fg1, fg2)
　　最后必须明白一点，一张表最多只能有1000 个分区。
　　3)、分区表
　　在分区函数和分区方案创建完成后，创建分区表的准备工作已经完成。我们看一个完整的例子，代码如下：
　　--创建分区函数
　　create partition function MyPF(datetime)
　　range right
　　for values('2007-1-1', '2008-1-1')
　　go
　　--创建分区方案
　　create partition scheme MyPS
　　as partition MyPF
　　to(fg1, fg2, fg3)
　　go
　　--创建分区表
　　create table orders
　　(

　　OrderID int>　　OrderDate datetime,
　　CustID varchar(10)
　　)
　　on MyPS(OrderDate)
　　4)、DEMO1：新建一个表，写入100万条记录，要求