Oracle 資料表分區策略實務：從表篩選到分區鍵設計

分區優勢

• 邏輯上獨立，維護（刪除、移動、分割）不影響其他分區
• 合適的分區鍵可大幅加速查詢（分區修剪）
• 支援大型資料管理：備份、歸檔、清理更彈性

步驟 1：識別需分區的表

方法 A：稽核存取頻率

1. 啟用稽核：AUDIT SELECT, INSERT, UPDATE, DELETE ON schema.table BY ACCESS;
2. 蒐集 DBA_AUDIT_OBJECT 資訊，彙總存取次數
3. 關閉稽核：NOAUDIT ...

方法 B：結合資料量與存取熱度

篩選條件：資料量大（百萬列以上）且存取頻繁。本案例中，doctor_orders、orders、orders_costs 符合條件，重點處理 orders（醫囑執行主表）。

步驟 2：選擇分區鍵

收集統計資訊

ANALYZE TABLE orders ESTIMATE STATISTICS SAMPLE 20 PERCENT;

查詢 DBA_TAB_COLUMNS 觀察候選欄位的值分布：

• ORDER_CODE：分布不均，不適合
• ORDER_CLASS、ORDERING_DEPT：分布均勻，但隨時間增長分區會傾斜
• START_DATE_TIME：時間欄位，天然適合 Range Partitioning

推薦策略：複合分區

CREATE TABLE orders (
  order_id      NUMBER,
  start_date_time DATE,
  ordering_dept   VARCHAR2(10),
  ...
)
PARTITION BY RANGE (start_date_time)
SUBPARTITION BY HASH (ordering_dept)
SUBPARTITIONS 8
(
  PARTITION p_2023_01 VALUES LESS THAN (TO_DATE('2023-02-01','YYYY-MM-DD')),
  PARTITION p_2023_02 VALUES LESS THAN (TO_DATE('2023-03-01','YYYY-MM-DD')),
  ...
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

優點

• 每月一個 Range 分區，符合業務查詢習慣（多為月度統計）
• Hash 子分區將同月資料均勻分散，平行查詢效能佳
• 歷史月份可整體 DROP PARTITION 或 ARCHIVE，維運簡單
• 跨月查詢仍受益於分區修剪

實務建議

• 分區鍵盡可能選擇 查詢 WHERE 條件常用欄位
• 避免在分區鍵上使用函數（如 TRUNC(date_col)）導致分區修剪失效
• 定期 ANALYZE 或 DBMS_STATS 維持統計資訊準確
• 監控 DBA_TAB_PARTITIONS 大小，適時 SPLIT 或 MERGE