摘要：在數(shù)據(jù)倉(cāng)庫(kù)的開(kāi)發(fā)中，隨著業(yè)務(wù)數(shù)據(jù)量的劇增以及其數(shù)據(jù)量增加的不穩(wěn)定性，如何使用一種合適的方式來(lái)實(shí)現(xiàn)ETL(數(shù)據(jù)的抽取，轉(zhuǎn)換，裝載)成為在數(shù)據(jù)倉(cāng)庫(kù)開(kāi)發(fā)中最需要考慮的熱點(diǎn)。本文介紹一種可以實(shí)現(xiàn)數(shù)據(jù)倉(cāng)庫(kù)ETL的技術(shù)——Oracle物化試圖，并以作者在數(shù)據(jù)倉(cāng)庫(kù)開(kāi)發(fā)實(shí)踐作為例子，具體說(shuō)明Oracle物化試圖的實(shí)現(xiàn)方法。

關(guān)鍵詞：數(shù)據(jù)倉(cāng)庫(kù)；ETL；物化試圖；物化試圖日志；增量抽取

中圖分類號(hào)：TP311文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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 引言

隨著信息技術(shù)的不斷推廣和應(yīng)用，許多企業(yè)都已經(jīng)在使用管理信息系統(tǒng)處理管理事務(wù)和日常業(yè)務(wù)。這些管理信息系統(tǒng)為企業(yè)積累了大量的信息。企業(yè)管理者已經(jīng)不滿足于僅僅用計(jì)算機(jī)去處理每天所發(fā)生的事務(wù)數(shù)據(jù)，而開(kāi)始考慮如何利用這些信息海洋對(duì)企業(yè)的管理決策提供支持。因此，產(chǎn)生了與傳統(tǒng)數(shù)據(jù)庫(kù)有很大差異的數(shù)據(jù)環(huán)境要求和從這些海洋數(shù)據(jù)中獲取特殊知識(shí)的工具需要。這就需要一種能夠?qū)⑷粘I(yè)務(wù)處理中所收集到的各種數(shù)據(jù)轉(zhuǎn)變?yōu)榫哂猩虡I(yè)價(jià)值信息的技術(shù)——數(shù)據(jù)倉(cāng)庫(kù)。

數(shù)據(jù)倉(cāng)庫(kù)是指一個(gè)面向主題的、集成的、隨時(shí)間變化的、非易失性數(shù)據(jù)的集合，用于支持管理層的決策過(guò)程的系統(tǒng)。在數(shù)據(jù)倉(cāng)庫(kù)的開(kāi)發(fā)中，技術(shù)上主要體現(xiàn)在ETL以及前端展現(xiàn)上，下面即將介紹一種ETL的實(shí)現(xiàn)方式——Oracle物化試圖。

2 ETL的實(shí)現(xiàn)方式

ETL是指數(shù)據(jù)抽取，轉(zhuǎn)換，裝載。數(shù)據(jù)抽取是指從源系統(tǒng)中抽取數(shù)據(jù)倉(cāng)庫(kù)所需要的數(shù)據(jù);數(shù)據(jù)轉(zhuǎn)換是指在源系統(tǒng)中抽取的數(shù)據(jù)不能直接應(yīng)用在數(shù)據(jù)倉(cāng)庫(kù)中查詢，需要將這部分?jǐn)?shù)據(jù)轉(zhuǎn)換成滿足數(shù)據(jù)倉(cāng)庫(kù)條件的數(shù)據(jù);數(shù)據(jù)裝載是指將抽取出來(lái)以及轉(zhuǎn)換好的數(shù)據(jù)裝載到數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)庫(kù)中。

ETL實(shí)現(xiàn)方式主要有以下幾種(以下方式均以O(shè)LTP系統(tǒng)作為源系統(tǒng)，并進(jìn)行ETL數(shù)據(jù)加載到OLAP系統(tǒng))：

1)時(shí)戳方式：需要在OLTP系統(tǒng)中業(yè)務(wù)表中統(tǒng)一添加時(shí)間字段作為時(shí)戳（如表中已有相應(yīng)的時(shí)間字段，可以不必添加），每當(dāng)OLTP系統(tǒng)中更新修改業(yè)務(wù)數(shù)據(jù)時(shí)，同時(shí)修改時(shí)戳字段值。當(dāng)作ETL加載時(shí)，通過(guò)系統(tǒng)時(shí)間與時(shí)戳字段的比較來(lái)決定進(jìn)行何種數(shù)據(jù)抽取。

2)日志表方式：在OLTP系統(tǒng)中添加系統(tǒng)日志表，當(dāng)業(yè)務(wù)數(shù)據(jù)發(fā)生變化時(shí)，更新維護(hù)日志表內(nèi)容，當(dāng)作ETL加載時(shí)，通過(guò)讀日志表數(shù)據(jù)決定加載那些數(shù)據(jù)及如何加載。該方式即可用ORACLE物化視圖實(shí)現(xiàn)。

