Oracle 物化視圖日誌與建立實戰：指定表空間、延遲建置、定時刷新完整腳本

物化視圖日誌記錄基表 DML 變更，支援 FAST REFRESH。實戰腳本示範：日誌建立於專用表空間、視圖延遲建置、定時強制刷新、索引建立、清理腳本。

一、物化視圖日誌建立 (指定表空間)

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);

• WITH ROWID：記錄 Rowid，支援匿名區塊快速刷新。
• SEQUENCE(col)：記錄序列值，支援混合 DML (INSERT/UPDATE/DELETE) 快速刷新。

二、物化視圖建立完整腳本

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:NSS')
NEXT TRUNC(SYSDATE+1)+18/24
AS
SELECT levydetaildataid, detaildatano, taxtermbegin, taxtermend,
       ROUND(taxdeduct*taxpercent1,2) - ROUND(taxdeduct*taxpercent2,2) - ... AS taxdeduct,
       a.levydataid, a.budgetitemcode, taxtypecode, ...
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';

• BUILD DEFERRED：不立即執行查詢，首次由 START WITH 觸發。
• REFRESH FORCE ON DEMAND：手動/排程刷新，優先 FAST。
• NEXT TRUNC(SYSDATE+1)+18/24：每天 18:00 刷新。

三、索引與清理

-- 物化視圖可建立索引，語法同普通表
CREATE INDEX idx_mv_levydataid ON MV_LVY_LEVYDETAILDATA (levydataid) TABLESPACE ZGMV_IDX;

-- 刪除順序：先視圖，後日誌
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;

四、關鍵提醒

1. 日誌表空間與視圖表空間分離，避免爭用。
2. 大量基表變更時，COMPLETE REFRESH 可能比多次 FAST 更快，需實測決定。
3. 11g+ 可用 DBMS_MVIEW.REFRESH 並行刷新多個視圖。