Oracle 10g 中管理物化視圖變得更加容易

　　利用強制查詢重寫和新的強大的調整顧問程序 — 它們使您不再需要憑猜測進行工作 — 的引入在 g 中管理物化視圖變得更加容易
　　
　　物化視圖 (MV) — 也稱為快照 — 一段時間來已經廣泛使用MV 在一個段中存儲查詢結果並且能夠在提交查詢時將結果返回給用戶從而不再需要重新執行查詢 — 在查詢要執行幾次時（這在數據倉庫環境中非常常見）這是一個很大的好處物化視圖可以利用一個快速刷新機制從基礎表中全部或增量刷新
　　
　　假定您已經定義了一個物化視圖如下
　　
　　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 rhotel_id = hhotel_id;
　　
　　您如何才能知道已經為這個物化視圖創建了其正常工作所必需的所有對象？在 Oracle 數據庫 g 之前這是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 過程來判斷的這些過程（在 g 中仍然提供）非常簡要地說明一種特定的功能 — 如快速刷新功能或查詢重寫功能 — 可能用於上述的物化視圖但不提供如何實現這些功能的建議相反需要對每一個物化視圖的結構進行目視檢查這是非常不實際的
　　
　　在 g 中新的 DBMS_ADVISOR 程序包中的一個名為 TUNE_MVIEW 的過程使得這項工作變得非常容易您利用 IN 參數來調用程序包這構造了物化視圖創建腳本的全部內容該過程創建一個顧問程序任務 (Advisor Task)它擁有一個特定的名稱僅利用 OUT 參數就能夠把這個名稱傳回給您
　　
　　下面是一個例子因為第一個參數是一個 OUT 參數所以您需要在 SQL*Plus 中定義一個變量來保存它
　　
　　SQL> 首先定義一個變量來保存 OUT 參數
　　SQL> var adv_name varchar()
　　SQL>　begin
　　　dbms_advisortune_mview
　　　　　 (
　　　　　　:adv_name
　　　　　　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 rhotel_id = hhotel_id);
　　* end;
　　
　　現在您可以在該變量中找出顧問程序的名稱
　　
　　SQL> print adv_name
　　
　　ADV_NAME
　　
　　TASK_
　　
　　接下來通過查詢一個新的 DBA_TUNE_MVIEW 來獲取由這個顧問程序提供的建議務必在運行該命令之前執行 SET LONG 因為該視圖中的列語句是一個 CLOB默認情況下只顯示 個字符
　　
　　select script_type statement
　　from　 dba_tune_mview
　　where　task_name = TASK_
　　order　by script_type action_id;
　　
　　下面是輸出
　　
　　SCRIPT_TYPE　　STATEMENT
　　
　　IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON ARUPHOTELS WITH ROWID
　　SEQUENCE (HOTEL_IDCITY)　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON ARUPHOTELS ADD
　　ROWID SEQUENCE (HOTEL_IDCITY)　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON ARUPRESERVATIONS WITH
　　ROWID SEQUENCE (RESV_IDHOTEL_IDCUST_NAME)
　　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON ARUPRESERVATIONS
　　ADD ROWID SEQUENCE (RESV_IDHOTEL_IDCUST_NAME)
　　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION CREATE MATERIALIZED VIEW ARUPMV_HOTEL_RESV　 REFRESH FAST
　　WITH ROWID ENABLE QUERY REWRITE AS SELECT
　　ARUPRESERVATIONSCUST_NAME C ARUPRESERVATIONSRESV_ID
　　C ARUPHOTELSCITY C COUNT(*) M FROM ARUPRESERVATIONS
　　ARUPHOTELS WHERE ARUPHOTELSHOTEL_ID =
　　ARUPRESERVATIONSHOTEL_ID GROUP BY
　　ARUPRESERVATIONSCUST_NAME ARUPRESERVATIONSRESV_ID
　　ARUPHOTELSCITY
　　
　　UNDO　　　　　 DROP MATERIALIZED VIEW ARUPMV_HOTEL_RESV
　　
　　SCRIPT_TYPE 列顯示建議的性質大多數行將要執行因此名稱為 IMPLEMENTATION如果接受則需按照由 ACTION_ID 列指出的特定順序執行建議的操作
　　
　　如果您仔細查看這些自動生成的建議那麼您將注意到它們與您自己通過目視分析生成的建議是類似的這些建議合乎邏輯快速刷新的存在需要在擁有適當子句（如那些包含新值的子句）的基礎表上有一個 MATERIALIZED VIEW LOGSTATEMENT 列甚至提供了實施這些建議的確切 SQL 語句
　　
　　在實施的最後一個步驟中顧問程序建議改變創建物化視圖的方式注意我們的例子中的不同之處將一個 count(*) 添加到了物化視圖中因為我們將這個物化視圖定義為可快速刷新的所以必須有 count(*)以便顧問程序糾正遺漏
　　
　　TUNE_MVIEW 過程不僅在建議方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能還為創建相同的物化視圖指出了更容易和更高效的途徑有時顧問程序可以實際推薦多個物化視圖以使查詢更加高效
　　
　　您可能會問如果任何一個經驗豐富的 DBA 都能夠找出 MV 創建腳本中缺了什麼然後自己糾正它那這還有什麼用？嗯顧問程序正是用來完成這項工作的它是一位經驗豐富高度自覺的自動數據庫管理員它可以生成能與人的建議相媲美的建議但有一個非常重要的不同之處它免費工作並且不會要求休假或加薪這一好處使高級 DBA 解放出來將日常的工作交給較低級的 DBA從而允許他們將其專業技能應用到更具有戰略意義的目標上
　　
　　您還可以將顧問程序的名稱作為值傳遞給 TUNE_MVIEW 過程中的參數這將使用該名稱而非系統生成的名稱生成一個的顧問程序
　　
　　更容易的實施
　　既然您可以看到建議那麼您可能想實施它們一種方式是選擇列 STATEMENT假脫機到一個文件然後執行該腳本文件一種更容易的替代方法是調用附帶的封裝過程
　　
　　begin
　　dbms_advisorcreate_file (
　　dbms_advisorget_task_script (TASK_)　
　　MVTUNE_OUTDIR
　　mvtune_scriptsql
　　);
　　end;
　　/
　　
　　該過程調用假定您已經定義了一個目錄對象例如
　　
　　create directory mvtune_outdir as /home/oracle/mvtune_outdir;
　　
　　對 dbms_advisor 的調用將在 /home/oracle/mvtune_outdir 目錄中創建一個名為 mvtune_scriptsql 的文件如果您查看一下這個文件您將看到
　　
　　Rem　SQL Access Advisor:Version Production
　　Rem
　　Rem　Username:ARUP
　　Rem　Task:TASK_
　　Rem　Execution date:
　　Rem
　　
　　set feedback
　　set linesize
　　set trimspool on
　　set tab off
　　set pagesize
　　
　　whenever sqlerror CONTINUE
　　
　　CREATE MATERIALIZED VIEW LOG ON
　　ARUPHOTELS
　　WITH ROWID SEQUENCE(HOTEL_IDCITY)
　　INCLUDING NEW VALUES;
　　
　　ALTER MATERIALIZED VIEW LOG FORCE ON
　　ARUPHOTELS
　　ADD ROWID SEQUENCE(HOTEL_IDCITY)
　　INCLUDING NEW VALUES;
　　
　　CREATE MATERIALIZED VIEW LOG ON
　　ARUPRESERVATIONS
　　WITH ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)
　　INCLUDING NEW VALUES;
　　
　　ALTER MATERIALIZED VIEW LOG FORCE ON
　　ARUPRESERVATIONS
　　ADD ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)
　　INCLUDING NEW VALUES;
　　
　　CREATE MATERIALIZED VIEW ARUPMV_HOTEL_RESV
　　REFRESH FAST WITH ROWID
　　ENABLE QUERY REWRITE
　　AS SELECT ARUPRESERVATIONSCUST_NAME C ARUPRESERVATIONSRESV_ID C ARUPHOTELSCITY
　　C COUNT(*) M FROM ARUPRESERVATIONS ARUPHOTELS WHERE ARUPHOTELSHOTEL_ID
　　= ARUPRESERVATIONSHOTEL_ID GROUP BY ARUPRESERVATIONSCUST_NAME ARUPRESERVATIONSRESV_ID
　　ARUPHOTELSCITY;
　　
　　whenever sqlerror EXIT SQLSQLCODE
　　
　　begin
　　dbms_advisormark_recommendation(TASK_IMPLEMENTED);
　　end;
　　/
　　
　　這個文件包含了您實施建議所需的一切從而為您省去了相當大的手動創建文件的麻煩這個自動數據庫管理員又一次能夠為您完成工作
　　
　　重寫或退出！
　　
　　至此您一定意識到了查詢重寫特性有多重要和多有用它顯著地減少了 I/O 和處理並能夠更快地返回結果
　　
　　讓我們基於上述例子假定一種情況用戶執行以下查詢
　　
　　Select city sum(actual_rate)
　　from hotels h reservations r trans t
　　where tresv_id = rresv_id
　　and hhotel_id = rhotel_id
　　group by city;
　　
　　執行狀態顯示以下內容
　　
　　　 recursive calls

From:http://tw.wingwit.com/Article/program/Oracle/201311/18173.html