分布式-DBA-创建及使用分区表.doc

分布式 DBA: 创建和使用分区表
表分区是一种数据组织方案，它根据一列或多列中的值把表数据划分为多个称为数据分区 的存储对象。分区的表能够包含的数据比普通的表多得多；另外，通过利用称为分区消除 的过程，针对分区表运行的查询通常比针对非分区表运行时执行速度更快，需要的磁盘 I/O 更少。（DB2 优化器能够感知分区，在执行查询时只扫描相关的数据分区。）
　　在这篇专栏文章中，我将讨论如何创建分区表，解释分区表的定义如何决定各个记录的存储位置。
　　范围分区表
　　数据分区也称为范围（当前 DB2 只支持范围分区方案），一个数据分区包含一个行子集，这些行存储在与表中其他行集不同的地方。不同的数据分区可以驻留在不同的表空间中，也可以驻留在相同的表空间中。CREATE TABLE 语句的 PARTITION BY 子句中提供的信息决定表数据的分区方式。这个可选子句的语法取决于是希望让 DB2 在指定的表数据范围内均匀地生成每个分区范围，还是要手工控制每个范围的边界。对于给定的表数据范围自动生成分区的语法是：
PARTITION　BY　<RANGE>　
　([ColumnName]　<NULLS　LAST　|　NULLS　FIRST>　,...)　
　(　
　STARTING　<FROM>　[Start　|　MINVALUE　|　MAXVALUE]　|　
　　STARTING　<FROM>　([Start　|　MINVALUE　|　MAXVALUE]　,...)　
　<INCLUSIVE　|　EXCLUSIVE>　
　ENDING　<AT>　[End　|　MINVALUE　|　MAXVALUE]　|　
　　ENDING　<AT>　([End　|　MINVALUE　|　MAXVALUE]　,...)　
　<INCLUSIVE　|　EXCLUSIVE>　
　EVERY　<(>[Constant]　<DurationLabel>　<)>　
　,...)　
手工指定分区的语法是：
PARTITION　BY　<RANGE>　　
　　([ColumnName]　<NULLS　LAST　|　NULLS　FIRST>　,...)　
　　(　
　　<PARTITION　[PartitionName]>　
　STARTING　<FROM>　[Start　|　MINVALUE　|　MAXVALUE]　|　
　　STARTING　<FROM>　([Start　|　MINVALUE　|　MAXVALUE]　,...)　
　<INCLUSIVE　|　EXCLUSIVE>　
　ENDING　<AT>　[End　|　MINVALUE　|　MAXVALUE]　|　
　　ENDING　<AT>　([End　|　MINVALUE　|　MAXVALUE]　,...)　
　<INCLUSIVE　|　EXCLUSIVE>　
　　<IN　[TSName]>　
　　<INDEX　IN　[IndexTSName]>　
　　<LONG　IN　[LongTSName]>　
　　其中：
　　ColumnName：用名称指定一个或多个列（最多 16 列），这些列的值用来决定数据行应该存储在哪个数据分区中。（指定的列组成表的分区键 — 参见边栏 “选择表分区键”。）数据类型为 LONG VARCHAR、LONG VARGRAPHIC、BLOB、CLOB、DBCLOB、XML、基于这些数据类型的 distinct 类型和结构化数据类型的列都不能作为数据分区键的组成部分。
　　PartitionName：指定分配给要创建的数据分区的惟一名称。
　　Start：指定每个数据分区的范围下限。
　　End：指定每个数据分区的范围上限。
　　Constant：在使用语法的自动生成形式时，指定每个数据分区范围的宽度。从 STARTING FROM 值开始创建数据分区，数据分区的范围内包含指定数量的值。支持这种语法的条件是分区键由单一列组成，这一列的数据类型是数字、日期、时间或时间戳。
DurationLabel：如果分区键列的数据类型是日期、时间或时间戳，这个参数指定与 Constant 值相关的时间单位。这个参数的有效值是：YEAR、YEARS、MONTH、MONTHS、DAY、DAYS、HOUR、HOURS、MINUTE、MINUTES、SECOND、SECONDS、MICROSECOND 和 MICROSECONDS。
　　TSName：指定存储每个数据分区的表空间。
　　IndexTSName：指定存储每个数据分区的分区索引的表空间。
　　LongTSName：指定存储长列的值的表空间。
　　注意：尖括号 (< >) 中的参数是可选的；方括号 ([ ]) 中的参数或选项是必需的，必须提供它们；逗号后面跟着省略号 (...) 表示前面的参数可以重复出现多次。
　　因此，如果希望创建一个名为 SALES 的分区表，把每个季度的数据存储在单独的分区中，每个分区驻留在不同的表空间中，那么可以执行以下 CREATE TABLE 语句：
CREATE　TABLE　sales　
　(sales_date　　DATE,　
　sales_amt　　　NUMERIC(5,2))　
　IN　tbsp0,　tbsp1,　tbsp2,　tbsp3　
　PARTITION　BY　RANGE　(sales_date)　
　　(STARTING　'1/1/2010'　ENDING　'12/31/2010'　
　　EVERY　3　MONTHS)　
　　图 1 说明生成的表是什么样的。
图 1. 一个简单分区表中存储的数据

　　另一方面，如果希望创建一个名为 INVENTORY 的分区表，把物品编号在 1 到 100 之间的行存储在一个表空间中的一个分区中，把编号在 101 到 200 之间的行存储在另一个表空间中的另一个分区中，以此类推，那么可以执行下面的 SQL 语句：
CREATE　TABLE　inventory　
　　(item_no　INT,　
　　desc　VARCHAR(20))　
PARTITION　BY　(item_no　NULLS　FIRST)　
(PARTITION　PRODUCE　STARTING　MINVALUE　ENDING　100　IN　tbsp0,　
PARTITION　DAIRY　　STARTING　　　　101　ENDING　200　IN　tbsp1,　
PARTITION　BAKERY　　STARTING　　　　201　ENDING　300　IN　tbsp2,　
PARTITION　MEAT　　　STARTING　　　　301　ENDING　400　IN　tbsp3)　
　　选择表分区键
　　选择有效的表分区键列对于发挥表分区的优势非常重要。最有效的表分区键列是有利于分区消除的列。例如，如果通常按日期查询表中的记录，那么应该用日期或时间列进行表分区。
　　同样，如果希望随着时间的推移把表数据的一部分删除或存档，应该根据期望的记录存档方式进行表分区。例如，如果希望把三年前的所有数据都存档，应该按周、月或季度进行表分区，这样就可以分别在每周、每月或每季度末删除一个老分区。
　　对于这个示例，ITEM_NO 值在 1 到 100 之间的行将存储在名为 PRODUCE 的分区中（这个分区的数据写到表空间 TBSP0 中），ITEM_NO 值在 101 到 200 之间的行将存储在名为 DAIRY 的分区中（这个分区的数据写到表空间 TBSP1 中），依次类推；ITEM_NO 值为 NULL 的行将存储在 PRODUCE 分区中。
　　一定要注意，当指定 NULLS FIRST 选项时，第一个分区必须从 MINVALUE 开始。（同样，如果使用 NULLS LAST 选项，最后一个分区必须结束于 MAXVALUE。）否则，在插入分区键列为 NULL 值的记录时，会产生 “data out of bounds” 错误。另外，每个分区的数据、索引和长列数据可以放在不同的表空间中。如果不为索引或长列指定表空间，分区索引和长列数据会存储在与数据相同的表空间中。
　　在默认情况下，范围包含边界本身。要想防止在某一分区中存储特定的记录，可以用 EXCLUSIVE 选项创建范围。例如：
CREATE　TABLE　sales　
　(sales_date　DATE,　
　sales_amt　NUMERIC(5,2))　
IN　tbsp0,　tbsp1,　tbsp2,　tbsp3　
PARTITION　BY　RANGE　(sales_date)　
(STARTING　'1/1/2010'　ENDING　'3/31/2010'　EXCLUSIVE,　　
STARTING　'3/31/2010'　ENDING　'6/30/2010'　EXCLUSIVE,　　
STARTING　'6/30/2010'　ENDING　'9/30/2010'　EXCLUSIVE,　　
STARTING　'9/30/2010'　ENDING　'12/31/2010')　
　　在这个示例中，销售日期为 3/31/2010 的记录不会存储在表空间 TBSP0 中，而是存储在表空间 TBSP1 中。
　　当在分区表中插入行时，会根据键值及其所处的范围自动地把它放到适当的数据分区中。如果键值不处于表的任何范围内，插入操作就会失败并产生一个错误。
　　轻松地移入和移出数据
　　使用分区表的另一个优点是，可以轻松地在表中添加新数据（作为新的数据分区），同时可以轻松地删除并存档老数据。在下一篇专栏文章中，我将讲解添加（移入）和删除（移出）数据分区的过程。还要讨论 DB2 9.7 中的改进如何大大加快移入和移出数据分区的速度，减少这些操作产生的干扰。