3)全表比對(duì)方式：在ETL過(guò)程中，抽取所有源數(shù)據(jù)，并進(jìn)行相應(yīng)規(guī)則轉(zhuǎn)換，完成后先不插入目標(biāo)，而對(duì)每條數(shù)據(jù)進(jìn)行目標(biāo)表比對(duì)。根據(jù)主鍵值進(jìn)行插入與更新的判定，目標(biāo)表已存在該主鍵值的，表示該記錄已有，并進(jìn)行其余字段比對(duì)，如有不同，進(jìn)行Update操作，如目標(biāo)表沒(méi)有存在該主鍵值，表示該記錄還沒(méi)有，即進(jìn)行Insert操作。

4)全表刪除插入方式：每次ETL操作均刪除目標(biāo)表數(shù)據(jù)，由ETL全新加載數(shù)據(jù)。

3 ORACLE物化視圖簡(jiǎn)介

物化視圖是包括一個(gè)查詢結(jié)果的數(shù)據(jù)庫(kù)對(duì)像，它是遠(yuǎn)程數(shù)據(jù)的的本地副本，或者用來(lái)生

成基于數(shù)據(jù)表求和的匯總表。物化視圖存儲(chǔ)基于遠(yuǎn)程表的數(shù)據(jù)，也可以稱為快照。通常情況下，物化視圖被稱為主表（在復(fù)制期間）或明細(xì)表（在數(shù)據(jù)倉(cāng)庫(kù)中）。對(duì)于復(fù)制，物化視圖允許你在本地維護(hù)遠(yuǎn)程數(shù)據(jù)的副本，這些副本是只讀的。如果你想修改本地副本，必須用高級(jí)復(fù)制的功能。物化視圖可以查詢表，視圖和其它的物化視圖。 當(dāng)你想從一個(gè)表或視圖中抽取數(shù)據(jù)時(shí)，你可以用從物化視圖中抽取。

創(chuàng)建物化視圖的時(shí)候可以指定多個(gè)選項(xiàng)，下面主要對(duì)創(chuàng)建方式（Build Methods）， 查詢重寫(xiě)（Query Rewrite），刷新（Refresh）三個(gè)選項(xiàng)進(jìn)行說(shuō)明：

創(chuàng)建方式：包括Build Immediate和Build Deferred兩種。Build Immediate是在創(chuàng)建物化視圖的時(shí)候就生成數(shù)據(jù)，而B(niǎo)uild Deferred則在創(chuàng)建時(shí)不生成數(shù)據(jù)，以后根據(jù)需要在生成數(shù)據(jù)。默認(rèn)為Build Immediate。

查詢重寫(xiě)（Query Rewrite）：包括Enable Query Rewrite和Disable Query Rewrite兩種。分別指出創(chuàng)建的物化視圖是否支持查詢重寫(xiě)。查詢重寫(xiě)是指當(dāng)對(duì)物化視圖的基表進(jìn)行查詢時(shí)，Oracle會(huì)自動(dòng)判斷能否通過(guò)查詢物化視圖來(lái)得到結(jié)果，如果可以，則避免了聚集或連接操作，而直接從已經(jīng)計(jì)算好的物化視圖中讀取數(shù)據(jù)。默認(rèn)為Disable Query Rewrite。

刷新（Refresh）：指當(dāng)基表發(fā)生了DML操作后，物化視圖何時(shí)采用哪種方式和基表進(jìn)行同步。刷新的模式有兩種：On Demand和On Commit。On Demand指物化視圖在用戶需要的時(shí)候進(jìn)行刷新，可以手工通過(guò)DBMS_MVIEW。REFRESH等方法來(lái)進(jìn)行刷新，也可以通過(guò)JOB定時(shí)進(jìn)行刷新。On Commit指出物化視圖在對(duì)基表的DML操作提交的同時(shí)進(jìn)行刷新。刷新的方法有四種：Fast、Complete、Force和Never。FAST刷新采用增量刷新，只刷新自上次刷新以后進(jìn)行的修改。Complete刷新對(duì)整個(gè)物化視圖進(jìn)行完全的刷新。如果選擇Force方式，則Oracle在刷新時(shí)會(huì)去判斷是否可以進(jìn)行快速刷新，如果可以則采用Fast方式，否則采用Complete的方式。Never指物化視圖不進(jìn)行任何刷新。默認(rèn)值是Force On Demand。

如果需要進(jìn)行快速刷新，需要建立物化視圖日志。 物化視圖日志根據(jù)不同物化視圖的快速刷新的需要，可以建立為ROWID或PRIMARY KEY類型的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。上面這些情況產(chǎn)生的物化視圖日志的結(jié)構(gòu)都不相同。物化視圖日志的名稱為MLOG$_后面跟基表的名稱，如果表名的長(zhǎng)度超過(guò)20位，則只取前20位，當(dāng)截短后出現(xiàn)名稱重復(fù)時(shí)，Oracle會(huì)自動(dòng)在物化視圖日志名稱后面加上數(shù)字作為序號(hào)。

