本文深入介绍了DB2V9表分区功能，旨在帮助用户理解并应用这一数据组织方案，以提高大表管理效率与查询性能。通过创建示例数据库、表空间与分区表，本文演示了如何使用自动与手动方式创建分区表，并解释了关键概念与最佳实践。
DB2 V9表分区
DB2 V9新增了表分区功能,因此对一些大表,我们在DB2 V9中不再需要分拆成小表,再用UNION ALL视图的方式进行设计,而是直接用分区表实现一些这些功能｡表分区功能是一种数据组织方案,即,表数据根据一个或多个表列中的值分布到多个存储对象(称为数据分区或范围)中｡每个数据分区都是单独存储的｡这些存储对象可以在不同的表空间中,也可以在相同表空间中｡由于表分区是DB2V9新增的功能,所以很多用户对其特性不是很了解,本文将重点介绍DB2 V9表分区功能,并通过实际的例子来帮助大家理解和提高｡
简介
在DB2 V9之前,对一些大表,出于对性能和数据容量限制的考虑,我们通常会把大表分拆成一些小表,再用UNION ALL 视图的方式进行联合起来｡DB2 V9在数据容量方面有了重大突破,DMS 表空间的新缺省类型是“大型”,当使用“大型”DMS表空间时,单表的最大容量限制是16384G(Byte),而不再是以前的512 G(Byte)｡DB2 V9新增了表分区功能,使得我们对大表的性能和数据容量的顾虑都不再存在｡使用表分区,能够创建非常大的表,通过跨多个存储器对象划分表数据,可显著增大表,一个表最多可以有32K个数据分区｡使用表分区分隔数据能够避免扫描不相关的数据,从而提高查询处理性能｡DB2优化器从查询操作中去除不相关的分区｡
表分区功能是一种数据组织方案,根据一个或多个表列中的值将表数据划分到多个称为数据分区或范围的存储对象中｡每个数据分区都是单独存储的｡这些存储器对象可位于不同的表空间和/或相同的表空间中｡跨多个存储器对象对表数据进行分区的能力为数据库管理员提供了更高的可伸缩性和灵活性,同时提高了性能和控制能力｡表分区可大幅度减少管理庞大数据库所需的维护工作,并可有效增加单个表的潜在大小｡表和索引自动重组的新策略选项使您能够更有效地管理DB2服务器对表和索引的自动重组｡表分区使您能够定义表数据的范围,以便单独保存每个范围｡例如,您可基于表中的日期列,按月对表进行分区｡每个范围(称为数据分区)与单个存储器对象对应｡这些存储器对象可位于不同的表空间和/或相同的表空间中｡由于可以对单个数据分区执行管理任务,将很耗时的维护操作分成一系列较小的操作来执行,从而使管理工作更为灵活｡例如,可备份和复原单个数据分区而不是整个表｡DB2 V9新增的表分区功能增强了对索引位置的细化控制,可将索引置于不同表空间并单独管理它们｡使用 ALTER TABLE语句的 ATTACH PARTITION和DETACH PARTITION子句可进行快速及方便的数据转入或转出｡此功能在数据仓库环境中特别有用,在此环境中经常需要装入或删除数据以运行决策支持查询｡能够将表分区与其他数据组织方案组合在一起｡通过将表分区与数据分区功能(DPF)一起使用,可跨数据库分区均匀地分布数据范围,以利用 DPF 的查询内并行性和数据库分区负载均衡功能｡将表分区与多维集群(MDC)一起使用时,能够对同一表扩展数据块中在多个维上具有类似值的行进行分组｡
由于表分区是DB2 V9新增的功能,所以很多用户对其特性不是很了解,本文将重点介绍DB2 V9表分区功能,并通过实际的例子来帮助大家理解和提高｡
我们将按照下列顺序介绍表分区:
1. 创建示例数据库DB2TEST1,在示例数据库上创建一个使用32K页大小的缓冲池,创建3个DMS表空间,用来存放示例分区表的数据,创建3个大型表空间(DMS),用来存放示例分区表的大型数据,创建1个DMS表空间,用来存放示例分区表的索引
2. 使用自动方式创建示例分区表
3. 使用手工方式创建示例分区表
创建示例数据库DB2TEST1
首先我们在WINDOWS XP环境下安装DB2 ESE V9.1,安装完成后,打开DB2CMD环境,创建示例数据库DB2TEST1｡在 DB2 命令窗口中,发出CREATE DATABASE语句,创建示例数据库,具体如清单1所示:
- - 清单1. 创建示例数据库DB2TEST1
C:\> DB2 CREATE DATABASE DB2TEST1 DB20000I CREATEDATABASE命令成功完成｡
这样我们创建了一个示例数据库DB2TEST1｡查看其数据库配置参数,数据库代码页是1386,数据库地域是CN,数据库代码集是GBK,具体如清单2所示:
- - 清单2. 查看示例数据库DB2TEST1配置参数
C:\> db2 get db cfg fordb2test1
数据库 db2test1 的数据库配置
数据库配置发行版级别= 0x0b00
数据库发行版级别= 0x0b00
数据库地域 = CN
数据库代码页= 1386
数据库代码集= GBK
数据库国家/地区代码 = 86
数据库整理顺序= UNIQUE
备用整理顺序 ( ALT_COLLATE ) =
数据库页大小= 4096
. . . . . . . . . . . . . . . . . .
接下来来我们来查看一下示例数据库DB2TEST1上的表空间情况,在DB2CLP窗口中,连上示例数据库DB2TEST1,使用LIST TABLESPACES命令能够看到创建示例数据库时已经默认创建了3个表空间:SYSCATSPACE､TEMPSPACE1､USERSPACE1,其都是使用自动存储管理｡具体如清单3所示:
- - 清单3. 查看示例数据库DB2TEST1表空间情况
C:\> DB2 CONNECT TODB2TEST1
数据库连接信息
数据库服务器= DB2 / NT 9.1.0
SQL 授权标识 =RHETTE
本地数据库别名= DB2TEST1
C:\> db2 list tablespaces show detail
当前数据库的表空间
表空间标识 = 0
名称 = SYSCATSPACE
类型 = 数据库管理空间
内容 = 所有持久数据｡常规表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 8192
可用页数 = 8188
已用页数 = 7924
可用页数 = 264
高水位标记(页) = 7924
页大小(以字节计) = 4096
扩展数据块大小(页) = 4
预取大小(页) = 4
容器数 = 1
表空间标识 = 1
名称 = TEMPSPACE1
类型 = 系统管理空间
内容 = 系统临时数据
状态 = 0x0000
详细解释:
正常
总计页数 = 1
可用页数 = 1
已用页数 = 1
可用页数 = 不适用
高水位标记(页) = 不适用
页大小(以字节计) = 4096
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
表空间标识 = 2
名称 = USERSPACE1
类型 = 数据库管理空间
内容 = 所有持久数据｡大型表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 8192
可用页数 = 8160
已用页数 = 96
可用页数 = 8064
高水位标记(页) = 96
页大小(以字节计) = 4096
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
接下来我们再查看一下示例数据库的缓冲池情况,在DB2CLP窗口中通过查看系统表sysibm.sysbufferpools可以看到,在创建示例数据库时默认创建的的4K页大小缓冲池IBMDEFAULTBP,具体如清单4所示:
--清单 4. 查看默认创建缓冲池情况
C:\> DB2 CONNECT TODB2TEST1
数据库连接信息
数据库服务器= DB2/NT 9.1.0
SQL 授权标识 =RHETTE
本地数据库别名= DB2TEST1
C:\> db2 " selectBPNAME , NPAGES from sysibm.sysbufferpools "
BPNAME NPAGES
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
IBMDEFAULTBP -2
1 条记录已选择｡
当缓冲池启用了自调整功能时,该特定缓冲池的sysibm.sysbufferpools表中的NPAGES字段将设置为 -2｡当自调整功能处于禁用状态时,NPAGES字段将设置为缓冲池的当前大小｡我们可以在清单4中看到,IBMDEFAULTBP的NPAGES 字段将设置为 -2,由此我们可以确定,IBMDEFAULTBP启用了自调整功能｡
下面我们创建32K页大小的示例缓冲池MYBP1,其使用自调整功能(注意其CREATEBUFFERPOOL语句使用了AUTOMATIC),初始大小3.2M,具体如清单5所示:
--清单 5 . 创建使用自动自调整功能的示例缓冲池MYBP1
C:\> db2 create bufferpool mybp1 immediate size 100 automatic pagesize32k
DB20000I SQL命令成功完成｡
接下来我们在示例数据库DB2TEST1中创建3个4K页大小的DMS表空间,用来存放示例分区表的数据,名称分别为TABLESPACE1,TABLESPACE2和TABLESPACE3,在DB2CLP窗口中,发出CREATE TABLESPACE命令,其缓冲池使用数据库默认创建的4K页大小的缓冲池IBMDEFAULTBP,具体如清单6所示:
- - 清单6 . 创建DMS示例表空间
C:\> DB2 CREATEREGULAR TABLESPACE TABLESPACE1 PAGESIZE 4 KMANAGED BY AUTOMATIC STORAGE BUFFERPOOL IBMDEFAULTBP
DB20000I SQL命令成功完成｡
C:\> DB2 CREATEREGULAR TABLESPACE TABLESPACE2 PAGESIZE 4 KMANAGED BY AUTOMATIC STORAGE BUFFERPOOL IBMDEFAULTBP
DB20000I SQL命令成功完成.
C:\> DB2 CREATEREGULAR TABLESPACE TABLESPACE3 PAGESIZE 4 KMANAGED BY AUTOMATIC STORAGE BUFFERPOOL IBMDEFAULTBP
DB20000I SQL命令成功完成
注意,我们在MANAGED BY后面跟的是AUTOMATIC STORAGE,表示新创建的表空间将使用自动存储｡如果新建的表空间使用DB2管理存储器(自动存储器),根据要创建的表空间类型不同,其空间管理会有所区别,当其表空间类型是常规或者大型时,将自动创建成数据库管理空间(DMS), 当其表空间类型是系统临时或者用户临时时,将自动创建成系统管理空间(SMS)｡使用自动存储,就不再需要担心如何添加容器以及监控容器的增长等,自动存储会自动增加表空间在磁盘和文件系统上的大小｡在DB2CLP窗口中输入LIST TABLESPACE命令,你可以看到3个示例表空间TABLESPACE1,TABLESPACE2和TABLESPACE3已经创建成功,其空间管理类型是数据库管理空间,具体如清单7所示:
- - 清单7. 查看示例表空间
C:\> db2 list tablespaces show detail
当前数据库的表空间
. . . . . . . . . . . . . . . . . . . .
表空间标识 = 4
名称 = TABLESPACE1
类型 = 数据库管理空间
内容 = 所有持久数据｡常规表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 8192
可用页数 = 8160
已用页数 = 96
可用页数 = 8064
高水位标记(页) = 96
页大小(以字节计) = 4096
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
表空间标识 = 5
名称 = TABLESPACE2
类型 = 数据库管理空间
内容 = 所有持久数据｡常规表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 8192
可用页数 = 8160
已用页数 = 96
可用页数 = 8064
高水位标记(页) = 96
页大小(以字节计) = 4096
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
表空间标识 = 6
名称 = TABLESPACE3
类型 = 数据库管理空间
内容 = 所有持久数据｡常规表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 8192
可用页数 = 8160
已用页数 = 96
可用页数 = 8064
高水位标记(页) = 96
页大小(以字节计) = 4096
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
接下来我们创建3个32K页大小的大型表空间,其缓冲池使用我们新创建的示例缓冲池MYBP1,大型表空间的名称分别为:LARGETBS1,LARGETBS2和LARGETBS3｡在DB2CLP窗口中,发出CREATE LARGE TABLESPACE命令,具体如清单8所示:
- - 清单8. 创建大型示例表空间LARGETBS1,LARGETBS2和LARGETBS3
C:\> DB2 CREATE LARGETABLESPACE LARGETBS1 PAGESIZE 32 K MANAGED BYAUTOMATIC STORAGE BUFFERPOOL MYBP1
DB20000I SQL命令成功完成｡
C:\> DB2 CREATE LARGETABLESPACE LARGETBS2 PAGESIZE 32 K MANAGED BYAUTOMATIC STORAGE BUFFERPOOL MYBP1
DB20000I SQL命令成功完成｡
C:\> DB2 CREATE LARGETABLESPACE LARGETBS3 PAGESIZE 32 K MANAGED BYAUTOMATIC STORAGE BUFFERPOOL MYBP1
DB20000I SQL命令成功完成｡
在DB2CLP窗口中输入LIST TABLESPACE命令,你可以看到3个大型示例表空间LARGETBS1,LARGETBS2和LARGETBS3已经创建成功,其空间管理类型是数据库管理空间,具体如清单9所示:
- - 清单9. 查看示例表空间
C:\> db2 list tablespaces show detail
当前数据库的表空间
. . . . . . . . . . . . . . . . . . . .
表空间标识 = 7
名称 = LARGETBS1
类型 = 数据库管理空间
内容 = 所有持久数据｡大型表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 1024
可用页数 = 992
已用页数 = 96
可用页数 = 896
高水位标记(页) = 96
页大小(以字节计) = 32768
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
表空间标识 = 8
名称 = LARGETBS2
类型 = 数据库管理空间
内容 = 所有持久数据｡大型表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 1024
可用页数 = 992
已用页数 = 96
可用页数 = 896
高水位标记(页) = 96
页大小(以字节计) = 32768
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
表空间标识 = 9
名称 = LARGETBS3
类型 = 数据库管理空间
内容 = 所有持久数据｡大型表空间｡
状态 = 0x0000
详细解释:
正常
总计页数 = 1024
可用页数 = 992
已用页数 = 96
可用页数 = 896
高水位标记(页) = 96
页大小(以字节计) = 32768
扩展数据块大小(页) = 32
预取大小(页) = 32
容器数 = 1
使用自动方法创建示例分区表
在创建示例分区表时,可以为每个数据分区指定范围｡分区表使用了数据组织方案,即,表数据根据该表中一个或多个表分区键列中的值分布到多个存储对象(称为数据分区或范围)中｡根据CREATE TABLE语句的PARTITION BY子句中指定的内容,给定表的数据被划分到多个存储对象中｡范围由PARTITION BY子句的 STARTING FROM和ENDING AT值指定｡这些存储对象可以在不同的表空间中,也可以在相同表空间中｡所有指定的表空间在下列方面必须相同:页大小､扩展数据块大小､存储机制(DMS 和 SMS)和类型(常规或大型),并且所有表空间必须位于相同数据库分区组中｡表分区功能简化了表数据转入和转出以及管理工作,并且提高了索引布置灵活性和查询处理效率｡与普通的表相比,分区表包含的数据可以多得多｡分区表最多可以有 32767 个数据分区｡可以对分区表添加数据分区､将数据分区与分区表相连以及断开数据分区与分区表的连接,并且,可以将一个表的多个数据分区范围存储在一个表空间中｡不支持在分区表中使用XML和DATALINK等类型｡
表分区键是一个或多个表列的有序集合｡表分区键列中的值用来确定每个表行所属的数据分区｡选择有效的表分区键列对于充分利用表分区功能的优点来说十分关键｡下列准则可以帮助您为分区表选择最有效的表分区键列:
• 将范围定义成与数据转入大小相匹配｡最常见的情况是根据日期或时间列对数据进行分区｡
• 将范围详细程度定义为与数据转出相匹配｡最常见的情况是使用月份或季度｡
• 根据有益于消除分区的列进行分区｡
分区表可以包含下列数据类型,但不支持将它们用作表分区键列:
• 用户定义的类型(结构化)
• LONG VARCHAR
• LONG VARCHARFOR BIT DATA
• BLOB
• BINARY LARGEOBJECT
• CLOB
• CHARACTERLARGE OBJECT
• DBCLOB
• LONGVARGRAPHIC
• REF
• C变长字符串
• Pascal变长字符串
数据分区是表的一部分行,这些行不与其他部分的行存储在一起,并且按照 CREATE TABLE语句的PARTITION BY子句中提供的规范分组｡如果一个表是使用PARTITION BY子句创建的,则该表是分区表｡
可以通过在 DB2 控制中心中使用“创建表”向导或者通过使用 CREATE TABLE语句来创建示例分区表｡要使用 DB2 CLP来创建示例分区表,请发出 CREATE TABLE 语句:
CREATE TABLE < NAME > ( < column_name > < data_type > < null_attribute > ) IN
< table space list > PARTITION BY RANGE ( < column expression > )
STARTING FROM < constant >ENDING < constant > EVERY< constant >
自动生成方法十分简单,它使您能够快速方便地创建许多数据分区｡此方法适合于创建基于日期或数值并且大小相等的范围｡如果选择使用CREATETABLE语句的EVERY子句来自动生成数据分区,那么只能将一列用作表分区键｡使用自动生成的语法格式(包含EVERY子句)创建的表在表分区键中只能使用数字或日期时间类型｡在自动生成的语法格式中,不支持MINVALUE和MAXVALUE｡范围按升序排列｡EVERY子句中的增量必须大于零｡ENDING值必须大于或等于STARTING值｡我们先来看一个最简单的创建分区表的例子,在DB2CLP窗口中连上数据库,发出CREATE TABLE命令,具体如清单10所示:
- - 清单10 .创建示例分区表TEST1
C:\> DB2 CREATE TABLE TEST1 ( COL1 INT , COL2 INT ) PARTITION BY RANGE( COL2 ) ( STARTING FROM ( 1 )INCLUSIVE ENDING AT ( 100 ) EXCLUSIVE EVERY ( 10 ) )
DB20000I SQL命令成功完成
命令执行成功,这样我们就创建了一个示例分区表TEST1,其包含10个数据分区,每个数据分区包含10个键值:
1<=col2<11
11<=col2< 21
. . . . . .
91<=col2<= 100
STARTING子句指定数据分区范围的下界｡对于最低数据分区范围来说,此子句是必需的(尽管可以将边界定义为 MINVALUE)｡INCLUSIVE表示将所有等于指定值的值都包括在包含此边界的数据分区中｡最低数据分区范围是具有最低指定边界的数据分区｡清单10中STARTING参数指定整体数据范围从1开始,其后的INCLUSIVE参数表示端值1包含在第一个数据分区内｡
ENDING(或 VALUES)子句指定数据分区范围的上界｡对于最高数据分区范围来说,此子句是必需的(尽管可以将边界定义为 MAXVALUE)｡最高数据分区范围是具有最高指定边界的数据分区｡EXCLUSIVE表示所有等于指定值的值都不包括在包含此边界的数据分区中｡清单10中ENDING参数表示整体数据范围从到100为止,其后的EXCLUSIVE表示,最后一个数据分区不包含端值100｡
如果未对某个数据分区指定ENDING子句,则下一个更大数据分区就必须指定STARTING子句｡否则,如果未指定STARTING子句,则上一个数据分区就必须指定ENDING子句｡
由于整体起始界限(1)包括端值,所以第一个数据分区(col2>= 1且col2< 11 )的起始值包括端值｡同样,由于整体结束界限(100)不包括端值,所以最后一个数据分区(col2 >= 91且col2<100)的结束界限不包括端值｡其余 STARTING 值都包括端值,并且其余 ENDING 值全都不包括端值｡每个数据分区都存放 n 个键值,其中 n 由 EVERY子句指定｡由于没有指定表空间,所以10个数据分区使用缺省表空间USERSPACE1｡缺省情况下,索引将存储在以下表空间中:USERSPACE1｡
接下来我们创建一个稍微复杂些的示例分区表,在DB2CLP窗口中连上数据库,发出CREATE TABLE命令,具体如清单11所示:
- - 清单11 .创建示例分区表TEST2
C:\> DB2 CREATE TABLE TEST2 ( ID INTEGER NOT NULL , AGE INTEGER NOT NULL ) PARTITION BY RANGE( AGE NULLS LAST ) ( STARTING FROM ( 1 )INCLUSIVE ENDING AT ( 100 ) EXCLUSIVE EVERY ( 10 ) ) IN TABLESPACE1, TABLESPACE2, TABLESPACE3 CYCLE INDEX IN USERSPACE1
DB20000I SQL命令成功完成
命令成功完成后,这样我们创建了一个示例分区表TEST2,其包含10个数据分区,每个数据分区包含10个键值:
1<=AGE<11
11<=AGE<21
. . . . . . .. .
91<=AGE< 100
由于没有显式给数据分区指定表空间,所以将使用循环法将3个表空间(TABLESPACE1,TABLESPACE2和TABLESPACE3)指定给10个数据分区｡缺省情况下,索引将存储在以下表空间中:USERSPACE1｡
NULL 子句指定考虑数据分区布置时是将空值安排在高位置还是低位置｡缺省情况下,将空值安排在高位置｡在此情况下,将把表分区键列中的空值视为正无穷并放到以MAXVALUE 结尾的范围中｡如果未定义这样的数据分区,就会将空值视为超出范围的值｡如果要排除表分区键列中的空值,请使用 NOT NULL约束｡LAST 指定让空值在排序的值列表中最后出现｡FIRST 指定让空值在排序的值列表中最先出现｡PARTITION BY RANGE 指定表分区键为AGE,其后跟的NULLS LAST参数表示含有空值的数据行就会排到最后,您也可以在升序排序中使用NULLS FIRST,这样含有空值的数据行会出现在第一个｡
STARTING参数指定整体数据范围从1开始,其后的INCLUSIVE参数表示端值1包含在第一个数据分区内,ENDING参数表示整体数据范围从到100为止,其后的EXCLUSIVE表示,最后一个数据分区不包含端值100｡由于整体起始界限(1)包括端值,所以第一个数据分区(AGE>= 1且AGE< 11 )的起始值包括端值｡同样,由于整体结束界限(100)不包括端值,所以最后一个数据分区(AGE>= 91且AGE<100)的结束界限不包括端值｡其余 STARTING 值都包括端值,并且其余 ENDING 值全都不包括端值｡每个数据分区都存放 n 个键值,其中 n 由 EVERY子句指定｡
接下来我们创建示例分区表TEST3,用来对表数据进行分区的列是RQ,表数据将存储在下列表空间
中:TABLESPACE1,TABLESPACE2和TABLESPACE3｡缺省情况下,索引将存储在以下表空间中:USERSPACE1｡大数据将存储在下列表空间中:LARGETBS1,LARGETBS2和LARGETBS3｡在DB2CLP窗口中连上数据库,发出CREATE TABLE命令,具体如清单12所示:
- - 清单12 .自动创建示例分区表TEST3
C:\> DB2 CREATE TABLE TEST3 ( ID INTEGER NOT NULL , RQ DATE NOT NULL , IMAGE BLOB ( 1 M ) NOT NULL LOGGED NOT COMPACT ) PARTITION BY RANGE( RQ NULLS LAST ) ( STARTING FROM ( '2007-01-01' ) INCLUSIVE ENDING AT ( '2007-12-31' ) INCLUSIVE EVERY ( 1MONTHS ) ) IN TABLESPACE1 , TABLESPACE2, TABLESPACE3 CYCLEINDEX IN USERSPACE1 LONG IN LARGETBS1 , LARGETBS2 , LARGETBS3 CYCLE
DB20000I SQL命令成功完成
此语句生成 12 个数据分区,每个数据分区包含 1 个键值:
(RQ) >= ( ' 2007-01-01 ' ) ,(RQ) < (' 2007-02-01 ' )
(RQ) >= ( ' 2007-02-01 ' ) ,(RQ) < (' 2007-03-01 ' )
. . . . . .
(RQ) >= ( ' 2007-12-01 ' ) ,(RQ) <= (' 2007-12-31 ' )
由于整体起始界限( ' 2007-01-01 ' )包括端值,所以第一个数据分区的起始值包括端值｡同样,由于整体结束界限(' 2007-12-31 ' )包括端值,所以最后一个数据分区的结束界限包括端值｡其余 STARTING 值都包括端值,并且其余 ENDING 值也都包括端值｡每个数据分区都存放 n 个键值,其中 n 由 EVERY 子句指定｡使用公式 (start +every) 来确定每个数据分区的范围末端｡如果 START 到 END 的范围无法整除 EVERY 值,最后一个数据分区包含的键值就会较少｡
使用手工方式创建示例分区表
手工生成方法为PARTITIONBY子句中列示的每个范围创建一个新数据分区｡这种语法格式提高了定义范围时的灵活性,从而增加了数据和LOB布置选项｡对清单12所创建的示例分区表TEST3,如果用手工方式创建,可以在DB2CLP窗口中,连上数据库,先发出DROP TABLE命令,删除示例分区表TEST3,再发出CREATETABLE命令创建示例分区表TEST3,其分区键RQ允许录入的时间段为“2007-01-01”到“2007-12-31”,具体如清单13所示:
- - 清单13 .手工创建示例分区表TEST3
C:\> DB2 CONNECT TODB2TEST1
数据库连接信息
数据库服务器= DB2 / NT 9.1.0
SQL 授权标识 =RHETTE
本地数据库别名= DB2TEST1
C:\> db2 drop table test3
DB20000I SQL命令成功完成｡
C:\> db2 CREATE TABLE TEST3 ( ID INTEGER NOT NULL , RQ DATE NOT NULL , IMAGE BLOB (1 M ) NOT NULL LOGGED NOT COMPACT ) PARTITION BY RANGE(RQ NULLS LAST) (PARTITION DATAPATITION1 STARTING FROM ('2007-01-01') INCLUSIVE ENDING AT ('2007-02-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION2 STARTING FROM ('2007-02-01') INCLUSIVE ENDING AT ('2007-03-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION3 STARTING FROM ('2007-03-01') INCLUSIVE ENDING AT ('2007-04-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION4 STARTING FROM ('2007-04-01') INCLUSIVE ENDING AT ('2007-05-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION5 STARTING FROM ('2007-05-01') INCLUSIVE ENDING AT ('2007-06-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ,PARTITION DATAPATITION6 STARTING FROM ('2007-06-1') INCLUSIVE ENDING AT ('2007-07-1')EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION7 STARTING FROM ('2007-07-01') INCLUSIVE ENDING AT ('2007-08-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION8 STARTING FROM ('2007-08-01') INCLUSIVE ENDING AT ('2007-09-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION9 STARTING FROM ('2007-09-01') INCLUSIVE ENDING AT ('2007-10-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION10 STARTING FROM ('2007-10-01') INCLUSIVE ENDING AT ('2007-11-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION11 STARTING FROM ('2007-11-01') INCLUSIVE ENDING AT ('2007-12-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION12 STARTING FROM ('2007-12-01') INCLUSIVE ENDING AT ('2007-12-31') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3) INDEX IN USERSPACE1
DB20000I SQL命令成功完成｡
这样我们通过手工方式创建了示例分区表TEST3,用来对表数据进行分区的列是RQ,表数据将存储在下列表空间中:TABLESPACE1,TABLESPACE2和TABLESPACE3｡缺省情况下,索引将存储在以下表空间中:USERSPACE1｡此语句生成12个数据分区,每个数据分区包含1个键值:
(RQ) >= ( '2007-01-01 ' ) , (RQ) < (' 2007-02-01 ' )
(RQ) >= ( '2007-02-01 ' ) , (RQ) < (' 2007-03-01 ' )
. . . . . .
(RQ) >= ( '2007-12-01 ' ) , (RQ) <= (' 2007-12-31 ' )
每个数据分区通过PARTITION命令进行了命名,名字分别DATAPARTITION1,DATAPARTITION2, . . . .. . DATAPARTITION12｡
接下来我们对示例分区表TEST3插入数据,在DB2CLP窗口中,发出INSERT INTO命令,具体如清单14所示:
- - 清单14 .对示例分区表TEST3插入一条记录
C:\> DB2 INSERT INTO TEST3 VALUES ( 1 , '2007-01-10' , blob ( 'thefirst rows' ) )
DB20000I SQL命令成功完成｡
命令成功完成,把行插入示例分区表时,根据该行的键值以及它所处的范围自动将其放入正确的数据分区,这样我们数据就插入到了数据分区DATAPARTITION1中了｡如果该行处于对该表定义的所有范围之外,插入就会失败,并且将把以下错误返回给应用程序,比如我们插入一条如清单15所示的记录,RQ的值为'2006-01-10',不在'2007-01-01'和'2007-12-31'的范围内:
- - 清单15 .对示例分区表TEST3插入一条分区键范围外的记录
C:\> DB2 INSERT INTO TEST3 VALUES (1 , '2006-01-10' , blob ( 'thefirst rows' ) )
DB21034E 该命令被当作 SQL语句来处理,因为它不是有效的“命令行处理器”命令｡在 SQL 处理期间,它返回:
SQL0327N 无法将行插入表 "RHETTE.TEST3 " 中,因为它在定义的数据分区范围之外｡
SQLSTATE = 22525
此时我们对查看示例分区表中的记录,通过在WHERE子句中指定分区键的值,可以能够避免扫描不相关的数据,直接从DATAPARTITION1数据分区中得到想要的记录,从而提高查询处理性能,具体如清单16所示:
- - 清单16 .查询示例分区表TEST3
C:\> db2 select id,rqfrom test3 where rq='2007-01-10'
ID RQ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 2007-01-10
1 条记录已选择｡
如果想在范围之间允许存在间隔,可以使用MINVALUE和MAXVALUE指定间隔的范围,MINVALUE表示最小的值,就是比你插入的任何值都会小的意思,MAXVALUE表示最大的值,就是比你插入的任何值都要大的意思｡对示例分区表TEST3,我们可以增量两个数据分区:
数据分区OTHERS1,范围从MINVALUE到 ” 2007-01-01” ,不包含” 2007-01-01”;所有比” 2007-01-01 ”小的值,都会放入到此分区中｡
数据分区OTHERS2,范围从” 2008-01-01” 到MAXVALUE,包含” 2008-01-01”;所有比 ”2008-01-01” 大的值,都会放入到此分区中｡
具体如清单17中蓝色标记部分所示:
- - 清单17 .手工创建示例分区表TEST3
C:\> db2 drop table test3
DB20000I SQL命令成功完成｡
C:\> db2 CREATE TABLE TEST3 ( ID INTEGER NOT NULL , RQ DATE NOT NULL , IMAGE BLOB (1 M ) NOT NULL LOGGED NOT COMPACT ) PARTITION BY RANGE (RQ NULLS LAST) (PARTITION DATAPATITION1 STARTING FROM ('2007-01-01') INCLUSIVE ENDING AT ('2007-02-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION2 STARTING FROM ('2007-02-01') INCLUSIVE ENDING AT ('2007-03-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION3 STARTING FROM ('2007-03-01') INCLUSIVE ENDING AT ('2007-04-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION4 STARTING FROM ('2007-04-01') INCLUSIVE ENDING AT ('2007-05-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION5 STARTING FROM ('2007-05-01') INCLUSIVE ENDING AT ('2007-06-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ,PARTITION DATAPATITION6 STARTING FROM ('2007-06-1') INCLUSIVE ENDING AT ('2007-07-1')EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION7 STARTING FROM ('2007-07-01') INCLUSIVE ENDING AT ('2007-08-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITIONDATAPATITION8 STARTING FROM ('2007-08-01') INCLUSIVE ENDING AT ('2007-09-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION9 STARTING FROM ('2007-09-01') INCLUSIVE ENDING AT ('2007-10-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION10 STARTING FROM ('2007-10-01') INCLUSIVE ENDING AT ('2007-11-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION11 STARTING FROM ('2007-11-01') INCLUSIVE ENDING AT ('2007-12-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION12 STARTING FROM ('2007-12-01') INCLUSIVE ENDING AT ('2007-12-31') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 ,PARTITION OTHERS1 STARTING FROM(MINVALUE) EXCLUSIVE ENDING AT ('2007-01-01') EXCLUSIVE INTABLESPACE1 LONG IN LARGETBS1 , PARTITION OTHERS2 STARTING FROM ('2008-01-01') INCLUSIVE ENDING AT (MAXVALUE) EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ) INDEX IN USERSPACE1
DB20000I SQL命令成功完成｡
此时我们再次插入清单15中所插入的值,就可以成功了,具体如清单18所示:
- - 清单18 .对示例分区表TEST3插入一条记录
C:\> DB2 INSERT INTO TEST3 VALUES (1,'2006-01-10' , blob ( ' thefirst rows ' ) )
DB20000I SQL命令成功完成｡
对清单10中,把数值型的列作为表分区键,也可以用手工的方式进行创建,具体如清单19所示:
- - 清单19 .创建示例分区表TEST4
C:\> DB2 CREATE TABLE RHETTE.TEST4 ( COL1 INTEGER NOT NULL , COL2 BIGINT NOT NULL ) PARTITION BY RANGE( COL2 NULLS LAST ) ( PARTITION DATAPARTION1 STARTING FROM ( 1 ) INCLUSIVE ENDING AT ( 10 )INCLUSIVE IN TABLESPACE1 , PARTITION DATAPARTITION2STARTING FROM ( 11 )INCLUSIVE ENDING AT ( 20 ) INCLUSIVE )
DB20000I SQL命令成功完成｡
使用手工方式创建分区表可以将多个列用作表分区键,比如我们在DB2CLP窗口中创建示例分区表TEST5,具体如清单20所示:
- - 清单20 .创建示例分区表TEST5
C:\> DB2 CREATE TABLE TEST5 ( year INT , month INT) PARTITION BY RANGE( year , month ) ( STARTING FROM ( 2007 , 1 ) ENDING ( 2007 , 3 ) IN TABLESPACE1 , ENDING ( 2007 , 6 ) IN TABLESPACE2 , ENDING ( 2007 , 9 ) IN TABLESPACE3 )
DB20000I SQL命令成功完成｡
命令成功完成,这样我们就成功地创建了示例分区表TEST5,其包含3个数据分区,即2007年前3个季度,每个季度一个数据分区｡需要注意的是,当将多个列用作表分区键时,将把这些列视为组合键(类似于索引中的组合键),其中,后面的列依赖于前面的列｡指定的每个起始值或结束值(所有列一起)不能超出512个字符｡此限制与SYSCAT.DATAPARTITIONS目录视图中的LOWVALUE和HIGHVALUE 列大小对应｡如果指定超出512个字符的起始值或结束值,就会导致错误SQL0636N,原因码为9｡ 表分区是多列的,而不是多维的｡在表分区中,使用的所有列都包含在单个维中｡
另外,还可以将生成列用作表分区键｡接下来我们在DB2CLP窗口中,创建示例分区表TEST6,其包含 12个数据分区的表,即每个月一个数据分区｡对于任何年份,一月份的所有行都将被放到第一个数据分区中,二月份的行将被放到第二个数据分区中,依此类推,具体如清单21所示:
- - 清单21 .创建示例分区表TEST6
C:\> DB2 CREATE TABLE TEST6 ( RQ date,YF int GENERATED ALWAYS AS ( month( RQ ) ) ) PARTITION BY RANGE( YF ) (STARTING FROM 1 ENDING AT 12 EVERY 1 )
DB20000I SQL命令成功完成｡
命令成功完成,这样我们成功创建了示例分区表TEST6,需要注意的是对于表分区键中使用的生成列,不能改变或删除其表达式｡不允许对表分区键中使用的列添加生成列表达式｡对于表分区键中使用的列,如果尝试添加､删除或改变该列的生成列表达式,就会导致错误(SQL0270N,原因码为52)｡
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
