Ｏｒａｃｌｅ物化視圖在數據倉庫中的應用

摘要：在數據倉庫的開發中，隨著業務數據量的劇增以及其數據量增加的不穩定性，如何使用一種合適的方式來實現ETL(數據的抽取，轉換，裝載)成為在數據倉庫開發中最需要考慮的熱點。本文介紹一種可以實現數據倉庫ETL的技術——Oracle物化試圖，并以作者在數據倉庫開發實踐作為例子，具體說明Oracle物化試圖的實現方法。

關鍵詞：數據倉庫；ETL；物化試圖；物化試圖日志；增量抽取

中圖分類號：TP311文獻標識碼：A 文章編號：1009-3044(2008)12-20000-00

Application of Oracle Materialized View in Data Warehouse

XIE Ren-dong1， YANG Jun2

(1.Beijing Global InfoTech Group，Beijing 100013，China;2.Department of Information Management Engineering ，Jiangxi Toursm Commerce College，Nanchang 330039，China)

Abstract: In the data warehouse development， along with the service data quantity sharp increase the instabilitywhich increases by and its the data quantity， how uses one appropriate way to realize ETL (data extracting， transformation， loading) becomes the hot spot which most needs to consider in the data warehouse development. This article introduced one kind may realize the data warehouseETL technical - Oracle Materialized View， and takes the example by the author in the data warehouse development practice， specifically explained the Oracle Materialized View realization method.

Key words: data warehouse; Materialized View; data extracting; transformation; loading

1 引言

隨著信息技術的不斷推廣和應用，許多企業都已經在使用管理信息系統處理管理事務和日常業務。這些管理信息系統為企業積累了大量的信息。企業管理者已經不滿足于僅僅用計算機去處理每天所發生的事務數據，而開始考慮如何利用這些信息海洋對企業的管理決策提供支持。因此，產生了與傳統數據庫有很大差異的數據環境要求和從這些海洋數據中獲取特殊知識的工具需要。這就需要一種能夠將日常業務處理中所收集到的各種數據轉變為具有商業價值信息的技術——數據倉庫。

數據倉庫是指一個面向主題的、集成的、隨時間變化的、非易失性數據的集合，用于支持管理層的決策過程的系統。在數據倉庫的開發中，技術上主要體現在ETL以及前端展現上，下面即將介紹一種ETL的實現方式——Oracle物化試圖。

2 ETL的實現方式

ETL是指數據抽取，轉換，裝載。數據抽取是指從源系統中抽取數據倉庫所需要的數據;數據轉換是指在源系統中抽取的數據不能直接應用在數據倉庫中查詢，需要將這部分數據轉換成滿足數據倉庫條件的數據;數據裝載是指將抽取出來以及轉換好的數據裝載到數據倉庫的數據庫中。

ETL實現方式主要有以下幾種(以下方式均以OLTP系統作為源系統，并進行ETL數據加載到OLAP系統)：

1)時戳方式：需要在OLTP系統中業務表中統一添加時間字段作為時戳（如表中已有相應的時間字段，可以不必添加），每當OLTP系統中更新修改業務數據時，同時修改時戳字段值。當作ETL加載時，通過系統時間與時戳字段的比較來決定進行何種數據抽取。

2)日志表方式：在OLTP系統中添加系統日志表，當業務數據發生變化時，更新維護日志表內容，當作ETL加載時，通過讀日志表數據決定加載那些數據及如何加載。該方式即可用ORACLE物化視圖實現。

3)全表比對方式：在ETL過程中，抽取所有源數據，并進行相應規則轉換，完成后先不插入目標，而對每條數據進行目標表比對。根據主鍵值進行插入與更新的判定，目標表已存在該主鍵值的，表示該記錄已有，并進行其余字段比對，如有不同，進行Update操作，如目標表沒有存在該主鍵值，表示該記錄還沒有，即進行Insert操作。

4)全表刪除插入方式：每次ETL操作均刪除目標表數據，由ETL全新加載數據。

3 ORACLE物化視圖簡介

