淺析利用Oracle物化視圖對(duì)查詢優(yōu)化

陶孝海

摘 要：隨著大數(shù)據(jù)、物聯(lián)網(wǎng)、云計(jì)算等概念的興起， 關(guān)于數(shù)據(jù)的分析應(yīng)用逐步從后臺(tái)走向了前臺(tái)， 生活中各類的軟件開發(fā)、終端應(yīng)用都離不開背后支撐整個(gè)環(huán)境的數(shù)據(jù)本身， 動(dòng)輒上億、甚至百千億級(jí)別的數(shù)據(jù)亦不罕見， 面對(duì)如此龐大的數(shù)據(jù)量， 效率“二字”也被提升到了一個(gè)空前的高度上。Oracle作為目前使用最為廣泛應(yīng)用的關(guān)系數(shù)據(jù)庫， 如何對(duì)其做性能優(yōu)化， 提升工作效率， 去促成更強(qiáng)的決策能力、洞察力與最優(yōu)化處理， 成為信息產(chǎn)業(yè)所有人聚焦的核心。本文從Oracle物化視圖入手， 結(jié)合日常工作， 指出了基于Oracle物化視圖性能優(yōu)化方式。

關(guān)鍵詞： Oracle; 物化視圖; 優(yōu)化

文章編號(hào)： 2095-2163（2019）03-0309-03 中圖分類號(hào)： TP309.3 文獻(xiàn)標(biāo)志碼： A

0 引 言

荊州市于2013年實(shí)現(xiàn)8個(gè)縣市區(qū)（含市本級(jí)）社會(huì)保險(xiǎn)數(shù)據(jù)庫市級(jí)大集中，日常工作中涉及大量的數(shù)據(jù)查詢及統(tǒng)計(jì)分析工作。在查詢操作非常頻繁的情況下， 數(shù)據(jù)庫整體運(yùn)行性能會(huì)受到嚴(yán)重影響。通過在本地創(chuàng)建物化視圖，在實(shí)際工作中可以大大提高數(shù)據(jù)庫查詢效率。

物化視圖（Materialized View）在9i以前的版本叫做快照（SNAPSHOT），從9i開始改名叫做物化視圖。在設(shè)計(jì)上，是用于預(yù)先計(jì)算并保存表連接或聚集等耗時(shí)較多的操作的結(jié)果，如此一來，在執(zhí)行查詢時(shí)，就可以避免頻繁調(diào)取這些耗時(shí)的操作，從而快速得到查詢結(jié)果。

1 物化視圖的特點(diǎn)與優(yōu)勢(shì)

和表一樣，物化視圖是數(shù)據(jù)庫中的一種存儲(chǔ)數(shù)據(jù)的對(duì)象。用于預(yù)先計(jì)算并保存表連接或者聚集等耗時(shí)較多的操作。這樣，在執(zhí)行查詢的時(shí)候，就可以避開連接、聚集等耗時(shí)的操作，從而快速地得到結(jié)果。在使用時(shí)，可以查詢表、視圖、甚至是其他物化視圖中的數(shù)據(jù)。物化視圖有很多方面和索引很相似，諸如：使用物化視圖的目的是為了提高查詢性能;物化視圖對(duì)應(yīng)用透明，增加和刪除物化視圖不會(huì)影響應(yīng)用程序中SQL 語句的正確性和有效性;物化視圖需要占用存儲(chǔ)空間;當(dāng)基表發(fā)生變化時(shí)，物化視圖也應(yīng)當(dāng)刷新。

簡單說，物化視圖不僅存儲(chǔ)了SQL的定義，還存儲(chǔ)了數(shù)據(jù);此外，也是遠(yuǎn)程數(shù)據(jù)的本地副本，或者用來生成基于數(shù)據(jù)表求和的匯總表。

1.1 物化視圖與普通視圖的區(qū)別

Oracle物化視圖與普通視圖概念相似， 但是具有本質(zhì)上的區(qū)別?，F(xiàn)對(duì)其探討表述如下。

（1）普通視圖 （View） ， 也稱為虛表，不存儲(chǔ)任何數(shù)據(jù)，也不占用任何物理存儲(chǔ)空間，且只有定義，在查詢中是轉(zhuǎn)換為對(duì)應(yīng)的定義SQL去查詢，視圖本身的定義語句存儲(chǔ)在數(shù)據(jù)字典里。而物化視圖是將數(shù)據(jù)轉(zhuǎn)換為一個(gè)表，實(shí)際存儲(chǔ)著數(shù)據(jù)，也會(huì)占用數(shù)據(jù)庫磁盤空間。故而在查詢數(shù)據(jù)時(shí)，就無需用到大量表格，而且如果表很大的話，還會(huì)在臨時(shí)表空間內(nèi)展開大量的操作。

