Oracle分区表和分区索引在VLDB中的研究

赵卫东， 刘永红， 鄢 涛， 于 曦

(1.成都大学 模式识别与智能信息处理四川省高校重点实验室， 四川 成都 610106;2.成都大学 信息科学与工程学院， 四川 成都 610106)

Oracle分区表和分区索引在VLDB中的研究

赵卫东1,2， 刘永红1,2， 鄢 涛1,2， 于 曦1,2

(1.成都大学 模式识别与智能信息处理四川省高校重点实验室， 四川 成都 610106;2.成都大学 信息科学与工程学院， 四川 成都 610106)

分区表和分区索引功能是数据库管理中的关键技术之一，是海量数据库管理(Very Large Databases，VLDB)中一个重要的性能提升机制.分区技术的原理是将逻辑上的一个大表拆分成多个独立的物理分区来存储，从而提高数据I/O性能.面对大数据的存储，几乎所有Oracle数据库都用分区技术来提高查询数据的性能.通过分析一个项目案例，说明如何综合使用分区表和分区索引来提高大数据的查询速度.

Oracle;分区表;分区索引;VLDB

0 引 言

分区是构建千兆字节数据系统或超高可用性系统的关键工具.Oracle分区工具将表或者索引物理分割成多个小单元.应用时，从逻辑上看是一个表或者一个索引，而物理上却都是独立的对象，并且都可以被独立管理[1-4]，为数据库维护和各类应用程序的编写带来了极大的好处.通常，分区可以大大提高某些查询以及维护操作的性能,还可以极大简化常见的管理任务.分区功能将表、索引或索引组织表进一步细分为段，每个分区有自己的名称，可以选择自己的存储特性，以便能存储在不同的物理介质中.从数据库管理员的角度，分区后的对象具有多个段，这些段既可被集体管理，也可被单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性.如果把表的不同分区分配到不同的磁盘，还可以平衡磁盘I/O操作，减少磁盘争用.然而，从应用程序的角度，分区后的表与非分区表完全相同，访问数据的SQL语句无需任何修改，即用户访问数据时感觉不到分区的存在，这正是Oracle数据库系统的优越性所在.

1 分区类型

1.1 分区表类型

1)范围分区(Range).是应用范围比较广的表分区方式，以列值的范围作为分区的划分条件，将记录存放到列值所在的Range分区中.

2)散列分区(Hash).如果指定为Hash分区，Oracle会将表中的数据自动平均分配到指定的几个分区中，因此并不能控制也不知道哪条记录会被放到哪个分区.Hash分区可以支持多个依赖列.对于那些无法有效划分范围的表，可以使用Hash分区.

3)列表分区(List).List也需要指定列的值，但不同于Range分区的范围式列值，其分区值必须明确指定.也不同于Hash分区，List分区通过明确指定分区值，将记录存储在预设的分区.List分区的分区列只能有一个.

4)组合分区(Subpartition).如果某表按照某列分区后仍然较大，或者有一些其他的需求，还可以通过分区内再建子分区的方式将分区再进行分区，即为组合分区的方式.要注意顺序，根分区只能是Range分区，子分区可以是Hash分区或List分区.

5)Interval分区.是Range分区的增强分区方式，可以实现范围分区的自动化与均衡化.创建的分区作为元数据，只有最开始的分区是永久分区.随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引.

6)引用分区(Reference).是指子表没有本身的分区属性，通过外键参照父表分区进行分区.这在有父子关系的多表分区存储中有重要的应用价值.

7)虚拟列分区(Generated).Oracle允许将某一列或者多列设计为一个虚拟列.数据库对这个虚拟列进行分区存储.当一个表中某个属性不能直接转为范围或者列表时，虚拟列则可以表示为某个范围，从而进行分区.

1.2 分区索引类型

索引和表一样，需要占用磁盘空间.一般来说，如果索引所对应的表的数据量非常大，比如几百万甚至上千万条数据，则索引也会占用很大的空间，因此建议对索引进行分区[1].