物化視圖是包括一個查詢結果的數據庫對像，它是遠程數據的的本地副本，或者用來生

成基于數據表求和的匯總表。物化視圖存儲基于遠程表的數據，也可以稱為快照。通常情況下，物化視圖被稱為主表（在復制期間）或明細表（在數據倉庫中）。對于復制，物化視圖允許你在本地維護遠程數據的副本，這些副本是只讀的。如果你想修改本地副本，必須用高級復制的功能。物化視圖可以查詢表，視圖和其它的物化視圖。 當你想從一個表或視圖中抽取數據時，你可以用從物化視圖中抽取。

創建物化視圖的時候可以指定多個選項，下面主要對創建方式（Build Methods）， 查詢重寫（Query Rewrite），刷新（Refresh）三個選項進行說明：

創建方式：包括Build Immediate和Build Deferred兩種。Build Immediate是在創建物化視圖的時候就生成數據，而Build Deferred則在創建時不生成數據，以后根據需要在生成數據。默認為Build Immediate。

查詢重寫（Query Rewrite）：包括Enable Query Rewrite和Disable Query Rewrite兩種。分別指出創建的物化視圖是否支持查詢重寫。查詢重寫是指當對物化視圖的基表進行查詢時，Oracle會自動判斷能否通過查詢物化視圖來得到結果，如果可以，則避免了聚集或連接操作，而直接從已經計算好的物化視圖中讀取數據。默認為Disable Query Rewrite。

刷新（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。

如果需要進行快速刷新，需要建立物化視圖日志。 物化視圖日志根據不同物化視圖的快速刷新的需要，可以建立為ROWID或PRIMARY KEY類型的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。上面這些情況產生的物化視圖日志的結構都不相同。物化視圖日志的名稱為MLOG$_后面跟基表的名稱，如果表名的長度超過20位，則只取前20位，當截短后出現名稱重復時，Oracle會自動在物化視圖日志名稱后面加上數字作為序號。

任何物化視圖都會包括的列：

SNAPTIME$$：用于表示刷新時間

DMLTYPE$$：用于表示DML操作類型，I表示INSERT，D表示DELETE，U表示UPDATE。

OLD_NEW$$：用于表示這個值是新值還是舊值。N（EW）表示新值，O（LD）表示舊值，U表示UPDATE操作。

CHANGE_VECTOR$$表示修改矢量，用來表示被修改的是哪個或哪幾個字段。

4 ORACLE物化視圖在數據倉庫中的應用

在數據倉庫的實現過程中，我們在進行ETL開發的時候，一般都是采用時間戳的方式來實現增量抽取。但是對于在業務系統中沒有時間戳的表，我們必須要修改業務系統，對沒有時間戳的表加上時間字段，以記錄業務發生的時間。這樣就增加了修改業務系統的工作，改動面也很大，也違背了在開發數據倉庫時候不應該修改業務系統的原則。所以對于在業務系統中，存在沒有保存時間字段的表，采用時間戳的方式實現是不合適的。還可以采用全表比對方式或全表刪除插入方式。對于采用全表比對方式，ETL設計非常復雜，而且如果數據量很大的話，實現增量抽取的時候需要很長的時間，速度很慢;對于全表刪除插入方式，如果OLTP系統中有刪除記錄的操作，那么在OLAP系統中將不會記錄到刪除的歷史數據，這樣就沒有實現保存歷史數據的功能，不能實現數據的遞增加載。

