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

2024-08-29 13:50:52

字體：大中小

供稿：網(wǎng)友

利用強(qiáng)制查詢重寫和新的強(qiáng)大的調(diào)整顧問(wèn)程序 — 它們使您不再需要憑猜測(cè)進(jìn)行工作，在 10g 中治理物化視圖變得更加輕易。物化視圖 (MV) — 也稱為快照，已經(jīng)廣泛使用。MV 在一個(gè)段中存儲(chǔ)查詢結(jié)果，并且能夠在提交查詢時(shí)將結(jié)果返回給用戶，從而不再需要重新執(zhí)行查詢 — 在查詢要執(zhí)行幾次時(shí)（這在數(shù)據(jù)倉(cāng)庫(kù)環(huán)境中非經(jīng)常見(jiàn)），這是一個(gè)很大的好處。物化視圖可以利用一個(gè)快速刷新機(jī)制從基礎(chǔ)表中全部或增量刷新。假定您已經(jīng)定義了一個(gè)物化視圖，如下：

　　　　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)為這個(gè)物化視圖創(chuàng)建了其正常工作所必需的所有對(duì)象？在 Oracle 數(shù)據(jù)庫(kù) 10g 之前，這是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 過(guò)程來(lái)判定的。這些過(guò)程（在 10g 中仍然提供）非常簡(jiǎn)要地說(shuō)明一種特定的功能 — 如快速刷新功能或查詢重寫功能 — 可能用于上述的物化視圖，但不提供如何實(shí)現(xiàn)這些功能的建議。相反，需要對(duì)每一個(gè)物化視圖的結(jié)構(gòu)進(jìn)行目視檢查，這是非常不實(shí)際的。在 10g 中，新的 DBMS_ADVISOR 程序包中的一個(gè)名為 TUNE_MVIEW 的過(guò)程使得這項(xiàng)工作變得非常輕易：您利用 IN 參數(shù)來(lái)調(diào)用程序包，這構(gòu)造了物化視圖創(chuàng)建腳本的全部?jī)?nèi)容。該過(guò)程創(chuàng)建一個(gè)顧問(wèn)程序任務(wù) (Advisor Task)，它擁有一個(gè)特定的名稱，僅利用 OUT 參數(shù)就能夠把這個(gè)名稱傳回給您。下面是一個(gè)例子。因?yàn)榈谝粋€(gè)參數(shù)是一個(gè) OUT 參數(shù)，所以您需要在 SQL*Plus 中定義一個(gè)變量來(lái)保存它。 SQL> -- 首先定義一個(gè)變量來(lái)保存 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)在您可以在該變量中找出顧問(wèn)程序的名稱。

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

接下來(lái)，通過(guò)查詢一個(gè)新的 DBA_TUNE_MVIEW 來(lái)獲取由這個(gè)顧問(wèn)程序提供的建議。務(wù)必在運(yùn)行該命令之前執(zhí)行 SET LONG 999999，因?yàn)樵撘晥D中的列語(yǔ)句是一個(gè) CLOB，默認(rèn)情況下只顯示 80 個(gè)字符。

　　　　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)生成的建議，那么您將注重到它們與您自己通過(guò)目視分析生成的建議是類似的。這些建議合乎邏輯；快速刷新的存在需要在擁有適當(dāng)子句（如那些包含新值的子句）的基礎(chǔ)表上有一個(gè) MATERIALIZED VIEW LOG。STATEMENT 列甚至提供了實(shí)施這些建議的確切 SQL 語(yǔ)句。在實(shí)施的最后一個(gè)步驟中，顧問(wèn)程序建議改變創(chuàng)建物化視圖的方式。注重我們的例子中的不同之處：將一個(gè) count(*) 添加到了物化視圖中。因?yàn)槲覀儗⑦@個(gè)物化視圖定義為可快速刷新的，所以必須有 count(*)，以便顧問(wèn)程序糾正遺漏。 TUNE_MVIEW 過(guò)程不僅在建議方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，還為創(chuàng)建相同的物化視圖指出了更輕易和更高效的途徑。有時(shí)，顧問(wèn)程序可以實(shí)際推薦多個(gè)物化視圖，以使查詢更加高效。您可能會(huì)問(wèn)，假如任何一個(gè)經(jīng)驗(yàn)豐富的 DBA 都能夠找出 MV 創(chuàng)建腳本中缺了什么，然后自己糾正它，那這還有什么用？嗯，顧問(wèn)程序正是用來(lái)完成這項(xiàng)工作的：它是一位經(jīng)驗(yàn)豐富、高度自覺(jué)的自動(dòng)數(shù)據(jù)庫(kù)治理員，它可以生成能與人的建議相媲美的建議，但有一個(gè)非常重要的不同之處：它免費(fèi)工作，并且不會(huì)要求休假或加薪。這一好處使高級(jí) DBA 解放出來(lái)，將日常的工作交給較低級(jí)的 DBA，從而答應(yīng)他們將其專業(yè)技能應(yīng)用到更具有戰(zhàn)略意義的目標(biāo)上。您還可以將顧問(wèn)程序的名稱作為值傳遞給 TUNE_MVIEW 過(guò)程中的參數(shù)，這將使用該名稱而非系統(tǒng)生成的名稱生成一個(gè)的顧問(wèn)程序。更輕易的實(shí)施既然您可以看到建議，那么您可能想實(shí)施它們。一種方式是選擇列 STATEMENT，假脫機(jī)到一個(gè)文件，然后執(zhí)行該腳本文件。一種更輕易的替代方法是調(diào)用附帶的封裝過(guò)程：

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

該過(guò)程調(diào)用假定您已經(jīng)定義了一個(gè)目錄對(duì)象，例如：

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

對(duì) dbms_advisor 的調(diào)用將在 /home/oracle/mvtune_outdir 目錄中創(chuàng)建一個(gè)名為mvtune_script.sql 的文件。假如您查看一下這個(gè)文件，您將看到：

　　　　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;　　/　

這個(gè)文件包含了您實(shí)施建議所需的一切，從而為您省去了相當(dāng)大的手動(dòng)創(chuàng)建文件的麻煩。這個(gè)自動(dòng)數(shù)據(jù)庫(kù)治理員又一次能夠?yàn)槟瓿晒ぷ鳌?/div>

上一篇：Oracle可傳輸表空間實(shí)現(xiàn)跨平臺(tái)移植

下一篇：Oracle數(shù)據(jù)庫(kù)用VPD來(lái)確保信息的隱私