（2）普通視圖可以簡化設(shè)計(jì)，清晰編碼，但其存在卻只會(huì)降低性能。在每次使用時(shí)，視圖都需要重新執(zhí)行SQL， 這種操作的優(yōu)點(diǎn)是總能獲得最新的數(shù)據(jù)， 缺點(diǎn)是其運(yùn)行性能依賴于視圖所使用的查詢語句優(yōu)劣，此時(shí)如果視圖所使用的SELECT語句連接了許多張表， 或者使用了基于非索引的連接， 則視圖執(zhí)行的性能就會(huì)比較差。物化視圖則根據(jù)查詢的需要周期性地更新數(shù)據(jù)。物化視圖的優(yōu)勢(shì)是， 查詢物化視圖本質(zhì)上是查詢一張表， 而且這張表可以被索引。物化視圖更新時(shí)，所有的連接都被完成， 這就省去了在每次使用SELECT語句時(shí)均需進(jìn)行連接的較為耗時(shí)操作，從而可快速地得到結(jié)果。

1.2 物化視圖的特點(diǎn)

研究可知，物化視圖有3個(gè)特點(diǎn)，這里可做闡釋分述如下。

（1） 在某種意義上說，物化視圖就是一個(gè)物理表，而且不僅僅是一個(gè)物理表，這可通過其能被user_tables查詢出來而得到確認(rèn)。

（2） 物化視圖也是一種段（segment），因此具有自己的物理存儲(chǔ)屬性。

（3） 物化視圖會(huì)占用數(shù)據(jù)庫磁盤空間。這點(diǎn)從user_segment的查詢結(jié)果中，可以得到佐證;物化視圖由于是物理真實(shí)存在的，故可以創(chuàng)建索引。創(chuàng)建語句為：

create materialized view mv_name as? select * from table_name

1.3 物化視圖的優(yōu)勢(shì)

物化視圖最大的優(yōu)勢(shì)是可以提高性能，通過預(yù)先計(jì)算好答案存儲(chǔ)起來，從而大大地降低機(jī)器的負(fù)載，研究將分為4個(gè)方面對(duì)其加以剖析，詳見如下。

（1）更少的物理讀，掃描更少的數(shù)據(jù)。物化視圖可以基于列級(jí)和行級(jí)子集復(fù)制數(shù)據(jù)， 從而復(fù)本僅是與特定需求有關(guān)的信息。

（2）減輕網(wǎng)絡(luò)負(fù)載。通過使用多層架構(gòu)的物化視圖， 可以創(chuàng)建基于其他物化視圖的物化視圖， 進(jìn)一步分散用戶負(fù)載，將用戶負(fù)載分散于多個(gè)數(shù)據(jù)庫服務(wù)器，用戶可以訪問物化視圖站點(diǎn)、而非主站點(diǎn)。

（3）更少的寫，減少CPU的消耗。使用物化視圖不用經(jīng)常排序和聚集，不用對(duì)數(shù)據(jù)進(jìn)行聚集計(jì)算和函數(shù)調(diào)用，當(dāng)物化視圖創(chuàng)建為主表或主物化視圖的子集時(shí)， 還可以減少復(fù)制的數(shù)據(jù)量。

（4）顯著地加快響應(yīng)時(shí)間。在使用物化視圖查詢數(shù)據(jù)時(shí)（與主表相比），將會(huì)很快地返回查詢結(jié)果。

2 物化視圖的創(chuàng)建和使用

作為遠(yuǎn)程數(shù)據(jù)的的本地副本，或者用來生成基于數(shù)據(jù)表求和的匯總表，物化視圖往往需要跨庫訪問數(shù)據(jù)庫，遠(yuǎn)程的數(shù)據(jù)庫可以通過同步更新本地的物化視圖， 保證數(shù)據(jù)的一致性，如圖1所示。

2.1 物化視圖的創(chuàng)建

研究中，以創(chuàng)建個(gè)人賬戶刷卡消費(fèi)金額統(tǒng)計(jì)為例，創(chuàng)建設(shè)計(jì)實(shí)例如圖2所示。

物化視圖有2種刷新模式，分別是：on demand 和 on commit。其中，on demand 顧名思義，僅在該物化視圖需要被刷新時(shí)，才進(jìn)行刷新（REFRESH），即更新物化視圖，以保證和基表數(shù)據(jù)的一致性;on commit就是提交觸發(fā)，一旦基表有了commit，即事務(wù)提交，則即時(shí)刷新，即時(shí)更新物化視圖，使得數(shù)據(jù)和基表始終一致。一般情況下，使用這種方法在操作基表時(shí)，速度會(huì)比較慢。創(chuàng)建物化視圖時(shí)未作指定，Oracle的默認(rèn)定義即為 on demand 模式。

