[Oracle10G新特性]_12.物化視圖
?
??? 關(guān)于物化視圖的這兩個特性，我之前倒還真的是沒有注意過。不光是在10g的版本里，10g之前也沒有注意到Oracle還有這個功能。在學(xué)習(xí)了這么多章的內(nèi)容之后，更加發(fā)現(xiàn)自己不懂的還真的是很多。如果想要能夠深入的了解一項(xiàng)特性，也許仔細(xì)閱讀文檔時唯一的辦法，幾乎任何重寫的教程都不能完全包含所有的功能屬性。
?
??? 不過在我測試了之后，發(fā)現(xiàn)dbms_advisor.tune_mview這個方法無法正常工作，這個有待進(jìn)一步核實(shí)，不過能夠生成所有所需的腳本，還是一件很好的事情，可以省去很多的麻煩。另外關(guān)于重寫的部分，也需要再進(jìn)行深入的了解。
?
--------------------------------------------------------------
?
物化視圖
?
利用強(qiáng)制查詢重寫和新的強(qiáng)大的調(diào)整顧問程序 — 它們使您不再需要憑猜測進(jìn)行工作 — 的引入，在 10g 中管理物化視圖變得更加容易
?
??? 物化視圖 (MV) — 也稱為快照 — 一段時間來已經(jīng)廣泛使用。MV 在一個段中存儲查詢結(jié)果，并且能夠在提交查詢時將結(jié)果返回給用戶，從而不再需要重新執(zhí)行查詢 — 在查詢要執(zhí)行幾次時（這在數(shù)據(jù)倉庫環(huán)境中非常常見），這是一個很大的好處。物化視圖可以利用一個快速刷新機(jī)制從基礎(chǔ)表中全部或增量刷新。
?
??? 假定您已經(jīng)定義了一個物化視圖，如下：
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';
??? 您如何才能知道已經(jīng)為這個物化視圖創(chuàng)建了其正常工作所必需的所有對象？在 Oracle 數(shù)據(jù)庫 10g 之前，這是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 過程來判斷的。這些過程（在 10g 中仍然提供）非常簡要地說明一種特定的功能 — 如快速刷新功能或查詢重寫功能 — 可能用于上述的物化視圖，但不提供如何實(shí)現(xiàn)這些功能的建議。相反，需要對每一個物化視圖的結(jié)構(gòu)進(jìn)行目視檢查，這是非常不實(shí)際的。
?
??? 在 10g 中，新的 DBMS_ADVISOR 程序包中的一個名為 TUNE_MVIEW 的過程使得這項(xiàng)工作變得非常容易：您利用 IN 參數(shù)來調(diào)用程序包，這構(gòu)造了物化視圖創(chuàng)建腳本的全部內(nèi)容。該過程創(chuàng)建一個顧問程序任務(wù) (Advisor Task)，它擁有一個特定的名稱，僅利用 OUT 參數(shù)就能夠把這個名稱傳回給您。
?
??? 下面是一個例子。因?yàn)榈谝粋€參數(shù)是一個 OUT 參數(shù)，所以您需要在 SQL*Plus 中定義一個變量來保存它。
?
SQL> -- 首先定義一個變量來保存 OUT 參數(shù)
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;
??? 現(xiàn)在您可以在該變量中找出顧問程序的名稱。
?
SQL> print adv_name
?
ADV_NAME
-----------------------
TASK_117
?
??? 接下來，通過查詢一個新的 DBA_TUNE_MVIEW 來獲取由這個顧問程序提供的建議。務(wù)必在運(yùn)行該命令之前執(zhí)行 SET LONG 999999，因?yàn)樵撘晥D中的列語句是一個 CLOB，默認(rèn)情況下只顯示 80 個字符。
?
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
?
??? SCRIPT_TYPE 列顯示建議的性質(zhì)。大多數(shù)行將要執(zhí)行，因此名稱為 IMPLEMENTATION。如果接受，則需按照由 ACTION_ID 列指出的特定順序執(zhí)行建議的操作。
?
??? 如果您仔細(xì)查看這些自動生成的建議，那么您將注意到它們與您自己通過目視分析生成的建議是類似的。這些建議合乎邏輯；快速刷新的存在需要在擁有適當(dāng)子句（如那些包含新值的子句）的基礎(chǔ)表上有一個 MATERIALIZED VIEW LOG。STATEMENT 列甚至提供了實(shí)施這些建議的確切 SQL 語句。
?
??? 在實(shí)施的最后一個步驟中，顧問程序建議改變創(chuàng)建物化視圖的方式。注意我們的例子中的不同之處：將一個 count(*) 添加到了物化視圖中。因?yàn)槲覀儗⑦@個物化視圖定義為可快速刷新的，所以必須有 count(*)，以便顧問程序糾正遺漏。
?
??? TUNE_MVIEW 過程不僅在建議方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，還為創(chuàng)建相同的物化視圖指出了更容易和更高效的途徑。有時，顧問程序可以實(shí)際推薦多個物化視圖，以使查詢更加高效。
?
??? 您可能會問，如果任何一個經(jīng)驗(yàn)豐富的 DBA 都能夠找出 MV 創(chuàng)建腳本中缺了什么，然后自己糾正它，那這還有什么用？嗯，顧問程序正是用來完成這項(xiàng)工作的：它是一位經(jīng)驗(yàn)豐富、高度自覺的自動數(shù)據(jù)庫管理員，它可以生成能與人的建議相媲美的建議，但有一個非常重要的不同之處：它免費(fèi)工作，并且不會要求休假或加薪。這一好處使高級 DBA 解放出來，將日常的工作交給較低級的 DBA，從而允許他們將其專業(yè)技能應(yīng)用到更具有戰(zhàn)略意義的目標(biāo)上。
?
??? 您還可以將顧問程序的名稱作為值傳遞給 TUNE_MVIEW 過程中的參數(shù)，這將使用該名稱而非系統(tǒng)生成的名稱生成一個的顧問程序。
?
?
更容易的實(shí)施
?
??? 既然您可以看到建議，那么您可能想實(shí)施它們。一種方式是選擇列 STATEMENT，假脫機(jī)到一個文件，然后執(zhí)行該腳本文件。一種更容易的替代方法是調(diào)用附帶的封裝過程：
?
begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),?
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/
??? 該過程調(diào)用假定您已經(jīng)定義了一個目錄對象，例如：
?
create directory mvtune_outdir as '/home/oracle/mvtune_outdir';
??? 對 dbms_advisor 的調(diào)用將在 /home/oracle/mvtune_outdir 目錄中創(chuàng)建一個名為 mvtune_script.sql 的文件。如果您查看一下這個文件，您將看到：
?
Rem? SQL Access Advisor:Version 10.1.0.1 - Production
Rem
Rem? Username:ARUP
Rem? Task:TASK_117
Rem? Execution date:
Rem
?
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
?
whenever sqlerror CONTINUE
?
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
?
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
?
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
?
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
?
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;
?
whenever sqlerror EXIT SQL.SQLCODE
?
begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/
??? 這個文件包含了您實(shí)施建議所需的一切，從而為您省去了相當(dāng)大的手動創(chuàng)建文件的麻煩。這個自動數(shù)據(jù)庫管理員又一次能夠?yàn)槟瓿晒ぷ鳌?
?
?
重寫或退出！
?
??? 至此，您一定意識到了查詢重寫特性有多重要和多有用。它顯著地減少了 I/O 和處理，并能夠更快地返回結(jié)果。
?
??? 讓我們基于上述例子假定一種情況。用戶執(zhí)行以下查詢：
?
Select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
?
??? 執(zhí)行狀態(tài)顯示以下內(nèi)容：
?
0?? recursive calls
0?? db block gets
6?? consistent gets
0?? physical reads
0?? redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2?? SQL*Net roundtrips to/from client
1?? sorts (memory)
0?? sorts (disk)
?
??? 注意 consistent gets 的值，它為 6 — 一個非常低的值。這個結(jié)果基于的事實(shí)是，重寫了查詢來使用在三個表上創(chuàng)建的兩個物化視圖。選擇不是從表中進(jìn)行的，而是從物化視圖中進(jìn)行，從而消耗了更少的資源（如 I/O 和 CPU）。
?
??? 但如果查詢重寫失敗了，那該怎么辦？它失敗的原因可能有以下幾種：如果初始化參數(shù) query_rewrite_integrity 的值被設(shè)為 TRUSTED，且 MV 的狀態(tài)是 STALE，那么將不會重寫該查詢。您可以通過在查詢之前在會話中設(shè)定這個值來模擬這個過程。
?
alter session set query_rewrite_enabled = false;
?
??? 在這條命令之后，說明計(jì)劃 (EXPLAIN PLAN) 顯示是從所有三個表中而不是從 MV 中作出的選擇。執(zhí)行狀態(tài)現(xiàn)在顯示：
?
0?? recursive calls
0?? db block gets
16? consistent gets
0?? physical reads
0?? redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2?? SQL*Net roundtrips to/from client
2?? sorts (memory)
0?? sorts (disk)
?
??? 注意 consistent gets 的值：它從 6 猛增到了 16。在實(shí)際情況下，這個結(jié)果可能無法接受，因?yàn)闊o法提供所需的額外資源，因此您可能想自己重寫查詢。在這種情況下，您可以確保如果而且只有在查詢被重寫的情況下，才允許進(jìn)行查詢。
?
??? 在 Oracle9i 數(shù)據(jù)庫和更低版本中，決策是單向的：您可以禁用查詢重寫，但不能禁用基礎(chǔ)表訪問。不過 Oracle 數(shù)據(jù)庫 10g 提供了一種機(jī)制 — 通過一個特殊的提示 REWRITE_OR_ERROR 來實(shí)現(xiàn)這一目的。上述查詢將利用該提示寫為：
?
select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
?
??? 注意現(xiàn)在的錯誤消息。
?
from hotels h, reservations r, trans t
???? *
ERROR at line 2:
ORA-30393:a query block in the statement did not rewrite
?
??? ORA-30393 是一種特殊類型的錯誤，它表示無法重寫語句來使用 MV；因此，語句失敗。這種防出錯功能將潛在地防止運(yùn)行時間很長的查詢獨(dú)占系統(tǒng)資源。不過，請注意一個潛在的陷阱：如果 MV 之一（而不是全部）可用于重寫查詢，那么查詢將成功。因此如果能夠使用 MV_ACTUAL_SALES 但不能使用 MV_HOTEL_RESV，那么查詢將被重寫，錯誤將不會出現(xiàn)。在這種情況下，執(zhí)行計(jì)劃將看起來像這樣：
?
Execution Plan
----------------------------------------------------------
0????? SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1??? 0?? SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2??? 1???? HASH JOIN (Cost=10 Card=80 Bytes=2080)
3??? 2?????? MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4??? 3???????? TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5??? 4?????????? INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6??? 3???????? SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7??? 6?????????? TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) (Cost=3 Card=80 Bytes=480)
8??? 2?????? MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560)
?
??? 查詢的確使用 MV_ACTUAL_SALES 而不是 MV_HOTEL_RESV；因而，HOTELS 和 RESERVATIONS 表被訪問。這種方法（特別是后一個表的全表掃描），無疑將使用更多的資源 — 在設(shè)計(jì)查詢和 MV 時您將注意到這種情況。
?
??? 雖然您可以始終利用資源管理器來控制資源使用情況，但使用該提示將防止執(zhí)行查詢，即使在調(diào)用資源管理器之前。資源管理器根據(jù)優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)估計(jì)所需的資源，因此是否存在足夠準(zhǔn)確的統(tǒng)計(jì)數(shù)據(jù)將影響這個過程。不過，重寫或錯誤特性將停止表訪問，而不管統(tǒng)計(jì)數(shù)據(jù)如何。
?
說明計(jì)劃更好地進(jìn)行說明
?
??? 在上一個例子中，請注意說明計(jì)劃輸出中的行：
?
MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)
?
??? 這種訪問方法 —MAT_VIEW REWRITE— 是新增的；它顯示正在訪問 MV，而非表或段。該過程立即告訴您表或 MV 是否被使用 — 即使名稱沒有表明段的本質(zhì)。
?
?
結(jié)論
?
??? 在 10g 中，通過引入強(qiáng)大的新調(diào)整顧問程序 — 它們能夠告訴您許多有關(guān) MV 的設(shè)計(jì)的信息，從而使您不再需要憑猜測進(jìn)行工作，管理 MV 變得更加容易。我尤其喜歡能夠生成一個完整的腳本的調(diào)整建議，這種腳本可以快速實(shí)施，從而顯著地節(jié)省時間和精力。強(qiáng)制重寫或退出查詢的能力在決策支持系統(tǒng)中會非常有幫助 — 在這種系統(tǒng)中必須保留資源，并且未重寫的查詢將不允許在數(shù)據(jù)庫內(nèi)隨意運(yùn)行。
?
?
?
有關(guān)在 10g 中管理物化視圖的更多信息，請參考《Oracle 數(shù)據(jù)庫數(shù)據(jù)倉庫指南 10g 第 1 版 (10.1)》中的第 8 章。
?
?
?
?