oracle物化視圖的兩個典型應用情境

標籤：oracle 物化視圖

物化視圖是oracle一個比較有特色的東西，自oracle9i起，應用非常廣泛，不像mysql，不支援原生物化視圖，要藉助flexviews去實現。物化視圖到底有什麼用呢？要回答這個問題，必須先搞清楚物化視圖與普通視圖的區別：

物化視圖是有一個與之對應的容器表的。容器表是一個跟物化視圖同名的“規則”的表，用於儲存查詢返回的結果集。這是物化視圖與普通視圖的根本區別，它是有儲存結果集的“物理存在”的，而普通視圖則沒有這個物理存在，只是一個虛表，每訪問一次，查詢就要執行一次基表訪問（不考慮cache）。

物化視圖的應用情境有兩種：1、用於查詢最佳化 2、用於進階複製，下面分別舉一些實際工作中的case來進行說明。

1、查詢最佳化。

某電信增值業務，在進程啟動的時候，需要載入一些重要的業務初始化資料（比如全球電訊廠商的networkid、cc、ndc等基礎資料），這些業務初始化資料要從四個表中查詢擷取。為了提高這部分資料的access效能，加快進程啟動速度，可以將這四個表的資料群組合為一個物化視圖，定義如下：

CREATE MATERIALIZED VIEW IRDB_NETWORKLIST
REFRESH FORCE ON COMMIT
AS
SELECT
   A.NETWORKID AS NETWORKID,
   A.NETWORKNAME AS NAME,
   C.CC AS CC,
   C.NDC AS NDC,
   B.MCC AS MCC,
   B.MNC AS MNC,
   A.NEWVISITINTERVAL AS NEWVISITINTERVAL,
   A.OUTNEWVISITINTERVAL AS OUTNEWVISITINTERVAL,
   D.MNP_ENABLED AS HASMNP,
   A.BRANDNAME AS BRANDNAME,
   A.LANGUAGECODE AS DEFAULTLANGUAGECODE,
   C.TIMEZONE AS TIMEZONE,
   A.NDD AS NDD,
   A.ZONEID AS ZONEID
FROM
IRDB_NETWORK_MASTER A, IRDB_NETWORK_GSM_DETAIL B, IRDB_NETWORK_CODES C, IRDB_COUNTRY_MASTER D
WHERE
A.NETWORKID = B.NETWORKID AND A.NETWORKID = C.NETWORKID AND A.COUNTRYID = D.COUNTRYID AND A.STATUS = ‘1‘ AND C.STATUS = ‘1‘

後續當我們對IRDB_NETWORK_MASTER、IRDB_NETWORK_GSM_DETAIL、IRDB_NETWORK_CODES和IRDB_COUNTRY_MASTER這四個業務基表中的任何一個或者多個進行DML commit的時候，物化視圖IRDB_NETWORKLIST就能自動更新了。當然了，基表與物化視圖的同步也是有一定代價的，但如果不建立物化視圖，那麼每次外部調用都會去查詢基表，而物化視圖會將這種壓力分散，將基表查詢、基表串連與外部業務介面訪問錯開，有利於降低資料庫負載的peak值，這也是資料庫效能最佳化的核心思想之一。

2、進階複製

很多業務情境，我們不需要對整個資料庫進行同步，只需要對部分表的部分欄位進行同步，這個時候，物化視圖可以派上用場。下面是某直轄市移動彩鈴業務資料庫結構圖：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6E/B0/wKioL1WC9wWi9kjfAAF0aiD-Lls713.jpg" title="P650.jpg" alt="wKioL1WC9wWi9kjfAAF0aiD-Lls713.jpg" />

這裡，一台P650小機作為管理節點，負責業務開銷戶、業務話單等資料處理，其餘5台P650小機作為呼叫節點使用，只提供使用者資料的查詢功能。這裡不需要用DG等全庫同步的技術，因為呼叫節點只需要同步部分與呼叫業務相關的資料。管理節點建立物化視圖，這裡以t_userinfo使用者表為例：

CREATE MATERIALIZED VIEW                     

usdptemp.T_USERINFO_MV

REFRESH  FAST                                

AS                                           

selectPHONENUMBER,LOCALID,BRANDID,PAYKIND from usdp604.T_USERINFO

在呼叫節點建立一個同義字：

create or replace synonym T_USERINFO
 for T_USERINFO_mv;

這樣子可以保持和管理節點完全一樣的對象名稱。

然後建立一個重新整理組，每10秒重新整理一次物化視圖：

--create a refresh group

BEGIN   

   DBMS_REFRESH.MAKE(   

   name => ‘usdpsync‘,   

    list => ‘T_USERINFO_mv‘,   

    next_date=> sysdate,   

    interval => ‘sysdate + 10/86400‘   

);   

END;   

/

呼叫節點上的T_USERINFO_mv是從管理節點上的臨時使用者impdp過來的，用的dblink，所以，呼叫節點上的T_USERINFO_mv和管理節點上的物化視圖日誌是綁在一起的。

物化視圖在使用過程中，還有幾點需要留意，在此羅列一下：

1、物化視圖有兩種重新整理模式ON DEMAND和ON COMMIT，前者需要調用dbms_mview手工重新整理，後者在基表提交的時候會自動重新整理。如果建立物化視圖的時候不指定重新整理方式，預設是ON DEMAND，此時一般需要建立一個job，在job裡按照某個固定周期調用dbms_mview進
行重新整理。

2、物化視圖日誌在建立時有多種選項：可以指定為ROWID、PRIMARY KEY和OBJECT ID幾種類型，同時還可以指定SEQUENCE或明確指定列名。上面這些情況產生的物化視圖日誌的結構都不相同。

3、重新整理方法有四種：fast、complete、force和never。
fast是增量重新整理，只重新整理上次重新整理以後進行的修改。
complete是對整個物化視圖進行完全的重新整理。
force，oracle在重新整理時會判斷是否可以進行快速重新整理，如果可以進行fast，否則進行complete
never，物化視圖不進行任何重新整理。

如果要進行快速重新整理，必須建立物化視圖日誌。物化視圖日誌命名規則為mlog$_+基表名稱。
查看當前系統的物化視圖日誌表：
select * from dba_mview_logs;

force方式重新整理則不需要建立物化視圖日誌。

4、如果物化視圖遇到同步問題，緊急情況下，可以執行手動重新整理：

SQL> exec dbms_mview.refresh(‘IRDB_NETWORKLIST‘);
PL/SQL procedure successfully completed

此時會根據基表對IRDB_NETWORKLIST物化視圖進行重新整理。

5、物化視圖日誌最佳化：

對於物化視圖日誌，我們可以建立索引提高效能。同時在排查同步慢等效能問題的時候也需要檢查看是否出現高水位，物化視圖日誌表的高水位會影響重新整理效能，處理高水位的方法，在此省略。

本文出自 “記憶片段” 部落格，謝絕轉載！

oracle物化視圖的兩個典型應用情境