Oracle Hash分區加速分析函數查詢：原理驗證與實測數據

核心假設

分析函數 (RANK, ROW_NUMBER, SUM() OVER 等) 依 PARTITION BY 欄位劃分計算窗口。若表的Hash分區鍵 = PARTITION BY欄位，各分區資料獨立計算，無跨分區交換，理應大幅提升效能。

驗證實驗設計

1. 建立測試表

-- Hash分區表 (8個分區，分區鍵 = OBJECT_TYPE)
CREATE TABLE t_partition_hash (...) 
PARTITION BY HASH (object_type) (
  PARTITION t_hash_p1 TABLESPACE users, ... PARTITION t_hash_p8 TABLESPACE users
);

-- 普通堆疊表 (同結構)
CREATE TABLE t_big_hash (...);

2. 資料準備

INSERT INTO t_partition_hash SELECT * FROM dba_objects;
INSERT INTO t_partition_hash SELECT * FROM dba_objects;  -- 重複插入擴大資料量
-- 總計 1,610,880 筆

3. 分析函數查詢 (5個RANK)

BEGIN
  INSERT INTO t_rank
  SELECT object_id,
    RANK() OVER (PARTITION BY object_type ORDER BY object_id) r_object_id,
    RANK() OVER (PARTITION BY object_type ORDER BY subobject_name) r_subobject_name,
    RANK() OVER (PARTITION BY object_type ORDER BY created) r_created,
    RANK() OVER (PARTITION BY object_type ORDER BY last_ddl_time) r_last_ddl_time,
    RANK() OVER (PARTITION BY object_type ORDER BY status) r_object_type
  FROM t_partition_hash;  -- 或 t_big_hash
END;

實測結果 (5次執行平均)

原理解析

• 普通表：Oracle需對全表按OBJECT_TYPE進行全域排序或Hash分區，產生大量臨時段I/O與跨CPU協調
• Hash分區表：資料依OBJECT_TYPE預先分散在8個分區，各分區獨立並行計算，無資料重分佈開銷
• 分區數 = CPU核心數時效果最佳 (本測試8分區)

適用場景與限制

• ✅ 大量資料的分析函數查詢 (PARTITION BY 欄位基數適中)
• ✅ 資料倉庫/OLAP系統，載入後分區固定
• ❌ OLTP高併發寫入 (分區維護開銷大)
• ❌ PARTITION BY 欄位與分區鍵不一致時無效

擴展建議

1. 結合平行查詢 (PARALLEL 8) 進一步提升
2. 考慮複合分區 (Range-Hash) 滿足時間範圍查詢+分析函數雙重需求
3. 監控V$PQ_TQSTAT 驗證平行執行分佈均勻度

結論：Hash分區與分析函數PARTITION BY鍵對齊，是大數據量分析查詢的銀彈，實測效能提升2-3倍，隨資料量增長優勢更明顯。