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

2024-08-29 13:50:52

字體：大中小

來源：轉載

供稿：網友

利用強制查詢重寫和新的強大的調整顧問程序 — 它們使您不再需要憑猜測進行工作，在 10g 中治理物化視圖變得更加輕易。物化視圖 (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 r.hotel_id = h.hotel_id';　

您如何才能知道已經為這個物化視圖創建了其正常工作所必需的所有對象？在 Oracle 數據庫 10g 之前，這是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 過程來判定的。這些過程（在 10g 中仍然提供）非常簡要地說明一種特定的功能 — 如快速刷新功能或查詢重寫功能 — 可能用于上述的物化視圖，但不提供如何實現這些功能的建議。相反，需要對每一個物化視圖的結構進行目視檢查，這是非常不實際的。在 10g 中，新的 DBMS_ADVISOR 程序包中的一個名為 TUNE_MVIEW 的過程使得這項工作變得非常輕易：您利用 IN 參數來調用程序包，這構造了物化視圖創建腳本的全部內容。該過程創建一個顧問程序任務 (Advisor Task)，它擁有一個特定的名稱，僅利用 OUT 參數就能夠把這個名稱傳回給您。下面是一個例子。因為第一個參數是一個 OUT 參數，所以您需要在 SQL*Plus 中定義一個變量來保存它。 SQL> -- 首先定義一個變量來保存 OUT 參數：

　　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;　

現在您可以在該變量中找出顧問程序的名稱。

　　　　SQL> PRint adv_name　　　　ADV_NAME　　-----------------------　　TASK_117

接下來，通過查詢一個新的 DBA_TUNE_MVIEW 來獲取由這個顧問程序提供的建議。務必在運行該命令之前執行 SET LONG 999999，因為該視圖中的列語句是一個 CLOB，默認情況下只顯示 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 列顯示建議的性質。大多數行將要執行，因此名稱為 IMPLEMENTATION。假如接受，則需按照由 ACTION_ID 列指出的特定順序執行建議的操作。假如您仔細查看這些自動生成的建議，那么您將注重到它們與您自己通過目視分析生成的建議是類似的。這些建議合乎邏輯；快速刷新的存在需要在擁有適當子句（如那些包含新值的子句）的基礎表上有一個 MATERIALIZED VIEW LOG。STATEMENT 列甚至提供了實施這些建議的確切 SQL 語句。在實施的最后一個步驟中，顧問程序建議改變創建物化視圖的方式。注重我們的例子中的不同之處：將一個 count(*) 添加到了物化視圖中。因為我們將這個物化視圖定義為可快速刷新的，所以必須有 count(*)，以便顧問程序糾正遺漏。 TUNE_MVIEW 過程不僅在建議方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，還為創建相同的物化視圖指出了更輕易和更高效的途徑。有時，顧問程序可以實際推薦多個物化視圖，以使查詢更加高效。您可能會問，假如任何一個經驗豐富的 DBA 都能夠找出 MV 創建腳本中缺了什么，然后自己糾正它，那這還有什么用？嗯，顧問程序正是用來完成這項工作的：它是一位經驗豐富、高度自覺的自動數據庫治理員，它可以生成能與人的建議相媲美的建議，但有一個非常重要的不同之處：它免費工作，并且不會要求休假或加薪。這一好處使高級 DBA 解放出來，將日常的工作交給較低級的 DBA，從而答應他們將其專業技能應用到更具有戰略意義的目標上。您還可以將顧問程序的名稱作為值傳遞給 TUNE_MVIEW 過程中的參數，這將使用該名稱而非系統生成的名稱生成一個的顧問程序。更輕易的實施既然您可以看到建議，那么您可能想實施它們。一種方式是選擇列 STATEMENT，假脫機到一個文件，然后執行該腳本文件。一種更輕易的替代方法是調用附帶的封裝過程：

　　　　begin　　dbms_advisor.create_file (　　dbms_advisor.get_task_script ('TASK_117'),　　　'MVTUNE_OUTDIR',　　'mvtune_script.sql'　　);　　end;　　/

該過程調用假定您已經定義了一個目錄對象，例如：

　　　　create Directory mvtune_outdir as '/home/oracle/mvtune_outdir';

對 dbms_advisor 的調用將在 /home/oracle/mvtune_outdir 目錄中創建一個名為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;　　/　

這個文件包含了您實施建議所需的一切，從而為您省去了相當大的手動創建文件的麻煩。這個自動數據庫治理員又一次能夠為您完成工作。

上一篇：Oracle可傳輸表空間實現跨平臺移植

下一篇：Oracle數據庫用VPD來確保信息的隱私