Oracle物化視圖完整指南：類型、刷新策略、查詢重寫與實戰案例

一、物化視圖核心特性

• 目的：預先計算聚合/連接結果，大幅提升查詢效能
• 透明性：應用端SQL無需修改，由優化器自動重寫
• 儲存成本：佔用實體空間，需權衡
• 資料同步：基表異動時需刷新維護一致性

二、三大類型與快速刷新限制差異

三、建立選項詳細說明

1. 建立模式 (BUILD)

• BUILD IMMEDIATE (預設)：建立即填入資料
• BUILD DEFERRED：延遲填入，後續手動/排程刷新

2. 查詢重寫 (QUERY REWRITE)

• ENABLE QUERY REWRITE：允許優化器自動重導查詢
• DISABLE QUERY REWRITE (預設)：不啟用
• 需QUERY_REWRITE_INTEGRITY = TRUSTED/STALE_TOLERATED

3. 刷新機制 (REFRESH)

4. 物化視圖日誌 (MV LOG) - 快速刷新前提

CREATE MATERIALIZED VIEW LOG ON base_table
TABLESPACE mv_log_ts
WITH ROWID, SEQUENCE (pk_col) INCLUDING NEW VALUES;

• ROWID / PRIMARY KEY：識別變更行方式
• SEQUENCE：支援混合DML操作
• INCLUDING NEW VALUES：記錄新值供FAST REFRESH使用

四、進階特性

• 分區物化視圖：支援PCT (Partition Change Tracking)，分區維護後仍可快速刷新
• 彙總層級：GROUP BY CUBE/ROLLUP建立多層級聚合
• ON PREBUILT TABLE：掛載既有表，DROP MV不刪除基表
• 索引支援：物化視圖可建立索引，語法同一般表

五、實戰案例：稅務明細彙總物化視圖

-- 1. 建立基表日誌
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, SEQUENCE(LEVYDATAID);

-- 2. 建立物化視圖 (延遲建立、強制刷新、按需、排程)
CREATE MATERIALIZED VIEW MV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA
BUILD DEFERRED
REFRESH FORCE ON DEMAND
START WITH TO_DATE('24-11-2005 18:00:10','DD-MM-YYYY HH24:MI:SS')
NEXT TRUNC(SYSDATE+1)+18/24
AS
SELECT a.levydetaildataid, a.detaildatano, a.taxtermbegin, a.taxtermend,
       ROUND(a.taxdeduct * b.taxpercent1, 2) - ... AS taxdeduct,
       ...
FROM tb_lvy_levydetaildata a, tb_lvy_levydata c, MV_LVY_LEVYTAXBGTDIV b
WHERE a.levydataid = c.levydataid
  AND a.budgetdistrscalecode = b.budgetdistrscalecode
  AND a.budgetitemcode = b.budgetitemcode
  AND c.incomeresidecode = b.rcvfisccode
  AND c.taxstatuscode = '08'
  AND c.negativeflag != '9';

-- 3. 基表異動頻繁時，改為延遲刷新避免鎖衝突
ALTER MATERIALIZED VIEW user_order_mavi
  DISABLE QUERY REWRITE
  REFRESH ON DEMAND;

六、清除腳本

DROP MATERIALIZED VIEW MV_LVY_LEVYDETAILDATA;
DROP MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv;
DROP MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata;
DROP MATERIALIZED VIEW LOG ON tb_lvy_levydata;

關鍵提醒：ON COMMIT刷新會延長基表交易時間，高併發OLTP慎用；大型聚合視圖建議BUILD DEFERRED+夜間批次FAST REFRESH。

1. 上一篇文章： Oracle排序作業優化完整指南：記憶體、臨時表空間與監控實戰
2. 下一篇文章： Oracle資料存取路徑全解析：全表掃描、ROWID、四種索引掃描

Oracle 資料庫基礎

Oracle Listener (tnslsnr) 未設密碼的安全風險與防護指南

風險描述 Oracle Listener 預設未設定密碼，攻

Oracle 伺服器進程深度解析：專用 vs 共享伺服器架構、連線與 Session 差異、Autotrace 產生雙 Session 實證

Oracle 進程三大類 類別代表進程職責 伺服器進程專用

Oracle 多資料庫配置指南

核心概念 Oracle 可配置多個資料庫，只需啟動一個監聽器

Oracle PL/SQL 動態建構與填充 Collection：EXTEND、初始化與元素賦值完整範例

應用場景 需在程序內部「從零開始」建立一個 Collecti