利用強制查詢重寫和新的強大的調(diào)整顧問程序 — 它們使您不再需要憑猜測進(jìn)行工作 — 的引入，在 10g 中治理物化視圖變得更加輕易
物化視圖 (MV) — 也稱為快照 — 一段時間來已經(jīng)廣泛使用。MV 在一個段中存儲查詢結(jié)果，并且能夠在提交查詢時將結(jié)果返回給用戶，從而不再需要重新執(zhí)行查詢 — 在查詢要執(zhí)行幾次時（這在數(shù)據(jù)倉庫環(huán)境中非經(jīng)常見），這是一個很大的好處。物化視圖可以利用一個快速刷新機制從基礎(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 中仍然提供）非常簡要地說明一種特定的功能 — 如快速刷新功能或查詢重寫功能 — 可能用于上述的物化視圖，但不提供如何實現(xiàn)這些功能的建議。相反，需要對每一個物化視圖的結(jié)構(gòu)進(jìn)行目視檢查，這是非常不實際的。
在 10g 中，新的 DBMS_ADVISOR 程序包中的一個名為 TUNE_MVIEW 的過程使得這項工作變得非常輕易：您利用 IN 參數(shù)來調(diào)用程序包，這構(gòu)造了物化視圖創(chuàng)建腳本的全部內(nèi)容。該過程創(chuàng)建一個顧問程序任務(wù) (Advisor Task)，它擁有一個特定的名稱，僅利用 OUT 參數(shù)就能夠把這個名稱傳回給您。
下面是一個例子。因為第一個參數(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ù)必在運行該命令之前執(zhí)行 SET LONG 999999，因為該視圖中的列語句是一個 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 列甚至提供了實施這些建議的確切 SQL 語句。
在實施的最后一個步驟中，顧問程序建議改變創(chuàng)建物化視圖的方式。注重我們的例子中的不同之處：將一個 count(*) 添加到了物化視圖中。因為我們將這個物化視圖定義為可快速刷新的，所以必須有 count(*)，以便顧問程序糾正遺漏。
TUNE_MVIEW 過程不僅在建議方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，還為創(chuàng)建相同的物化視圖指出了更輕易和更高效的途徑。有時，顧問程序可以實際推薦多個物化視圖，以使查詢更加高效。
您可能會問，假如任何一個經(jīng)驗豐富的 DBA 都能夠找出 MV 創(chuàng)建腳本中缺了什么，然后自己糾正它，那這還有什么用？嗯，顧問程序正是用來完成這項工作的：它是一位經(jīng)驗豐富、高度自覺的自動數(shù)據(jù)庫治理員，它可以生成能與人的建議相媲美的建議，但有一個非常重要的不同之處：它免費工作，并且不會要求休假或加薪。這一好處使高級 DBA 解放出來，將日常的工作交給較低級的 DBA，從而答應(yīng)他們將其專業(yè)技能應(yīng)用到更具有戰(zhàn)略意義的目標(biāo)上。
您還可以將顧問程序的名稱作為值傳遞給 TUNE_MVIEW 過程中的參數(shù)，這將使用該名稱而非系統(tǒng)生成的名稱生成一個的顧問程序。
更輕易的實施
既然您可以看到建議，那么您可能想實施它們。一種方式是選擇列 STATEMENT，假脫機到一個文件，然后執(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;
/
這個文件包含了您實施建議所需的一切，從而為您省去了相當(dāng)大的手動創(chuàng)建文件的麻煩。
這個自動數(shù)據(jù)庫治理員又一次能夠為您完成工作。
重寫或退出！
至此，您一定意識到了查詢重寫特性有多重要和多有用。它顯著地減少了 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
