Oracle 資料倉庫三大優化利器：分區、維度與物化視圖

核心原則

資料倉庫優化本質只有兩點：資料分塊存放 (便於轉儲管理、並發存取) + 中間層預計算 (提升查詢速度)。

1. 分區 — 物理分塊存放

表空間隔離

事實表、索引表、維度表部署於不同表空間，最佳實踐對應不同物理磁碟/ASM 磁碟組，發揮並發 I/O 能力。

範圍分區 + 列表子分區 (複合分區)

CREATE TABLE t_lognode_record (
  ...
  callin_dd      NUMBER(2),
  month_mod      NUMBER(1)
)
PARTITION BY RANGE (month_mod)
SUBPARTITION BY LIST (callin_dd)
SUBPARTITION TEMPLATE (
  SUBPARTITION subp1 VALUES (1),
  SUBPARTITION subp2 VALUES (2),
  SUBPARTITION subp3 VALUES (3)
)
(
  PARTITION p0 VALUES LESS THAN (1),
  PARTITION p1 VALUES LESS THAN (2),
  PARTITION p2 VALUES LESS THAN (3)
);

• month_mod = 月份 MOD 3：自動將月份均勻映射到 3 個分區，免人工維護。
• 子分區按日期 (callin_dd) 進一步切分，單一分區/子分區大小可控。
• 查詢時指定 PARTITION (p0) SUBPARTITION (subp1) 可實現分區剪枝，速度提升百倍。

分區類型選擇

Oracle 僅支援：Range-List、Range-Hash、Range-Range (多欄位) 複合分區。

分區維護操作

-- 僅查詢/更新特定分區
SELECT * FROM t_lognode_record PARTITION (p0);
UPDATE t_lognode_record PARTITION (p0) SET ...;

-- 快速清除歷史資料 (配合歸檔)
ALTER TABLE t_lognode_record DROP PARTITION p0;
-- 或 TRUNCATE PARTITION p0 (保留分區定義)

-- 匯出/匯入分區資料
EXP/IMP 支援 PARTITION 參數

注意：分區 DDL 會導致全域索引失效，需 ALTER INDEX ... REBUILD 或 UPDATE GLOBAL INDEXES 子句 (12c+)。

2. 維度 — 邏輯層級預計算

CREATE TABLE dim_time (
  d_year   VARCHAR2(4),
  d_quarter VARCHAR2(2),
  d_month  VARCHAR2(2),
  d_day    DATE
);

CREATE DIMENSION dim_time
  LEVEL year    IS (dim_time.d_year)
  LEVEL quarter IS (dim_time.d_quarter)
  LEVEL month   IS (dim_time.d_month)
  LEVEL day     IS (dim_time.d_day)
  HIERARCHY y_q_m_d (day CHILD OF month CHILD OF quarter CHILD OF year)
  HIERARCHY y_m_d   (day CHILD OF month CHILD OF year);

• 定義層級與父子關係，啟用 QUERY_REWRITE 後，彙總查詢自動改寫為掃描較高層級預聚合資料。
• 一張表可建多個維度，一個維度可含多層次。
• 工具支援：TOAD 可視化維護，PL/SQL Developer 僅能執行 DDL。

3. 物化視圖 — 實體化中間結果

CREATE MATERIALIZED VIEW mv_log_record
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT t.node_id, COUNT(*) AS call_times
FROM   t_lognode_record t
GROUP BY t.node_id;

• REFRESH 策略：FAST (增量、需主鍵/ROWID)、COMPLETE (全量)、FORCE (自動選擇)。
• 觸發方式：ON DEMAND (手動/排程)、ON COMMIT (即時)、START WITH ... NEXT SYSDATE+1/48 (每 30 分鐘)。
• 查詢改寫：啟用 QUERY_REWRITE_ENABLED=TRUE、QUERY_REWRITE_INTEGRITY=TRUSTED 後，Oracle 自動將原始表聚合查詢導向 MV。
• 可跨表 Join、可直接 SELECT * FROM mv_log_record 當表用。

啟用查詢改寫 — 關鍵一步

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

未啟用則 MV 僅作為靜態報表表，無法自動加速臨時查詢。

結語

分區解決「資料放哪裡」、維度解決「怎麼聚合」、物化視圖解決「預存什麼結果」。三者結合，構建出可線性擴展、查詢亞秒級響應的企業級資料倉庫骨架。

1. 上一篇文章： Oracle 資料導入實戰：SQL Developer 完勝 TOAD 的 Excel 匯入體驗
2. 下一篇文章： Oracle EXP/IMP 傳統匯出匯入命令速查手冊

Oracle 資料庫基礎

Oracle 11g 閃回資料歸檔（Flashback Data Archive）新功能深度解析與實戰部署

一、從手工觸發器到原生 FBDA 的演進 過去需自建 _HI

Oracle 多表視圖不可更新？INSTEAD OF 觸發器完美解決

破解多表視圖 DML 限制：INSTEAD OF 觸發器實戰

Windows 排程任務「不論使用者是否登入都要執行」報錯的完整解決方案

錯誤情境 設定排程任務勾選「不論使用者是否登入都要執行」並選

Oracle 11g 新特性搶先看：降低變更風險、強化高可用與自動化運維

背景與發布時程 隨著 Oracle OpenWorld 大會