Oracle 約束與分區表管理完整實戰指南

前言：完整性約束是資料品質的守門員，分區表是大資料效能的基石

約束與分區表看似獨立，實則緊密相關：分區表上的主鍵/唯一約束決定索引類型（LOCAL/GLOBAL），約束狀態控制資料載入效能，分區維護操作需考慮約束驗證成本。本文系統整理五大約束類型、四種狀態語義、修正違規資料流程，並詳細涵蓋五種分區策略、組合分區、維護操作與資料字典。

一、 完整性約束：類型、狀態與生命週期

1. 五大約束類型

2. 四種啟用/驗證狀態（關鍵差異）

3. 常用 DDL 速查

-- 建表時定義
CREATE TABLE t (
  id NUMBER CONSTRAINT pk_id PRIMARY KEY,
  val NUMBER NOT NULL,
  code VARCHAR2(10) CONSTRAINT uk_code UNIQUE,
  status VARCHAR2(1) CONSTRAINT chk_status CHECK (status IN ('A','I','P')),
  parent_id NUMBER CONSTRAINT fk_parent REFERENCES parent_t(id)
);

-- 事後新增/修改/刪除
ALTER TABLE t ADD CONSTRAINT uk_email UNIQUE (email);
ALTER TABLE t MODIFY (val NOT NULL);
ALTER TABLE t DROP CONSTRAINT uk_code;
ALTER TABLE t DROP PRIMARY KEY;

-- 狀態切換
ALTER TABLE t DISABLE NOVALIDATE CONSTRAINT pk_id;  -- 載入前
-- 大量載入...
ALTER TABLE t ENABLE NOVALIDATE CONSTRAINT pk_id;   -- 載入後快速啟用（不驗證舊資料）
-- 後續需完全驗證時
ALTER TABLE t ENABLE VALIDATE CONSTRAINT pk_id;     -- 會檢查所有資料，若違規報錯

二、 違規資料修正標準流程

場景：欲啟用 PK 約束，但現有資料有重複/NULL。

1. 移除約束：ALTER TABLE t DROP CONSTRAINT pk_id;
2. 建立 EXCEPTIONS 表：@$ORACLE_HOME/rdbms/admin/utlexcpt.sql（建立 EXCEPTIONS 表）
3. 嘗試啟用並導向異常表：

   ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY (id) EXCEPTIONS INTO exceptions;

4. 查詢違規行：

   SELECT t.* FROM t, exceptions e WHERE t.rowid = e.row_id;

5. 修正資料（更新/刪除/合併）
6. 重建約束：ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY (id);

關鍵提示

• 若約束為 PK/UK，DISABLE 會刪除索引，ENABLE NOVALIDATE 重啟前**必須先重建索引**：CREATE UNIQUE INDEX pk_id ON t(id);
• 大表修正建議使用 DBMS_REDEFINITION 線上重組，避免長時間鎖表

三、 分區表：五大策略與組合分區

1. 範圍分區 —— 時間序列首選

CREATE TABLE sales (
  sale_id NUMBER, sale_date DATE, amount NUMBER
) PARTITION BY RANGE (sale_date) (
  PARTITION p_2023_q1 VALUES LESS THAN (DATE '2023-04-01') TABLESPACE ts_2023_q1,
  PARTITION p_2023_q2 VALUES LESS THAN (DATE '2023-07-01') TABLESPACE ts_2023_q2,
  PARTITION p_max VALUES LESS THAN (MAXVALUE) TABLESPACE ts_max
);

• 新增：ALTER TABLE sales ADD PARTITION p_2023_q3 VALUES LESS THAN (DATE '2023-10-01') TABLESPACE ts_2023_q3;
• 刪除：ALTER TABLE sales DROP PARTITION p_2023_q1;（瞬間完成，資料連同索引一起刪除）

2. 雜湊分區 —— 載入平衡、平行度最大化

CREATE TABLE orders (
  order_id NUMBER, cust_id NUMBER, ...
) PARTITION BY HASH (cust_id)
  PARTITIONS 16
  STORE IN (ts_01, ts_02, ts_03, ts_04);

• 新增：ALTER TABLE orders ADD PARTITION p_new TABLESPACE ts_05;
• 合併：ALTER TABLE orders COALESCE PARTITION;（減少 1 個分區，資料重新雜湊分佈）

