ORACLE HANDBOOK系列之十一：分区（Partition）

Partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions. Each partition is an independent object with its own name and optionally its own storage characteristics.

Oracle允许用户将大表以及大的索引拆分成小块，每一块都是一个单独的对象，称为分区，分区技术可以用于提高查询及DML性能、以及更便捷地管理数据。

 

1.  分区表

1.1.  分区表主要包括三种：

Range Partitioning

List Partitioning

Hash partitioning

 

1.2. 分区表创建语法示例（Range分区）：

CREATE TABLE tab_part_0309(val DATE, val2 VARCHAR2(200))
PARTITION BY RANGE(val)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd'))
);

 

1.3. ORA-14400错误

如果在插入数据时Oracle无法找到合适的分区，就会产生ORA-14400错误，”inserted partition key does not map to any partition”。例如（假设当前为2012-03-09）：

INSERT INTO tab_part_0309 VALUES(SYSDATE,'abrownfox');

 

1.4. 如何添加分区

ALTER TABLE tab_part_0309 ADD PARTITION p2 VALUES LESS THAN (TO_DATE('20120309','yyyymmdd'));
ALTER TABLE tab_part_0309 ADD PARTITION p3 VALUES LESS THAN (TO_DATE('20120310','yyyymmdd'));

 

1.5. 查询

SELECT * FROM tab_part_0309 PARTITION(p3);

查询时我们可以指定分区。不过更常见的是Oracle自动的Partition Pruning，即如果查询时Where子句中包括用于分区的列（示例中的val列），Oracle会自动定位分区，而不用我们手工指定分区。

 

1.6. 删除分区

ALTER TABLE tab_part_0309 DROP PARTITION p3;

 

1.7. MAXVALUE

一些情况下，我们会通过定时任务（Scheduler）来定期创建分区，这时候我们需要考虑一个问题，如果定时任务失败了导致分区没有建立， 那么后斯的数据插入就会遇到ORA-14400错误。有一种方法可以避免这种错误，使用MAXVALUE：

CREATE TABLE tab_part_0309_2(val DATE, val2 VARCHAR2(200))
PARTITION BY RANGE(val)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd')),
  PARTITION pm VALUES LESS THAN (MAXVALUE)
);

使用MAXVALUE后，无法再添加新的分区：

ALTER TABLE tab_part_0309_2 ADD PARTITION p2 VALUES LESS THAN (TO_DATE('20120310','yyyymmdd'))
ORA-14074, partition bound must be collate higher than that of the last partition

但是我们可以从pm分区中进行拆分：

ALTER TABLE tab_part_0309_2
  SPLIT PARTITION pm AT (TO_DATE('20120310','yyyymmdd'))
  INTO (PARTITION p2, PARTITION pm)

这样的好处是，即便执行拆分的定时任务失败了，数据仍然可以正常插入（只不过进入了pm分区）。同时，发现错误后，我们可以进行补救，手工拆分出一个分区，这样，pm中符合新分区条件的数据，会自动进行新的分区。

 

1.8. Interval partitioning

11G中引入了一个更为强大的分区机制：Interval partitioning，可以按一定条件自动创建分区。创建语法是这样的：

CREATE TABLE tab_part_0309_3(val DATE, val2 VARCHAR2(200))
PARTITION BY RANGE(val)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd'))
);

Interval分区表必须指定一个根分区（上例的p1），使用Interval指定分区的间隔，它使用了NumToYMInterval函数，该函数第一个参数是number，后一个是单位，包括’MONTH’, ‘YEAR’，上例表示间隔为一个月，目前看一个月应该是最小的间隔了，你可以指定0.5或者0.3，但最终的效果还是一个月。

假设当前时间为2012-03-09，执行下述语句

INSERT INTO tab_part_0309_3 VALUES(SYSDATE, 'abrownfox');

1）             导致自动创建新分区

2）             分区字段值为2012-04-08，是从根分区往后的一个月时间。

3）             分区名称自动生成，如SYS_P21

4）             如果插入的时间在两个月以后或者更久，则Oracle只生成必须的分区，并不会生成连续分区。例如insert的时间是2012-06-01，则只会生成2012-06-08分区，至于中间的2012-05-08，2012-04-08分区并不生成。

 

2. 分区索引

跟表一样，索引也分为普通索引与文艺索引（就当分区索引比较文艺一点吧）。可以为一张普通表创建分区索引（不过不能创建Local分区索引，后面解释），也可以为一张分区表创建普通索引。

2.1. 分区索引分两类

全局分区索引(global partitioning index)

本地分区索引(local partitioning index)

 

2.2. 全局分区索引

与表的分区没有关系，创建索引时可以指定任意的列作为索引分区的Key，创建语法如下：

CREATE TABLE tab_part_0321_2(val DATE, val2 NUMBER)
PARTITION BY RANGE(val)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd'))
);


CREATE INDEX tab_part_0321_2_idx ON tab_part_0321_2 (val2)
   GLOBAL PARTITION BY RANGE (val2)
      (PARTITION p1 VALUES LESS THAN (2),
       PARTITION p2 VALUES LESS THAN (3),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

注意这里使用了global关键字， 并且索引分区使用的Key是val2字段，而不是表分区使用的val字段。

那么是否可以在为字段val2创建索引但是使用val字段作为分区的key呢？

CREATE INDEX tab_part_0321_2_idx2 ON tab_part_0321_2 (val2)
   GLOBAL PARTITION BY RANGE (val)
      (PARTITION pa VALUES LESS THAN (to_date('20120308','yyyymmdd')),
       PARTITION pb VALUES LESS THAN (to_date('20120408','yyyymmdd')),
       PARTITION pc VALUES LESS THAN (MAXVALUE));
ORA-14038 global partitioned index must be prefixed.

Prefixed即“前缀索引”，指索引键与分区键是相同的，Oracle无法创建非前缀的全局分区索引。

 

2.3. 本地分区索引

只有分区表才能创建本地分区索引，本地分区索引始终使用与分区表相同的字段进行分区（不需要partition by子句），因此索引的分区与表的分区是一一对应的。我们看看语法：

CREATE INDEX tab_part_0321_2_idx3 ON tab_part_0321_2 (val) LOCAL

尝试：

CREATE INDEX tab_part_0321_2_idx4 ON tab_part_0321_2 (val2) LOCAL
ORA-01408 such column list already indexed

这是因为在前一小节我们在这个列上创建过索引了，我们回去把tab_part_0321_2_idx移除，重新执行上面的语句便可创建成功，这说明跟global分区索引不同，local分区索引允许创建“非前缀索引”。

 

注：关于前面提到的本地前缀索引(local prefixed index)与本地非前缀索引(local non-prefixed index)的概念，有兴趣的可以看看OTN的一些讨论帖子，比如：https://forums.oracle.com/forums/thread.jspa?threadID=2150455&start=0&tstart=0