學習 Materialized View
?
?
??? 目前打算使用物化視圖的方法來同步兩個數據庫之間的數據，這樣肯定會比每天用DB Link定時傳導數據效率來得高，而且邏輯更易控置，不用再去判斷舊的表是否有更改。所以先大致了解一下物化視圖的概念和基本用法：
?
?
一、物化視圖的定義：
?
??? 物化視圖是包括一個查詢結果的數據庫對像(由系統實現定期刷新數據)，物化視圖不是在使用時才讀取，而是預先計算并保存表連接或聚集等耗時較多的操作結果，這樣在查詢時大大提高了讀取速度，特別適用抽取大數據量表某些信息以及數據鏈連接表使用。而且在數據倉庫中使用到重寫機制，對于用戶來說，Oracle會自己選擇合適的物化視圖進行查詢，不需要重新寫SQL。
?
??? 物化視圖還可以進行遠程數據的的本地復制，此時的物化視圖存儲也可以稱為快照。可是用于實施數據庫間的同步。通常情況下，物化視圖用于復制的時候為“主表”，而在運用于數據倉庫時稱為“明細表”。
?
?
二、物化視圖的創建
?
先看一個網上的例子：
?
--創建物化視圖時應先創建存儲的日志空間
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);
?
然后創建物化視圖：
?
--創建物化視圖
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 levydetaildataid, detaildatano, taxtermbegin, taxtermend,
......
ROUND(taxdeduct * taxpercent1, 2) - ROUND(taxdeduct * taxpercent2, 2) -
ROUND(taxdeduct * taxpercent3, 2) - ROUND(taxdeduct * taxpercent4, 2) -
ROUND(taxdeduct * taxpercent5, 2) taxdeduct, ROUND(taxfinal * taxpercent1, 2) -
ROUND(taxfinal * taxpercent2, 2) - ROUND(taxfinal * taxpercent3, 2) -
ROUND(taxfinal * taxpercent4, 2) - ROUND(taxfinal * taxpercent5, 2) taxfinal,
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'
?
說明：
??? 1、創建方式(Build Methods)：?
??? 包括BUILD IMMEDIATE和BUILD DEFERRED兩種。BUILD IMMEDIATE是在創建物化視圖的時候就生成數據，而BUILD DEFERRED則在創建時不生成數據，以后根據需要在生成數據。默認為BUILD IMMEDIATE。
?
??? 2、查詢重寫(Query Rewrite)：?
??? 包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE兩種。分別指出創建的物化視圖是否支持查詢重寫。查詢重寫是指當對物化視圖的基表進行查詢時，Oracle會自動判斷能否通過查詢物化視圖來得到結果，如果可以，則避免了聚集或連接操作，而直接從已經計算好的物化視圖中讀取數據。默認為DISABLE QUERY REWRITE。
?
??? 3、刷新(Refresh)：?
??? 指當基表發生了DML操作后，物化視圖何時采用哪種方式和基表進行同步。刷新的模式有兩種：ON DEMAND和ON COMMIT。ON DEMAND指物化視圖在用戶需要的時候進行刷新，可以手工通過DBMS_MVIEW.REFRESH等方法來進行刷新，也可以通過JOB定時進行刷新。ON COMMIT指出物化視圖在對基表的DML操作提交的同時進行刷新。刷新的方法有四種：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后進行的修改。COMPLETE刷新對整個物化視圖進行完全的刷新。如果選擇FORCE方式，則Oracle在刷新時會去判斷是否可以進行快速刷新，如果可以則采用FAST方式，否則采用COMPLETE的方式。NEVER指物化視圖不進行任何刷新。默認值是FORCE ON DEMAND。
?
??? 4、快速刷新限制(Refresh FAST)：?
??? 如果需要進行快速刷新，則需要建立物化視圖日志。物化視圖日志根據不同物化視圖的快速刷新的需要，可以建立為ROWID或PRIMARY KEY類型的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
?
??? 5、基于物理表的物化視圖：?
??? 可以指明ON PREBUILD TABLE語句將物化視圖建立在一個已經存在的表上。這種情況下，物化視圖和表必須同名。當刪除物化視圖時，不會刪除同名的表。這種物化視圖的查詢重寫要求參數QUERY_REWRITE_INTEGRITY必須設置為trusted或者stale_tolerated。
?
??? 6、REFRESH 子句?
??? [refresh [fast|complete|force]
??? [on demand | commit]
??? [start with date] [next date]
??? [with {primary key|rowid}]
?
??? Refresh? FAST
?
??? 增量刷新用物化視圖日志(參照上面所述)來發送主表已經修改的數據行到物化視圖中.如果指定REFRESH FAST子句，那么應該對主表創建物化視圖日志
?
??? SQL> CREATE MATERIALIZED VIEW LOG ON emp;
??? Materialized view log created.
??? 對于增量刷新選項，如果在子查詢中存在分析函數，則物化視圖不起作用。
?
??? Refresh? COMPLETE
?
??? 完全刷新重新生成整個視圖，如果請求完全刷新，oracle會完成完全刷新即使增量刷新可用。
?
??? Refresh FORCE
?
??? 當指定FORCE子句，如果增量刷新可用Oracle將完成增量刷新，否則將完成完全刷新,如果不指定刷新方法(FAST、COMPLETE、FORCE)。Force選項是默認選項
?
??? primary key 和 rowid
?
??? WITH PRIMARY KEY選項生成主鍵物化視圖，也就是說物化視圖是基于主表的主鍵，而不是ROWID(對應于ROWID子句)。 PRIMARY KEY是默認選項，為了生成PRIMARY KEY子句，應該在主表上定義主鍵，否則應該用基于ROWID的物化視圖。
?
??? 主鍵物化視圖允許識別物化視圖主表而不影響物化視圖增量刷新的可用性。
Rowid物化視圖只有一個單一的主表，不能包括“Distinct、聚合函數、Group by、子查詢、連接、SET操作”
?
??? 7、刷新時間??????
??? START WITH子句通知數據庫完成從主表到本地表第一次復制的時間，應該及時估計下一次運行的時間點， NEXT 子句說明了刷新的間隔時間。
?
?
三、使用物化視圖的設置和查錯：
??? COMPATIBLE參數必須高于 8.1.0
??? QUERY_REWRITE_ENABLED = TRUE
??? QUERY_REWRITE_INTEGRETY =
? ??? ENFORCED - 查詢僅用Oracle強制與保證的約束、規則重寫；
? ??? TRUSTED – 查詢除用Oracle強制與保證的約束、規則，也可用用戶設定的數據間的任何關系來重寫；
? ??? STALE_TOLERATED – 即便Oracle知道物化視圖中數據過期（與事實表等不同步），也重寫查詢。
?
??? 當建立物化視圖失敗時，10g之前可查詢DBMS_MVIEW包中的EXPLAIN_MVIEW、EXPLAIN_REWRITE過程進行錯誤定位。
?
???
?
另附：10g中可使用新的 DBMS_ADVISOR 程序包中的一個名為 TUNE_MVIEW 的過程使得這項工作變得非常容易：
-------------------------------------------------------------------------
SQL> -- 首先定義一個變量來保存 OUT 參數
SQL> var adv_name varchar2(20)
SQL> begin
? 2? dbms_advisor.tune_mview
? 3 (
? 4 :adv_name,
? 5 'create materialized view mv_hotel_resv
??????? refresh fast enable query rewrite as
??????? select distinct city, resv_id, cust_name from hotels h,
reservations r where r.hotel_id = h.hotel_id');
? 6? end;
?
SQL> print adv_name
ADV_NAME
-----------------------
TASK_117
?
SQL> select script_type, statement
???? from dba_tune_mview
???? where task_name = 'TASK_117'
???? order by script_type,action_id;
?
SCRIPT_TYPE STATEMENT
-------------- -----------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
ROWID, SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY
UNDO DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
?
?
?