索引分区的类型有2类：本地分区索引与全局分区索引.

1)本地分区索引(Local Partitioned Indexes)，也叫局部索引.本地分区形式与表分区完全相同，依赖列也相同.数据仓库系统中大多数分区实现使用的就是局部索引[2].

2)全局分区索引(Global Partitioned Indexes)，可以是Range分区或Hash分区.既可建于分区表上，又可创建于非分区表上[3]，即全局索引是完全独立于分区表的，因此它需要更多的维护操作.使用全局分区索引时需要注意索引分区数跟表分区数可能不同[4].全局分区最适合于联机事务处理过程(On-Line Transaction Processing，OLTP)系统来直接访问特定的记录[5].

2 分区应用

2.1 分区表和分区索引的应用

假定一个大型企业有一个超大数据量的订单表，数据在1亿行以上.订单表中有3个基本属性：订单ID(ORDER-ID),订单日期(ORDER-DATE)与客户姓名(CUST-NAME).在日常查询中，通常需要根据这3个属性值之一或者组合查询订单数据.

表1 ORDERS表

如表1所示，通常情况下企业的销售活动是按日期递增进行的，所以订单表应按日期分区存储(最常见是按年度或者月度分区)，这样就使大量的数据按日期分类集中存储在不同的数据段中.按日期查询时，只需要查询某个分区的数据，跳过其他不在日期范围的数据段，从而大大加快了数据的查询速度.

数据分区存储不会自动将数据排序.如果需要在查询时排序，那么还要加上索引，这就是在表2中给ORDER-DATE加上B-树索引的原因.大数据情况下，普通索引会占用大量的存储空间，因此需要将索引按分区存储，这就是“分区索引”技术(见图1).一般来讲，只需要对当年或者当月数据进行排序，所以设置ORDER-DATE为本地范围分区即可.没有必要对全部数据排序，如果需要，排序时间长一点也是可以接受的.

图1 基于ORDER-DATE的本地分区索引(Range)

对于ORDER-ID主键，由于它与ORDER-DATE属性无关，因此不能设计为本地分区存储，只能设计为全局分区方式存储.由于ORDER-ID的范围难以确定，所以最好采用Hash分区方式由系统自动平均分配到各分区中，如图2所示.

图2 基于ORDER-ID的全局分区索引(Hash)

对于CUST-NAME(客户姓名)属性，由于不是主要查询属性，可将其设置为普通B-树索引，见图3.

图3 基于CUST-NAME的全局非分区索引(B-Tree)

2.2 脚本语句

本研究的实验环境中，使用2个表空间存储ORDERS分区表以及表的分区索引.这2个表空间是USERS和USERS02.实际的SQL语句如下：

CREATE TABLE ORDERS

(

ORDER-ID NUMBER(10,0) NOT NULL,

ORDER-DATE DATE NOT NULL,

CUSTOMER-NAME VARCHAR2(40 BYTE) NOT NULL

)

PARTITION BY RANGE (ORDER-DATE)

(

PARTITION PARTITION-BEFORE-2016 VALUES LESS THAN(TO-DATE(′2016-01-01′,′YYYY-MM-DD′,′NLS-CALENDAR=GREGORIAN′)) TABLESPACE USERS,

PARTITION PARTITION-BEFORE-2017 VALUES LESS THAN(TO-DATE(′2017-01-01′,′YYYY-MM-DD′,′NLS-CALENDAR=GREGORIAN′)) TABLESPACE USERS02

);

——创建基于ORDER-DATE的本地分区索引INDEX-DATE：

CREATE INDEX INDEX-DATE ON ORDERS(ORDER-DATE ASC)

LOCAL

(

PARTITION PARTITION-BEFORE-2016 TABLESPACE USERS,

PARTITION PARTITION-BEFORE-2017 TABLESPACE USERS02

);

——创建基于CUSTOMER-NAME的普通索引

CREATE INDEX INDEX-CUSTON ORDERS(CUSTOMER_NAME ASC);