3. 列表分區 —— 離散值精確控制

CREATE TABLE regions (
  region_id NUMBER, country_code VARCHAR2(2), ...
) PARTITION BY LIST (country_code) (
  PARTITION p_asia VALUES ('CN','JP','KR','TW','HK') TABLESPACE ts_asia,
  PARTITION p_eu VALUES ('DE','FR','IT','ES') TABLESPACE ts_eu,
  PARTITION p_other VALUES (DEFAULT) TABLESPACE ts_other
);

• 插入 'US' 會進入 p_other，插入 'XX' 報錯 ORA-14400
• 新增值：ALTER TABLE regions MODIFY PARTITION p_asia ADD VALUES ('SG','MY');

4. 範圍-雜湊組合分區 —— 時間滾動 + 載入平衡

CREATE TABLE logs (
  log_id NUMBER, log_date DATE, source_id NUMBER, msg VARCHAR2(4000)
) PARTITION BY RANGE (log_date)
  SUBPARTITION BY HASH (source_id) SUBPARTITIONS 8
  STORE IN (ts_log_01, ts_log_02, ts_log_03, ts_log_04) (
  PARTITION p_2024_01 VALUES LESS THAN (DATE '2024-02-01'),
  PARTITION p_2024_02 VALUES LESS THAN (DATE '2024-03-01')
);

• 查詢：SELECT * FROM logs PARTITION (p_2024_01); 或 SUBPARTITION (p_2024_01_SYS_SUBP1);
• 新增主分區自動建立 8 子分區

5. 範圍-列表組合分區 —— 時間 + 業務區域雙維度

CREATE TABLE sales_fact (
  sale_id NUMBER, sale_date DATE, region_id NUMBER, amount NUMBER
) PARTITION BY RANGE (sale_date)
  SUBPARTITION BY LIST (region_id) (
  PARTITION p_2024_q1 VALUES LESS THAN (DATE '2024-04-01') (
    SUBPARTITION p_2024_q1_asia VALUES (1,2,3) TABLESPACE ts_asia,
    SUBPARTITION p_2024_q1_eu VALUES (4,5,6) TABLESPACE ts_eu
  ),
  PARTITION p_2024_q2 VALUES LESS THAN (DATE '2024-07-01') (
    SUBPARTITION p_2024_q2_asia VALUES (1,2,3) TABLESPACE ts_asia,
    SUBPARTITION p_2024_q2_eu VALUES (4,5,6) TABLESPACE ts_eu
  )
);

四、 分區維護高階操作

五、 關鍵資料字典

-- 約束
SELECT * FROM user_constraints WHERE table_name = 'T';
SELECT * FROM user_cons_columns WHERE table_name = 'T';

-- 分區表
SELECT * FROM user_part_tables WHERE table_name = 'SALES';
SELECT * FROM user_tab_partitions WHERE table_name = 'SALES';
SELECT * FROM user_tab_subpartitions WHERE table_name = 'SALES';
SELECT * FROM user_part_key_columns WHERE name = 'SALES';
SELECT * FROM user_subpart_key_columns WHERE name = 'SALES';

-- 分區索引
SELECT * FROM user_part_indexes WHERE table_name = 'SALES';
SELECT * FROM user_ind_partitions WHERE index_name = 'IDX_SALES_DT';

六、 實戰避坑指南

1. 分區表 PK 必須包含分區鍵：否則只能建 GLOBAL 索引，維護成本高
2. FK 參考分區表：父表 PK 包含分區鍵時，子表 FK 也需包含該鍵才能建 LOCAL 索引
3. ENABLE NOVALIDATE 後忘記重建索引導致無法 ENABLE VALIDATE
4. 交換分區結構不匹配：含索引/約束/LOB/虛擬欄位皆需一致
5. MAXVALUE 分區忘記定期拆分：導致熱點、統計資訊失真
6. 子分區模板：使用 SUBPARTITION TEMPLATE 簡化組合分區 DDL

約束保障資料正確，分區賦能大資料高效。兩者結合的核心原則：約束設計服從分區策略，分區維護利用約束狀態切換。建立標準化 DDL 模板、維護 Runbook、定期巡檢腳本，讓複雜度可控、可傳承。