您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 物化視圖全攻略：類索引特性、三大類型、刷新策略與分區實戰

編輯：Oracle 資料庫基礎

核心定位：預計算結果集的「類索引」加速器

物化視圖 (Materialized View, MV) 預先計算並儲存表聯結、聚合等耗時操作結果，查詢時直接讀取預計算結果，大幅縮短回應時間。與索引極度相似：

目的：提升查詢效能
對應用透明：增刪不影響 SQL 正確性
佔用儲存空間
基表變更需刷新同步

三大類型與適用場景

類型	特徵	典型場景
聚合物化視圖	含 GROUP BY、SUM/COUNT/AVG 等	銷售彙總、庫存快照、KPI 儀表板
聯結物化視圖	多表 JOIN 預計算	星型模型事實表+維度表、報表聯結
嵌套物化視圖	基於其他 MV 建立	多層彙總（日→月→年）、資料市集分層

快速刷新限制條件三類差異大，其他特性相近。

建立選項決策矩陣

1. 建立方式 (BUILD)

BUILD IMMEDIATE（預設）：建立即填充資料
BUILD DEFERRED：延遲填充，後續手動/排程刷新

2. 查詢重寫 (QUERY REWRITE)

ENABLE QUERY REWRITE：優化器自動改寫查詢導向 MV，避免重複聚合/聯結
DISABLE QUERY REWRITE（預設）：不自動重寫

需設定 QUERY_REWRITE_INTEGRITY = TRUSTED | STALE_TOLERATED 且 QUERY_REWRITE_ENABLED = TRUE。

3. 刷新策略 (REFRESH) — 核心決策

維度	選項	說明
觸發時機	ON DEMAND	手動/排程觸發 (`DBMS_MVIEW.REFRESH` / Job)
觸發時機		ON COMMIT	基表 DML 提交時同步刷新，影響交易延遲
刷新方法	FAST	增量刷新，需 MV Log 記錄變更，最快但限制多
		COMPLETE	全量重建，無限制但耗時長
		FORCE	嘗試 FAST，失敗轉 COMPLETE（預設）
		NEVER	不刷新，僅作靜態快照

預設：FORCE ON DEMAND

物化視圖日誌 (MV Log) — FAST 刷新前置條件

-- 基本建立
CREATE MATERIALIZED VIEW LOG ON base_table
TABLESPACE mv_log_ts
WITH ROWID;  -- 或 PRIMARY KEY

-- 進階：支援聯結/聚合快速刷新
CREATE MATERIALIZED VIEW LOG ON base_table
TABLESPACE mv_log_ts
WITH ROWID, SEQUENCE (col1, col2) INCLUDING NEW VALUES;

ROWID：行遷移場景適用
PRIMARY KEY：主鍵穩定場景優先
SEQUENCE：記錄操作順序，支援混合 DML
INCLUDING NEW VALUES：記錄新值，支援聚合快速刷新

進階特性

ON PREBUILD TABLE：基於現有表建立 MV，DROP MV 不刪除基表，需 QUERY_REWRITE_INTEGRITY = TRUSTED
分區 MV：支援分區變更追蹤 (PCT)，分區維護後仍可 FAST 刷新
GROUP BY CUBE/ROLLUP：單一 MV 產出多層級聚合
ORDER BY：建立時指定排序，但不持久化到定義，刷新後失效

實戰：指定表空間建立 MV 與 MV Log

-- 1. 建立 MV Log 指定表空間
CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv
TABLESPACE ZGMV_DATA
WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
TABLESPACE ZGMV_DATA
WITH ROWID, SEQUENCE (LEVYDETAILDATAID);

CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
TABLESPACE ZGMV_DATA
WITH ROWID;

-- 2. 建立 MV 指定表空間與儲存參數
CREATE MATERIALIZED VIEW mv_sales_summary
TABLESPACE ZGMV_DATA
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT ...
FROM ...;

刷新自動化排程

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'REFRESH_MV_SALES_DAILY',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''MV_SALES_SUMMARY'', ''F''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Daily fast refresh sales summary MV'
  );
END;
/