Oracle 分區表完整操作指南：Range、Hash、Composite 三大策略與維運實戰

為何需要分區表？

TB 級資料量下，分區表提供：

• 可用性：單分區故障不影響其他分區存取。
• 維運性：針對單分區備份/重建/移動，不鎖全表。
• I/O 平衡：分區分散至不同磁碟/表空間。
• 查詢效能：Partition Pruning (分區修剪) 僅掃描相關分區。

三大分區策略

1. Range Partitioning (範圍分區)

依數值/日期區間分割，最常用於時間序列資料。

-- 建立表空間
CREATE TABLESPACE dinya_space01 DATAFILE '/data/dinya01.dbf' SIZE 50M;
CREATE TABLESPACE dinya_space02 DATAFILE '/data/dinya02.dbf' SIZE 50M;
CREATE TABLESPACE dinya_space03 DATAFILE '/data/dinya03.dbf' SIZE 50M;

-- 交易 ID 範圍分區
CREATE TABLE material_transactions (
  transaction_id NUMBER PRIMARY KEY,
  item_id        NUMBER(8) NOT NULL,
  item_desc      VARCHAR2(300),
  trans_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
);

-- 交易日期範圍分區 (更符合業務)
CREATE TABLE material_transactions (
  ...
)
PARTITION BY RANGE (trans_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
);

2. Hash Partitioning (雜湊分區)

依雜湊演算法均勻分散，適合無明顯範圍特徵、需平衡 I/O 的場景。

CREATE TABLE material_transactions (
  transaction_id NUMBER PRIMARY KEY,
  item_id        NUMBER(8) NOT NULL,
  item_desc      VARCHAR2(300),
  trans_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
);

3. Composite Partitioning (複合分區)

兩層分區：第一層 Range，第二層 Hash (或反向)，兼顧時間範圍修剪與 I/O 平衡。

CREATE TABLE material_transactions (
  transaction_id NUMBER PRIMARY KEY,
  item_id        NUMBER(8) NOT NULL,
  item_desc      VARCHAR2(300),
  trans_date     DATE
)
PARTITION BY RANGE (trans_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)
);

分區表 DML 操作

插入 (自動路由)

INSERT INTO material_transactions VALUES (1, 12, 'BOOKS', SYSDATE);
INSERT INTO material_transactions VALUES (3, 12, 'BOOKS', TO_DATE('2006-05-30','YYYY-MM-DD'));
COMMIT;

指定分區查詢/修改/刪除 (加速定位)

-- 查詢特定分區
SELECT * FROM material_transactions PARTITION (part_01);

-- 更新特定分區 (若條件資料不在該分區，0 rows updated)
UPDATE material_transactions PARTITION (part_01) t
SET t.item_desc = 'DESK' WHERE t.transaction_id = 1;

-- 刪除特定分區資料
DELETE FROM material_transactions PARTITION (part_02) t
WHERE t.transaction_id = 4;

注意：指定 PARTITION 子句時，DML 僅作用於該分區；條件不匹配該分區資料則不生效 (不會跨分區)。

分區索引：Local vs Global

Local Index 範例

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

Global Index 範例

CREATE INDEX idx_item_global ON material_transactions (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
);

分區維運：ADD / MERGE / DROP / SPLIT

新增分區 (ADD PARTITION)

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

規則：新分區上界必須大於現有最大分區上界，否則 ORA-14074。

合併分區 (MERGE PARTITIONS)

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

結果分區名必須為較高邊界分區名，否則 ORA-14275。

刪除分區 (DROP PARTITION)

ALTER TABLE material_transactions DROP PARTITION part_01;

警告：資料永久刪除！執行前必須備份或先 MERGE 保留資料。

分割分區 (SPLIT PARTITION) - 補充

ALTER TABLE material_transactions
SPLIT PARTITION part_03 AT (TO_DATE('2015-01-01','YYYY-MM-DD'))
INTO (PARTITION part_03 TABLESPACE dinya_space03,
      PARTITION part_04 TABLESPACE dinya_space04);

索引維運同步

DROP/MERGE/SPLIT 分區後，Global Index 會變 UNUSABLE，需 ALTER INDEX ... REBUILD。Local Index 自動維護。

效能驗證：執行計畫確認 Partition Pruning

EXPLAIN PLAN FOR
SELECT * FROM material_transactions PARTITION (part_01) WHERE item_id = 12;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 關鍵字：PARTITION RANGE SINGLE / PARTITION HASH SINGLE / PARTITION LIST SINGLE

最佳實務總結

1. 依業務查詢模式選擇分區鍵 (時間 → Range；均勻分散 → Hash；兩者兼顧 → Composite)。
2. 分區數量建議 50-200 之間，過多增加字典管理開銷。
3. 優先使用 Local Index，簡化維運。
4. 建立分區維運 SOP：備份 → 操作 → 驗證索引 → 統計收集 (DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY=>'PARTITION')。
5. 利用 EXCHANGE PARTITION 實現歷史資料零停機歸檔/載入。

結語

分區表是 Oracle 處理海量資料的核心利器。掌握 Range/Hash/Composite 三大策略、Local/Global 索引選擇、DML 指定分區語法、ADD/MERGE/DROP/SPLIT 維運指令，配合 Partition Pruning 驗證，即可駕馭 TB 級資料系統，實現高效能、高可用、易維運的資料架構。