任何物化視圖都會(huì)包括的列：

SNAPTIME$$：用于表示刷新時(shí)間

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

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

CHANGE_VECTOR$$表示修改矢量，用來(lái)表示被修改的是哪個(gè)或哪幾個(gè)字段。

4 ORACLE物化視圖在數(shù)據(jù)倉(cāng)庫(kù)中的應(yīng)用

在數(shù)據(jù)倉(cāng)庫(kù)的實(shí)現(xiàn)過(guò)程中，我們?cè)谶M(jìn)行ETL開(kāi)發(fā)的時(shí)候，一般都是采用時(shí)間戳的方式來(lái)實(shí)現(xiàn)增量抽取。但是對(duì)于在業(yè)務(wù)系統(tǒng)中沒(méi)有時(shí)間戳的表，我們必須要修改業(yè)務(wù)系統(tǒng)，對(duì)沒(méi)有時(shí)間戳的表加上時(shí)間字段，以記錄業(yè)務(wù)發(fā)生的時(shí)間。這樣就增加了修改業(yè)務(wù)系統(tǒng)的工作，改動(dòng)面也很大，也違背了在開(kāi)發(fā)數(shù)據(jù)倉(cāng)庫(kù)時(shí)候不應(yīng)該修改業(yè)務(wù)系統(tǒng)的原則。所以對(duì)于在業(yè)務(wù)系統(tǒng)中，存在沒(méi)有保存時(shí)間字段的表，采用時(shí)間戳的方式實(shí)現(xiàn)是不合適的。還可以采用全表比對(duì)方式或全表刪除插入方式。對(duì)于采用全表比對(duì)方式，ETL設(shè)計(jì)非常復(fù)雜，而且如果數(shù)據(jù)量很大的話，實(shí)現(xiàn)增量抽取的時(shí)候需要很長(zhǎng)的時(shí)間，速度很慢;對(duì)于全表刪除插入方式，如果OLTP系統(tǒng)中有刪除記錄的操作，那么在OLAP系統(tǒng)中將不會(huì)記錄到刪除的歷史數(shù)據(jù)，這樣就沒(méi)有實(shí)現(xiàn)保存歷史數(shù)據(jù)的功能，不能實(shí)現(xiàn)數(shù)據(jù)的遞增加載。

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

以下是具體的實(shí)現(xiàn)步驟：

1)在中轉(zhuǎn)區(qū)創(chuàng)建表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)在中轉(zhuǎn)區(qū)物化試圖Lvy_levydata上創(chuàng)建物化試圖日志，該日志保存增量的數(shù)據(jù)：

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

3)在中轉(zhuǎn)區(qū)創(chuàng)建臨時(shí)表，該臨時(shí)表用于保存捕獲的變化數(shù)據(jù)信息：

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

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

insert into temp_Lvy_levydata

select b.*

from staging.mlog$_Lvy_levydata a，Lvy_levydata b

where a.Levydataid=b.Levydataid;

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

刪除：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)到這里基本上已經(jīng)完成了ETL工作，剩下的工作就是要清空臨時(shí)表和物化試圖日志，以便下次抽取的時(shí)候不會(huì)出現(xiàn)重復(fù)的記錄：

EXECUTE IMMEDIATE truncate tabletemp_Lvy_levydata

EXECUTE IMMEDIATE truncate table staging.mlog$_Lvy_levydata;

5 結(jié)束語(yǔ)

利用ORACLE物化試圖，開(kāi)發(fā)人員很容易就可以實(shí)現(xiàn)ETL，不用考慮源系統(tǒng)中的表是否具有時(shí)間戳字段，也不需要考慮數(shù)據(jù)量問(wèn)題，因?yàn)槲锘噲D日志只裝載變化的那部分?jǐn)?shù)據(jù)。 不需要修改OLTP表結(jié)構(gòu)，源數(shù)據(jù)抽取清楚，速度較快?？梢詫?shí)現(xiàn)數(shù)據(jù)的遞增加載。而且日志表的維護(hù)是在中轉(zhuǎn)區(qū)進(jìn)行，這樣不會(huì)影響OLTP系統(tǒng)。當(dāng)然數(shù)據(jù)倉(cāng)庫(kù)的實(shí)現(xiàn)最大的挑戰(zhàn)不是技術(shù)，而是開(kāi)發(fā)人員對(duì)所需實(shí)現(xiàn)系統(tǒng)的業(yè)務(wù)理解。

參考文獻(xiàn)：

[1] 騰永昌.Oracle9i數(shù)據(jù)庫(kù)管理員使用大全 .北京:清華大學(xué)出版社，2004.

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

[3] [美]Paulraj Ponniah著，段云峰，李劍威，韓潔，宋美娜，譯.數(shù)據(jù)倉(cāng)庫(kù)基礎(chǔ).北京:電子工業(yè)出版社，2004.

收稿日期：2008-02-27

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