♣题目 部分
分区表有什么优点？分区表有哪几类？如何选择用哪种类型的分区表？
♣答案部分
当表中的数据量不断增大时，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。当对表进行分区后，在逻辑上，表仍然是一张完整的表，只是将表中的数据在物理上可能存放到多个表空间或物理文件上。当查询数据时，不至于每次都扫描整张表。Oracle可以将大表或索引分成若干个更小、更方便管理的部分，每一部分称为一个分区，这样的表称为分区表。SQL语句使用分区表比全表能提供更好的数据处理与访问的性能。即使某些分区不可用，其它分区仍然可用，这叫做分区独立性。
分区表的一些限制条件：
① 簇表不能进行分区。
② 不能分割含有LONG或LONG RAW列的表。
③ 索引组织表不能进行范围分区。
何时考虑分区？
对大表进行分区，将有益于大表操作的性能和大表的数据的维护。官方文档说通常当表的大小超过2GB，或对于OLTP系统，当表的记录超过1000万时，都应考虑对表进行分区。
分区表有什么优点？
分区表有如下的优点：
① 增强可用性：如果表的一个分区由于系统故障而不能使用，那么表的其余好的分区仍可以使用。
② 减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，可能比整个大表修复花的时间更少。
③ 维护轻松：单独管理每个分区比管理单个大表要轻松得多。
④ 均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O，改善性能。
⑤ 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快。
⑥ 分区对用户透明，最终用户感觉不到分区的存在。
有哪些类型的分区？如何选择用哪种类型的分区表？
Oracle的分区可以分为：
● 范围分区（RANGE PARTITION）
● 哈希分区（HASH PARTITION）
● 列表分区（LIST PARTITION）
● 引用分区（REFERENCE PARTITION）
● 复合分区（组合分区）
● INTERVAL分区（间隔分区）
● 系统分区
INTERVAL分区和系统分区是Oracle 11g的新特性，由于篇幅原因，本书中不讲解这2个分区，读者可自行查阅官方文档进行学习。尤其对于INTERVAL分区在生产环境中还是比较实用的。下面作者将对范围、哈希、列表和复合分区分别进行讲解。
1、RANGE（范围）分区
在如下几种情况下会使用到范围分区：
（1）频繁地被一个时间范围谓词扫描。
（2）维护一个时间滚动的数据窗口（Rolling Window Of Data）。
（3）保存历史数据的表。
例如，在下面的例子中，给数据表SALE_DATA在时间字段SALES_DATE上按照每个月一个分区的方式来创建一个范围分区。
CREATE TABLE SALE_DATA
(SALE_ID NUMBER(5),
SALESMAN_NAME VARCHAR2(30),
SALES_AMOUNT NUMBER(10),
SALES_DATE DATE)
PARTITION BY RANGE(SALES_DATE)
(
PARTITION SALES_2009_1 VALUES LESS THAN(TO_DATE('01/02/2009','DD/MM/YYYY')),
PARTITION SALES_2009_2 VALUES LESS THAN(TO_DATE('01/03/2009','DD/MM/YYYY')),
PARTITION SALES_2009_3 VALUES LESS THAN(TO_DATE('01/04/2009','DD/MM/YYYY')),
PARTITION SALES_2009_4 VALUES LESS THAN(TO_DATE('01/05/2009','DD/MM/YYYY')),
PARTITION SALES_2009_5 VALUES LESS THAN(TO_DATE('01/06/2009','DD/MM/YYYY')),
PARTITION SALES_2009_6 VALUES LESS THAN(TO_DATE('01/07/2009','DD/MM/YYYY')),
PARTITION SALES_2009_7 VALUES LESS THAN(TO_DATE('01/08/2009','DD/MM/YYYY')),
PARTITION SALES_2009_8 VALUES LESS THAN(TO_DATE('01/09/2009','DD/MM/YYYY')),
PARTITION SALES_2009_9 VALUES LESS THAN(TO_DATE('01/10/2009','DD/MM/YYYY')),
PARTITION SALES_2009_10 VALUES LESS THAN(TO_DATE('01/11/2009','DD/MM/YYYY')),
PARTITION SALES_2009_11 VALUES LESS THAN(TO_DATE('01/12/2009','DD/MM/YYYY')),
PARTITION SALES_2009_12 VALUES LESS THAN(TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION SALES_2010_1 VALUES LESS THAN(TO_DATE('01/02/2010','DD/MM/YYYY'))
);
这样就可以非常方便地来管理每个月的数据了。例如，要删除1年前的数据，只需要写一个JOB来做定时检查，如果发现有分区是1年以前的，那么就可以直接将它删除，这样的操作对用户是不可见的。
例如，要删除2009年第1个月的分区数据，就可以使用这样的命令：ALTER TABLE SALE_DATA DROP PARTITION SALES_2009_1;。
2、HASH（哈希）分区
HASH分区有如下的优点：
（1） 提高了大表的高可用性和可管理性。
（2） 可以避免数据倾斜，将数据均匀地分布在多个物理设备上，最大化I/O吞吐量。
（3） 分区修剪和分区智能连接。
（4） 要求分区键是高基数列。
（5） 分区修剪不支持基于分区字段的范围查询，只支持等值查询或IN-LIST查询。
HASH分区能够很容易对数据进行分区，因为语法很简单，很容易实现。在下面这种情况下，使用HASH分区比RANGE分区更好：
（1） 事先不知道需要将多少数据映射到给定范围的时候。
（2） 分区的范围大小很难确定，或者很难平衡的时候。
（3） RANGE分区使数据得到不期望的聚集时。
（4） 性能特性，如并行DML、分区修剪和分区连接很重要的时候。
下面的示例创建了一个HASH分区表：
CREATE TABLE T_HASH PARTITION BY HASH(OBJECT_ID) PARTITIONS 8 AS SELECT * FROM DBA_OBJECTS;
上面的SQL语句创建了一个8个分区的表T_HASH，分区类型为哈希分区，按照Oracle官方的要求，分区的数量应该是2的N次方为合适，例如2，4，8…，这里是8个分区。
从视图DBA_TAB_PARTITIONS中可以查询到分区的详细情况：
SELECT *
FROM DBA_TAB_PARTITIONS D
WHERE D.TABLE_OWNER NOT IN ('SYS', 'SYSTEM') AND D.TABLE_NAME='T_HASH'
ORDER BY D.TABLE_NAME, D.PARTITION_POSITION, D.PARTITION_NAME;
SELECT PARTITION_NAME, COUNT(*)
FROM USER_EXTENTS
WHERE SEGMENT_NAME = 'T_HASH'
GROUP BY PARTITION_NAME;
上面是每个分区物理上占用的EXTENTS数。
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P21)
UNION ALL
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P22)
UNION ALL
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P23)
UNION ALL
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P24)
UNION ALL
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P25)
UNION ALL
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P26)
UNION ALL
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P27)
UNION ALL
SELECT COUNT(*)
FROM T_HASH PARTITION(SYS_P28);
在上面的例子中，不论是从EXTENT占用的空间数量，还是分区中的数据量，都可以看出，数据在各个分区上分布是非常均匀的。
3、列表分区
列表分区（LIST PARTITION）提供了一种按照字段的值来进行分区的方法，这种方法非常适合于有高重复率字段值的表。通过这种方法，可以非常方便地控制将某些特定的数值存放到一个分区。
列表分区有如下特点：
（1） 列表值是离散的。
（2） 列表值是无序的，例如：PARTITION PART_01 VALUES('A','E','F')等。
（3） 分区键仅能包含一个列。
（4） 列表值是低基数的。
下面的例子创建了一个列表分区：
CREATE TABLE SALES_LIST
(SALESMAN_ID NUMBER(5),
SALESMAN_NAME VARCHAR2(30),
SALES_STATE VARCHAR2(20),
SALES_AMOUNT NUMBER(10),
SALES_DATE DATE)
PARTITION BY LIST(SALES_STATE)
(PARTITION SALES_WEST VALUES('CALIFORNIA', 'HAWAII') ,
PARTITION SALES_EAST VALUES('NEW YORK', 'VIRGINIA', 'FLORIDA'),
PARTITION SALES_CENTRAL VALUES('TEXAS', 'ILLINOIS') ,
PARTITION SALES_OTHER VALUES(DEFAULT)
);
在字段SALES_STATE上创建了列表分区，同时设定了这样的对应关系：
SALES_WEST('CALIFORNIA','HAWAII')
SALES_EAST('NEWYORK','VIRGINIA', 'FLORIDA')
SALES_CENTRAL('TEXAS','ILLINOIS')
从分区的名字上就能猜出里面的内容了，例如SALES_WEST（西部地区）包含了“CALIFORNIA”和“HAWAII”，这对于内容重复率很高，并且可能有一些共性的字段，使用列表分区是有用处的，它有利于数据的管理，例如想导出或者备份西部地区的数据，可以直接操作SALES_WEST分区，非常方便。
4、复合分区
如果某表按照某列分区之后，仍然较大，或者是有一些其它的需求，那么还可以通过在分区内再建子分区的方式将分区再分区，即复合分区的方式。
复合分区在Oracle 11g之前有两种：RANGE-HASH与RANGE-LIST。需要注意的是其顺序，根分区只能是RANGE分区，子分区可以是HASH分区或LIST分区，而Oracle 11g在复合分区功能这块有所增强，又推出了RANGE-RANGE、LIST-RANGE、LIST-LIST和LIST-HASH，这就相当于除HASH外三种分区方式的笛卡尔形式都有了，即目前一共有6种分区，但根分区只能是RANGE分区或LIST分区。
在某些时候按照业务要求，上面的几种分区也可以按照一定的目的创建组合分区，或者叫子分区。下面的例子创建了一个范围-列表组合分区：
CREATE TABLE T_RANGE_LIST(ID INT,NAME VARCHAR2(100))
PARTITION BY RANGE(ID)
SUBPARTITION BY LIST(NAME)
(
PARTITION P1 VALUES LESS THAN(5)
(SUBPARTITION SP1 VALUES ('A,B,C'),
SUBPARTITION SP2 VALUES ('D,E,F')
),
PARTITION P2 VALUES LESS THAN(10)
(
SUBPARTITION SP3 VALUES ('A,B,C'),
SUBPARTITION SP4 VALUES ('D,E,F')
),
PARTITION P3 VALUES LESS THAN(15)
);
SELECT * FROM DBA_PART_TABLES D WHERE D.TABLE_NAME = 'T_RANGE_LIST';
对于海量数据的数据库设计，分区的设计非常重要。例如，对于一个大表，应该采用哪种类型的分区，对于以后数据库的性能和管理至关重要。
其实，范围分区、HASH分区和列表分区这三种分区的特点都非常明显，如下所示：
l 如果需要进行数据的过期化处理，那么范围分区基本上是唯一的选择。
l 如果需要数据的均匀分布，那么可以考虑使用HASH分区。
l 如果数据的值可以很好地对应于某个分区，那么就可以考虑使用列表分区。
在上面的原则基础上，再结合性能的影响因素，来最终确定使用哪种类型的分区。
如果选择的分区不能确保各分区内数据量的基本平均，那么这种分区方式有可能是不恰当的。比如对于RANGE分区，假设分了10个分区，而其中一个分区中的记录数占总记录数的90%，其它9个分区只占总记录数的10%，则这个分区方式就起不到数据平衡的作用。
作者：李华荣。
