Oracle 分區表完整操作指南：三大分區策略、索引維護與生命週期管理

一、為什麼需要分區表？

面對 TB 級資料量，分區表提供四大核心價值：

• 可用性增強：單分區故障不影響其他分區存取
• 維護便利：針對性修復/重建單一分區
• I/O 平衡：分區映射不同磁碟，分散熱點
• 查詢加速：分區裁剪只掃描相關分區

二、三大分區策略與建表實戰

先建立三個測試表空間：

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

1. Range Partitioning (範圍分區)

依欄位值範圍劃分，最適合時間序列資料。

-- 依交易 ID 分區
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
);

-- 依交易日期分區 (推薦)
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)。

2. Hash Partitioning (雜湊分區)

依雜湊演算法均勻分布，適合無明顯範圍特徵的大表。

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
);

3. Composite Partitioning (複合分區)

Range + Hash 兩級：主分區按範圍，子分區按雜湊。

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)
);

三、分區表 DML 操作實戰

以日期範圍分區表為例：

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;

資料自動落入對應分區：2006 前 → part_01，2006-2010 → part_02，2010 後 → part_03。

2. 指定分區查詢 (Partition Pruning)

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

大數據量時指定分區可大幅降低掃描量。亦可不指定分區全表掃描。

3. 指定分區更新/刪除

-- 成功：記錄確在 part_01
UPDATE dinya_test PARTITION (part_01) t SET t.item_description = 'DESK' WHERE t.transaction_id = 1;

-- 失敗 (0 rows)：transaction_id=6 在 part_03，指定 part_01 找不到
UPDATE dinya_test PARTITION (part_01) t SET t.item_description = 'DESK' WHERE t.transaction_id = 6;

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

關鍵：指定分區 DML 僅在該分區內生效，條件不匹配則無操作，不會跨分區。

四、分區索引：Local vs Global

1. Local Index 建立

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。

2. Global Index 建立

-- 範圍分區 Global Index
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
);

-- 非分區 Global Index (最常用)
CREATE INDEX dinya_idx_t ON dinya_test(item_id);

執行計畫顯示 TABLE ACCESS BY GLOBAL INDEX ROWID。

五、分區生命週期維護

1. 新增分區 (ADD PARTITION)

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

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

2. 合併分區 (MERGE PARTITIONS)

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

合併後分區名必須為較高界者 (part_02)，指定較低界會報 ORA-14275。

3. 刪除分區 (DROP PARTITION)

ALTER TABLE dinya_test DROP PARTITION part_01;

警告：分區內資料永久遺失！執行前務必備份或改用 MERGE 保留資料。

六、維護對索引的影響

分區維護操作 (ADD/MERGE/DROP/SPLIT/TRUNCATE) 可能導致 Global Index 失效 (UNUSABLE)，Local Index 自動維護。建議：

• 優先使用 Local Index 減少維護負擔
• Global Index 維護操作後重建：ALTER INDEX ... REBUILD PARTITION ... 或 REBUILD
• 12c+ 支援 UPDATE INDEXES 子句自動維護 Global Index

七、總結

分區表核心價值在於「分而治之」。選擇策略原則：時間序列 → Range；均勻分布無熱點 → Hash；兩者兼備 → Composite。DML 指定分區可精準控制掃描範圍，索引選 Local 為主、Global 為輔，維護操作謹慎執行並同步處理索引狀態。