Oracle 表分區策略實戰：從表識別到分區鍵選擇與維護

為什麼要分區？

大表未分區將無法發揮 Oracle 管理海量數據的優勢。分區帶來三大核心收益：

1. 邏輯獨立性：每個分區可獨立維護（刪除、移動、拆分）不影響其他分區
2. 查詢加速：分區裁剪 讓掃描範圍縮小數量級
3. 可管理性：備份、恢復、清理歷史數據可按分區並行操作

第一階段：識別需分區的表

方法：基於訪問頻度的審計分析

1. 開啟審計：

   SET ECHO OFF FEED OFF VER OFF PAGES 0
   SPOOL audon.sql
   SELECT 'AUDIT SELECT ON '||owner||'.'||object_name||' BY ACCESS;'
   FROM dba_objects
   WHERE object_type IN ('TABLE','VIEW') AND owner IN ('ORDADM');
   SPOOL OFF
   @audon.sql

2. 建立彙總表並收集 24 小時訪問數據：

   CREATE TABLE aud_summary (obj_name VARCHAR2(30), owner VARCHAR2(30), hits NUMBER);
   INSERT INTO aud_summary SELECT obj_name, owner, COUNT(*) FROM dba_audit_object GROUP BY obj_name, owner;

3. 關閉審計並清理：

   @audoff.sql
   DELETE FROM sys.aud$;

分析結果範例（醫院 HIS 系統）

重點處理 ORDERS 表（護士站執行醫囑，日增萬條，與計費緊密關聯）。

第二階段：選擇分區鍵

使用 ANALYZE TABLE ... ESTIMATE STATISTICS SAMPLE 20 PERCENT 收集統計信息，查詢 DBA_TAB_COLUMNS.NUM_DISTINCT：

最佳策略：範圍-哈希組合分區

• 一級範圍分區：按 START_DATE_TIME 每月一個分區
• 二級哈希子分區：按 ORDERING_DEPT 或 ORDER_CODE 每分區 3 個子分區

優勢：月度統計查詢僅掃描單分區，跨月查詢並行度高，歷史分區易歸檔/刪除。

第三階段：分區表 DDL 定義

CREATE TABLE orders (
  patient_id      VARCHAR2(10) NOT NULL,
  order_no        NUMBER(4),
  order_sub_no    NUMBER(2),
  order_class     CHAR(1),
  order_text      VARCHAR2(80),
  order_code      VARCHAR2(10),
  start_date_time DATE,
  stop_date_time  DATE,
  ordering_dept   VARCHAR2(8),
  ...
  STORAGE (INITIAL 100M NEXT 1M)
)
PARTITION BY RANGE (start_date_time)
SUBPARTITION BY HASH (order_code)
SUBPARTITIONS 3 STORE IN (ordersub1, ordersub2, ordersub3)
(
  PARTITION orders200212 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE tsp_orders,
  PARTITION orders200301 VALUES LESS THAN (TO_DATE('2003-02-01','YYYY-MM-DD')) TABLESPACE tsp_orders,
  PARTITION orders200302 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE tsp_orders,
  ...
  PARTITION orders200402 VALUES LESS THAN (TO_DATE('2004-03-01','YYYY-MM-DD')) TABLESPACE tsp_orders
);

分區維護操作速查

分區表索引策略

-- 全局索引（跨分區唯一約束）
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (patient_id, visit_id, order_no, order_sub_no)
USING INDEX PCTFREE 5 TABLESPACE tsp_orders;

-- 本地索引（分區對齊，維護更靈活）
CREATE INDEX idx_orders_dept ON orders (ordering_dept) LOCAL;

驗證分區效果

SELECT partition_name, NUM_ROWS FROM user_tab_partitions WHERE table_name='ORDERS';

預期：各月分區行數均勻，查詢計畫顯示 PARTITION RANGE SINGLE/ITERATOR。

總結

分區策略核心三步：審計識別熱表 → 統計分析選鍵 → 範圍+哈希組合分區。配合本地索引、分區級維護、歷史數據歸檔，可將 TB 級表查詢從分鐘級降至秒級，運維成本大幅下降。Oracle 8i+ 版本均完美支援，社保、電信、醫療等大數據場景強烈推薦。