——创建基于ORDER-ID的全局分区索引(Hash)

CREATE UNIQUE INDEX ORDERS-PK ON ORDERS(ORDER-ID ASC)

GLOBAL PARTITION BY HASH(ORDER-ID)

(

PARTITION INDEX-PARTITION1 TABLESPACE USERS,

PARTITION INDEX-PARTITION2 TABLESPACE USERS02

);

ALTER TABLE ORDERS ADD CONSTRAINT ORDERS-PK PRIMARY KEY(ORDER-ID)

USING INDEX ORDERS-PK ENABLE;

3 实验结果与分析

为了测试以上分区方式，插入1亿条ORDERS订单记录.分2个表空间，每个表空间各存储1个表的分区和1个索引分区.表2记录了3个典型SQL语句的执行跟踪过程和执行计划.从实际执行时间看，查询单条特定订单的语句执行时间都在0.01 s以内.如果分区得当，这个速度不会随数据量的增加而显著减慢，非常高效快捷.

注意，表2中的变量d1与d2必须在同一年范围内，否则“操作”中可能是全表搜索，而不进行分区搜索，即不会使用“PARTITION RANGE SINGLE”.

表2 3条SQL语句的执行计划及分析

4 结 语

分区表可以增加系统可用性，并改善系统性能[5].分区设计时，首先要考虑表的数据量是否足够大，然后分析使用哪个属性进行分区、分区类型的选用及分区范围的粗细度等因素，最后为每个分区分配1个表空间和数据文件.合理分配分区表可以极大提高数据的查询性能.如果有一些表不适合设计为分区表，也可以考虑将索引单独设计为全局索引分区.索引分区和表分区可以有不同的存储表空间.

请注意，分区表也有其局限性.主要局限性是，根据查询表的非分区属性值查询时，由于数据分散在不同分区，查询性能反而可能受影响.另外，对已经存在的非分区表不能直接转化为分区表,只能使用Oracle提供的在线重定义表功能.分区表也使得数据迁移工作变得异常复杂.

[1]谷长勇,王彬,陈杰,等.Oracle11g权威指南[M].北京:电子工业出版社,2008.

[2]Thomas Kyte.Oracle Database 9i/10g/11g编程艺术[M].苏金国，王小振，译.北京:人民邮电出版社,2011.

[3]吴洁明，周锦．基于Oracle数据库SQL查询语句优化规则的研究[J]．陕西理工学院学报(自然科学版)，2013，29(4)：34-38．

[4]朱木秀.ORACLE分区技术在移动综合网管系统中的应用[J].电脑知识与技术 2011,7(33):8137-8140.

[5]曹彤,程君.Oracle分区技术在LIS中的应用研究[J].医院数字化，2012,33(4):60-61.

[6]Ian Abramson,Michael Abbey，Michelle Malcher,等.专业级Oracle Database 12c安装、配置与维护[M].卢涛，李颖，译.北京：清华大学出版社，2014.

Research on Oracle Partitioned Table and Partitioned Index in VLDB

ZHAOWeidong1,2,LIUYonghong1,2,YANTao1,2,YUXi1,2

(1.Key Laboratory of Pattern Recognition and Intelligent Information Processing, Chengdu 610106, China;2.School of Information Science and Engineering, Chengdu University, Chengdu 610106, China)

Partitioned table and partitioned index are the key technologies in database management.They serve as important mechanisms to improve the performance of Very Large Databases(VLDB).The principle of partition technology is to separate a large table of logic into a plurality of independent physical partitions to store,and thus to improve the performance of data I/O.In the face of a large number of data storage,almost all of the Oracle database use partition technology to improve the performance of data query.This article analyzes a project case and then illustrates how to use the partitioned table and partitioned index to improve the query speed of large data.

Oracle;partitioned table;partitioned index;VLDB

1004-5422(2016)04-0358-04

2016-11-03.

赵卫东(1968 — )， 男， 硕士， 副教授， 从事计算机软件工程研究.

TP311.13

A