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