在此基礎(chǔ)上，關(guān)于如何刷新，則有3種刷新方法。對(duì)各種方法可給出研究論述如下。

（1）完全刷新（COMPLETE）： 會(huì)刪除表中所有的記錄（如果是單表刷新，可能會(huì)采用TRUNCATE的方式），此后將根據(jù)物化視圖中查詢語句的定義重新生成物化視圖。

（2）快速刷新（FAST）： 采用增量刷新的機(jī)制，只將自上次刷新后對(duì)基表進(jìn)行的所有操作刷新到物化視圖中去。FAST必須創(chuàng)建基于主表的視圖日志。對(duì)于增量刷新選項(xiàng)，如果在子查詢中存在分析函數(shù)，則物化視圖不起作用。

（3）FORCE方式：這是默認(rèn)的數(shù)據(jù)刷新方式。Oracle會(huì)自動(dòng)判斷是否滿足快速刷新的條件。如果滿足，將進(jìn)行快速刷新，否則進(jìn)行完全刷新。

特別地，Oracle物化視圖的快速刷新機(jī)制是通過物化視圖日志實(shí)現(xiàn)的。通過一個(gè)物化視圖日志，Oracle還可以支持多個(gè)物化視圖的快速刷新。根據(jù)不同物化視圖的快速刷新的需要，物化視圖日志可以建立為ROWID或PRIMARY KEY類型。此外，還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

2.2 物化視圖的應(yīng)用

在建立的物化視圖上， 用戶可以進(jìn)一步創(chuàng)建視圖， 例如采用以下SQL語句創(chuàng)建視圖vw_2017年個(gè)人賬戶刷卡：

create view vw_2017年個(gè)人賬戶刷卡 as select* from mv_sum_grzhsk where 年度=' 2017';

在物化視圖基礎(chǔ)上建立普通視圖查詢統(tǒng)計(jì)2017年個(gè)人賬戶刷卡用時(shí)0.024 s，如圖3所示;普通視圖查詢統(tǒng)計(jì)用時(shí)221.894s，如圖4所示。普通視圖查詢執(zhí)行的本質(zhì)內(nèi)容與前述物化視圖vw_2017年個(gè)人賬戶刷卡上的查詢相同， 因此查詢結(jié)果也與圖3相同。兩者對(duì)比可以看出物化視圖查詢統(tǒng)計(jì)，比普通視圖速度快了許多。

3 結(jié)束語

綜上所述， 本文研究了使用Oracle物理視圖優(yōu)化查詢， 使查詢執(zhí)行時(shí)間得到了明顯的降低， 數(shù)據(jù)庫性能得到大幅提升， 證實(shí)了優(yōu)化方法的有效性， 為數(shù)據(jù)庫管理與應(yīng)用工作提供了指引。數(shù)據(jù)庫的優(yōu)化是一個(gè)復(fù)雜的過程，本文關(guān)注的僅僅是應(yīng)用層的一些表現(xiàn)， 數(shù)據(jù)庫的運(yùn)維還涉及底層的資源分配、網(wǎng)絡(luò)層的流量控制和操作系統(tǒng)的構(gòu)架， 甚至相同平臺(tái)的不同類型數(shù)據(jù)， 其優(yōu)化方式的原則都要隨之改變， 而不是教條式的簡單修改， 作為使用者而言， 應(yīng)該勤加探索， 借助優(yōu)化軟件、跟蹤分析， 加以人工辨別， 找到一個(gè)適合自己的行之有效優(yōu)化方式。

參考文獻(xiàn)

[1]魏玉芬， 王玥. 基于ORACLE成本優(yōu)化器的SQL查詢優(yōu)化分析與應(yīng)用[J]. 內(nèi)蒙古農(nóng)業(yè)大學(xué)學(xué)報(bào) （自然科學(xué)版），2018， 39 （2）： 88-93.

[2] 童奕媛， 楊林. Oracle數(shù)據(jù)庫性能優(yōu)化實(shí)踐應(yīng)用分析—以某城市商業(yè)銀行財(cái)務(wù)系統(tǒng)為例[J]. 金融科技時(shí)代， 2017 （1）： 31-35.

[3] 李偉，安永麗，胡雄. Oracle 11g SQL和PL/SQL編程指南[M]. 北京：清華大學(xué)出版社， 2014.

[4] 陶春江. 基于ORACLE數(shù)據(jù)庫的SQL優(yōu)化研究[J]. 數(shù)字技術(shù)與應(yīng)用， 2016 （12）： 98.