根據以上幾種實現方式的討論得出的結果，我們可以考慮采取Oracle物化視圖方式來實現ETL。 數據倉庫中的物化視圖主要用于預先計算并保存表連接或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。在數據倉庫中，還經常使用查詢重寫（Query Rewrite）機制，這樣不需要修改原有的查詢語句，Oracle會自動選擇合適的物化視圖進行查詢，完全對應用透明。物化視圖在一個段中存儲查詢結果，并且能夠在提交查詢時將結果返回給用戶，從而不再需要重新執行查詢 — 在查詢要執行幾次時（這在數據倉庫環境中非常常見），這是一個很大的好處。物化視圖可以利用一個快速刷新機制從基礎表中全部或增量刷新。下面用一個有時間字段但是用時間戳實現不了的例子來介紹Oracle物化視圖方式來實現ETL：在納稅管理系統中，納稅人來申報征收崗申報稅款開票，系統將在數據庫中保存了開票信息記錄在Lvy_征收數據(Lvy_levydata，圖1)表中，此時系統記錄當時的開票時間;但是當會統崗需要把稅款入庫的時候，需要把入庫信息即入庫時間更新到表Lvy_征收數據中對應的記錄上，但此時，系統并不會記錄更新的系統時間。因此，使用時間戳方式是無法實現的，我使用物化視圖日志來保存此時更新的信息，然后通過讀取日志表里的信息來跟數據倉庫中事實表(F_LevyInputData，圖1)的記錄比對實現增量抽取。整個ETL的架構圖如圖2。

以下是具體的實現步驟：

1)在中轉區創建表Lvy_levydata的物化試圖，使用快速刷新方式：

create Materialized view Lvy_levydata tablespace staging refresh fast start with sysdate next trunc(sysdate)+1+3/24 as select * from Lvy_levydata;

2)在中轉區物化試圖Lvy_levydata上創建物化試圖日志，該日志保存增量的數據：

create Materialized view log on Lvy_levydata with primary key (LevydataId)including new values;

3)在中轉區創建臨時表，該臨時表用于保存捕獲的變化數據信息：

create table temp_Lvy_levydata as select * from Lvy_levydata where 1=2;

4)從物化試圖日志里面讀取數據，并且于物化試圖關聯，這樣就得到了增量部分的數據，然后保存到臨時表中：

insert into temp_Lvy_levydata

select b.*

from staging.mlog$_Lvy_levydata a，Lvy_levydata b

where a.Levydataid=b.Levydataid;

5)現在已經得到了所有的變化數據，需要把這些數據插入到事實表F_LevyInputData中，可以采用先刪除再插入的方式，即從事實表中刪除在臨時表已經捕獲的變化數據，然后插入臨時表中的全部數據到事實表中的方式來實現;或者直接采用更新的方式，即比對事實表與臨時表，如果兩個表存在相同的主鍵，那么更新事實表的數據，如果在事實表中不存在，那么插入那條記錄。這里采用先刪除再插入方式：

刪除：delete from F_LevyInputData d

where exists (select 1 from temp_Lvy_levydata c

where d.LevyDetailDataId=c。Levydataid);

插入： insert into F_LevyInputData select * from temp_Lvy_levydata;

6)到這里基本上已經完成了ETL工作，剩下的工作就是要清空臨時表和物化試圖日志，以便下次抽取的時候不會出現重復的記錄：

EXECUTE IMMEDIATE truncate tabletemp_Lvy_levydata

EXECUTE IMMEDIATE truncate table staging.mlog$_Lvy_levydata;

5 結束語

利用ORACLE物化試圖，開發人員很容易就可以實現ETL，不用考慮源系統中的表是否具有時間戳字段，也不需要考慮數據量問題，因為物化試圖日志只裝載變化的那部分數據。 不需要修改OLTP表結構，源數據抽取清楚，速度較快。可以實現數據的遞增加載。而且日志表的維護是在中轉區進行，這樣不會影響OLTP系統。當然數據倉庫的實現最大的挑戰不是技術，而是開發人員對所需實現系統的業務理解。

參考文獻：

[1] 騰永昌.Oracle9i數據庫管理員使用大全 .北京:清華大學出版社，2004.

[2] [美]Kevin Loney，George Koch 著，鐘鳴，石永平，郝玉潔，譯.Oracle 9i參考手冊 .北京: 機械工業出版社，2003.

[3] [美]Paulraj Ponniah著，段云峰，李劍威，韓潔，宋美娜，譯.數據倉庫基礎.北京:電子工業出版社，2004.

收稿日期：2008-02-27

作者簡介：謝任東（1983-），男，江西寧都人。