Oracle 分區表操作完整指南（第一部分）

分區表優勢

• 高可用性： 單一分區故障不影響其他分區資料存取。
• 維護便利： 僅需修復受損分區，縮短停機時間。
• I/O 負載均衡： 將不同分區對應至不同磁碟，提升整體效能。
• 查詢加速： 透過分區修剪，僅掃描相關分區，大幅降低檢索成本。

Oracle 三大分區類型

1. 範圍分區
2. 雜湊分區
3. 複合分區

建立測試表空間

CREATE TABLESPACE dinya_space01 DATAFILE '/test/demo/oracle/demodata/dinya01.dnf' SIZE 50M;
CREATE TABLESPACE dinya_space02 DATAFILE '/test/demo/oracle/demodata/dinya02.dnf' SIZE 50M;
CREATE TABLESPACE dinya_space03 DATAFILE '/test/demo/oracle/demodata/dinya03.dnf' SIZE 50M;

1.1 分區表建立範例

1.1.1 範圍分區

依據欄位數值範圍決定資料存放分區。常見於交易序號、建立日期等欄位。

依交易序號分區

CREATE TABLE dinya_test (
  transaction_id NUMBER PRIMARY KEY,
  item_id NUMBER(8) NOT NULL,
  item_description VARCHAR2(300),
  transaction_date DATE NOT NULL
)
PARTITION BY RANGE (transaction_id) (
  PARTITION part_01 VALUES LESS THAN (30000000) TABLESPACE dinya_space01,
  PARTITION part_02 VALUES LESS THAN (60000000) TABLESPACE dinya_space02,
  PARTITION part_03 VALUES LESS THAN (MAXVALUE) TABLESPACE dinya_space03
);

交易 ID < 3,000 萬存放於 part_01，3,000 萬~6,000 萬存 part_02，其餘存 part_03。

依交易日期分區

CREATE TABLE dinya_test (
  transaction_id NUMBER PRIMARY KEY,
  item_id NUMBER(8) NOT NULL,
  item_description VARCHAR2(300),
  transaction_date DATE NOT NULL
)
PARTITION BY RANGE (transaction_date) (
  PARTITION part_01 VALUES LESS THAN (TO_DATE('2006-01-01','YYYY-MM-DD')) TABLESPACE dinya_space01,
  PARTITION part_02 VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD')) TABLESPACE dinya_space02,
  PARTITION part_03 VALUES LESS THAN (MAXVALUE) TABLESPACE dinya_space03
);

亦可結合多欄位：PARTITION BY RANGE (transaction_id, transaction_date)。

1.1.2 雜湊分區

透過雜湊演算法將資料均勻分散至指定分區，適合無明顯範圍特徵的欄位。

CREATE TABLE dinya_test (
  transaction_id NUMBER PRIMARY KEY,
  item_id NUMBER(8) NOT NULL,
  item_description VARCHAR2(300),
  transaction_date DATE
)
PARTITION BY HASH (transaction_id) (
  PARTITION part_01 TABLESPACE dinya_space01,
  PARTITION part_02 TABLESPACE dinya_space02,
  PARTITION part_03 TABLESPACE dinya_space03
);

1.1.3 複合分區

先依範圍分區，再於各分區內以雜湊方式進一步細分。

CREATE TABLE dinya_test (
  transaction_id NUMBER PRIMARY KEY,
  item_id NUMBER(8) NOT NULL,
  item_description VARCHAR2(300),
  transaction_date DATE
)
PARTITION BY RANGE (transaction_date)
SUBPARTITION BY HASH (transaction_id)
SUBPARTITIONS 3 STORE IN (dinya_space01, dinya_space02, dinya_space03) (
  PARTITION part_01 VALUES LESS THAN (TO_DATE('2006-01-01','YYYY-MM-DD')),
  PARTITION part_02 VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD')),
  PARTITION part_03 VALUES LESS THAN (MAXVALUE)
);

1.2 分區表資料操作實測（以日期範圍分區為例）

1.2.1 插入資料

INSERT INTO dinya_test VALUES (1, 12, 'BOOKS', SYSDATE);
INSERT INTO dinya_test VALUES (2, 12, 'BOOKS', SYSDATE+30);
INSERT INTO dinya_test VALUES (3, 12, 'BOOKS', TO_DATE('2006-05-30','YYYY-MM-DD'));
INSERT INTO dinya_test VALUES (4, 12, 'BOOKS', TO_DATE('2007-06-23','YYYY-MM-DD'));
INSERT INTO dinya_test VALUES (5, 12, 'BOOKS', TO_DATE('2011-02-26','YYYY-MM-DD'));
INSERT INTO dinya_test VALUES (6, 12, 'BOOKS', TO_DATE('2011-04-30','YYYY-MM-DD'));
COMMIT;

系統自動依 transaction_date 將資料導向對應分區。

1.2.2 查詢指定分區

SELECT * FROM dinya_test PARTITION (part_01);
SELECT * FROM dinya_test PARTITION (part_02);
SELECT * FROM dinya_test PARTITION (part_03);

指定分區查詢可大幅縮減掃描範圍，提升大量資料檢索速度。亦可不指定分區查詢全表。

1.2.3 更新資料

UPDATE dinya_test PARTITION (part_01) t SET t.item_description = 'DESK' WHERE t.transaction_id = 1;

若指定分區與 WHERE 條件不符（如在 part_01 更新 ID=6），將不更新任何列。

1.2.4 刪除資料

DELETE FROM dinya_test PARTITION (part_02) t WHERE t.transaction_id = 4;

同理，分區與條件不符時不刪除資料。

1.3 分區索引

分區表支援區域索引與全域索引。需確保跨分區唯一性時使用全域索引。

1.3.1 區域索引建立

CREATE INDEX dinya_idx_t ON dinya_test(item_id)
LOCAL (
  PARTITION idx_1 TABLESPACE dinya_space01,
  PARTITION idx_2 TABLESPACE dinya_space02,
  PARTITION idx_3 TABLESPACE dinya_space03
);

執行計畫顯示 TABLE ACCESS (BY LOCAL INDEX ROWID)，確認索引生效。

1.3.2 全域索引建立

CREATE INDEX dinya_idx_t ON dinya_test(item_id)
GLOBAL PARTITION BY RANGE (item_id) (
  PARTITION idx_1 VALUES LESS THAN (1000) TABLESPACE dinya_space01,
  PARTITION idx_2 VALUES LESS THAN (10000) TABLESPACE dinya_space02,
  PARTITION idx_3 VALUES LESS THAN (MAXVALUE) TABLESPACE dinya_space03
);

亦可不指定分區直接建立全域索引：CREATE INDEX dinya_idx_t ON dinya_test(item_id);

1.4 分區維護操作

1.4.1 新增分區

ALTER TABLE dinya_test
ADD PARTITION part_04 VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD'))
TABLESPACE dinya_space03;

新增分區的上界值必須大於現有最大分區上界，否則報 ORA-14074。

1.4.2 合併分區

ALTER TABLE dinya_test MERGE PARTITIONS part_01, part_02 INTO PARTITION part_02;

合併後的分區名稱不可為較低邊界的分區名（如 part_01），否則報 ORA-14275。

1.4.3 刪除分區

ALTER TABLE dinya_test DROP PARTITION part_01;

警告： 刪除分區將永久移除該分區資料，執行前請務必備份或改用合併方式。

1.5 總結

本文示範均指定分區操作，以展示分區修剪效能優勢。實務上亦可不指定分區直接操作。分區索引建立與使用與一般表無異。維護分區後可能需重建索引，詳情請參閱官方文件。