無需改變SQL查詢就可以大幅提高查詢性能,。

你是否為等待你的查詢返回結(jié)果而感到疲憊,？你是否已經(jīng)為增強(qiáng)索引和調(diào)優(yōu)SQL而感到疲憊，但仍然不能提高查詢性能,？那么,，你是否已經(jīng)考慮創(chuàng)建物化視圖？有了物化視圖,，那些過去需要數(shù)小時運(yùn)行的報告可以在幾分鐘內(nèi)完成,。物化視圖可以包括聯(lián)接（join）和集合（aggregate），它提供了一種儲存預(yù)計(jì)算結(jié)果的方法。

在執(zhí)行一個查詢時,，優(yōu)化器會判定訪問物化視圖或數(shù)據(jù)駐留的基礎(chǔ)表是否更快一些,。如果優(yōu)化器判定查詢物化視圖是更好的解決方案，那么優(yōu)化器會在一個被稱為“查詢改寫”（query rewrite）的過程中改寫SQL查詢,。在這個過程中,，不需要對任何SQL或應(yīng)用程序代碼進(jìn)行修改，所以任何利用SQL訪問數(shù)據(jù)庫的應(yīng)用程序或特定查詢工具都可得益于使用物化視圖,。當(dāng)為計(jì)算結(jié)果而需要訪問的數(shù)據(jù)數(shù)量遠(yuǎn)大于結(jié)果（如集合）的大小時,，最適合使用查詢改寫，但是它也可被用于加速昂貴的聯(lián)接或規(guī)劃,。

本文首先介紹了優(yōu)化器可以執(zhí)行的查詢改寫類型,。然后，它討論了幫助確定創(chuàng)建最佳物化視圖集的工具,，使優(yōu)化器能夠改寫多個查詢,。利用這些工具創(chuàng)建的物化視圖在其基礎(chǔ)數(shù)據(jù)發(fā)生變化時還可以快速刷新。如果你不知道創(chuàng)建一個物化視圖,、一個索引或同時創(chuàng)建兩者哪種更好,，那么在Oracle數(shù)據(jù)庫10g中引入的SQL Access Advisor可以通過分析給定的工作負(fù)荷幫助你做出決定。

查詢改寫類型

可能有許多類型的查詢改寫,；當(dāng)物化視圖的定義查詢與查詢的文本完全匹配時,，就發(fā)生最簡單和最顯著類型的查詢改寫。但是,，當(dāng)相同物化視圖可用于相應(yīng)多個查詢時,，就可以實(shí)現(xiàn)查詢改寫的最大好處。現(xiàn)在,，我們將舉例說明一些Oracle優(yōu)化器使用的規(guī)則,，以確定它是否將使用物化視圖來響應(yīng)。

對于本文中的示例,，可以考慮將一個星形模式中的PURCHASES表看作事實(shí)表（fact table）,，其范圍由time_key劃分。維度表（dimension table）--TIME,、PRODUCT和CUSTOMERS--包含主鍵 time_key,、product_id和cust_id。在PURCHASES表中有引用各個維度表的外鍵約束,。

考慮一下清單 1中所創(chuàng)建的物化視圖,，該視圖按月按product_id計(jì)算銷售總額和銷售總次數(shù)。注意：對于用于查詢改寫的物化視圖,，必須有ENABLE QUERY REWRITE子句,。還有，初始化參數(shù)QUERY_REWRITE_ENABLED必須被設(shè)置為TRUE。

代碼清單 1：創(chuàng)建月銷售物化視圖



CREATE MATERIALIZED VIEW monthly_sales_mvENABLE QUERY REWRITEASSELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_salesFROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_idGROUP BY t.month, p.product_id;

集合計(jì)算

在本文的示例中,，我們將說明物化視圖的查詢并顯示由EXPLAIN PLAN得到的執(zhí)行計(jì)劃,。清單 2中的查詢要求按月和按產(chǎn)品的平均采購價格。優(yōu)化器可以使用物化視圖monthly_sales_mv,，利用SUM和COUNT集合計(jì)算平均采購價格,。這個示例說明了一種叫做“集合計(jì)算”的技術(shù)。

代碼清單 2：獲得平均(AVG)采購價格



SELECT t.month, p.product_id, AVG(ps.purchase_price) as avg_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_idGROUP BY t.month, p.product_id; Id Operation Name ________________________________________________ SELECT STATEMENT MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV

Joinback

joinback技術(shù)非常有用,，因?yàn)樗试S當(dāng)物化視圖中沒有列時進(jìn)行查詢改寫,。清單 3中的查詢要求按月和按產(chǎn)品類別的銷售總額，而該物化視圖中并沒有product.category列,。然而,，產(chǎn)品表的主鍵product_id列則位于物化視圖中。因此,，優(yōu)化器可以將物化視圖與產(chǎn)品表聯(lián)接起來以得到產(chǎn)品類別,。

代碼清單 3：通過joinback獲得銷售總額



SELECT t.month, p.category, SUM(ps.purchase_price) as sum_of_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_idGROUP BY t.month, p.category; Id Operation Name __________________________________________________ 0 SELECT STATEMENT 1 SORT GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCT 4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV

使用維度進(jìn)行查詢改寫

在一個使用維度建模技巧設(shè)計(jì)的典型數(shù)據(jù)倉庫中，數(shù)據(jù)中存在著著名的“層次關(guān)系”,。例如,，在時間層次中，“天”積累成“月”,，“月”又積累成“年”,。在Oracle數(shù)據(jù)庫中，可以使用CREATE DIMENSION語句創(chuàng)建一個叫做“DIEMNSION”的對象,，向優(yōu)化器聲明這種關(guān)系,。維度對象是一個描述性對象，除了其元數(shù)據(jù)外,，它不占用空間,。使用DIMENSION對象聲明的關(guān)系據(jù)說是可信的。Oracle不會驗(yàn)證這一關(guān)系對于你的數(shù)據(jù)是否一定成立,，它只是假設(shè)數(shù)據(jù)庫管理員已經(jīng)判定這些關(guān)系是正確的,。可信信息的其他示例是使用NOVALIDATE RELY標(biāo)記的約束及注冊為物化視圖的先存表,。

對于采用可信信息（包括維度）的查詢改寫,，初始化參數(shù)QUERY_ REWRITE_INTEGRITY必須被設(shè)置為TRUSTED，如下所示：


ALTER SESSION SET query_rewrite_integrity = TRUSTED;



例如,，假設(shè)有一個時間維度，其聲明如下：


CREATE DIMENSION time_dimLEVEL time_key IS time.time_keyLEVEL month IS time.monthLEVEL quarter IS time.quarterLEVEL year IS time.yearHIERARCHY calendar_rollup ( time_key CHILD OF month CHILD OF quarter CHILD OF year)ATTRIBUTE time_key determines (day_of_week, holiday)ATTRIBUTE month determines (month_name);



現(xiàn)在,，如果具有清單 4中要求按年的銷售額的查詢,，你仍然可以使用monthly_sales_mv物化視圖，因?yàn)榫S度對象中的HIERARCHY子句告訴Oracle數(shù)據(jù)庫月銷售額可以積累成年銷售額。它利用前面描述的joinback技巧由物化視圖中的“月”列得到“年”列的值,。

代碼清單 4：通過joinback和HIERARCHY獲得銷售總額



SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_idGROUP BY t.year, p.category; Id Operation Name __________________________________________________ 0 SELECT STATEMENT 1 SORT GROUP BY 2 HASH JOIN 3 HASH JOIN 4 VIEW 5 SORT UNIQUE 6 TABLE ACCESS FULL TIME 7 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV 8 TABLE ACCESS FULL PRODUCT

維度的ATTRIBUTE子句指明了一對一關(guān)系,。例如，你可以判定從time_key開始是一周中的哪一天,。假設(shè)你希望得到每年1月份的銷售總額：你仍然可以使用清單 5中所示的monthly_sales_mv物化視圖,。注意該查詢的WHERE子句如何具有一個在物化視圖中沒有出現(xiàn)的選擇條件。

代碼清單 5：通過joinback和ATTRIBUTE獲得銷售總額



SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.month_name = ‘January‘GROUP BY t.year, p.category; Id Operation Name __________________________________________________ 0 SELECT STATEMENT 1 SORT GROUP BY 2 HASH JOIN 3 HASH JOIN 4 VIEW 5 SORT UNIQUE 6 TABLE ACCESS FULL TIME 7 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV 8 TABLE ACCESS FULL PRODUCT

如果優(yōu)化器并未如期改寫一個查詢,，可以使用DBMS_MVIEW .EXPLAIN_REWRITE 過程來診斷該問題,。這一特性出現(xiàn)在Oracle9i數(shù)據(jù)庫及以后的版本中。

過濾后的數(shù)據(jù)

到目前為止,，我們所給出的所有示例都使用了與采購表中的所有數(shù)據(jù)對應(yīng)的物化視圖,。Oracle9i數(shù)據(jù)庫具備在物化視圖僅有一個數(shù)據(jù)子集情況下改寫查詢的能力。例如,，如果你只對1997年到2002年的銷售額感興趣,，你可以將物化視圖修改如下：


CREATE MATERIALIZED VIEW five_yr_monthly_sales_mvENABLE QUERY REWRITEASSELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1997 and 2002GROUP BY t.month, p.product_id;



此物化視圖可用于響應(yīng)要求從1997年至2002年數(shù)據(jù)的查詢，例如,，清單 6中的查詢要求2000年的銷售額,。

代碼清單 6：只查詢物化視圖


SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year = 2000GROUP BY t.month, p.product_id; Id Operation Name __________________________________________________ SELECT STATEMENT 1 HASH JOIN 2 VIEW 3 SORT UNIQUE 4 TABLE ACCESS FULL TIME 5 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV
在Oracle9i數(shù)據(jù)庫中，如果物化視圖中沒有查詢所需要的全部數(shù)據(jù),，查詢就不會使用物化視圖,。在Oracle數(shù)據(jù)庫10g中，已經(jīng)放松了這一限制,，因此查詢改寫可以由物化視圖中獲得盡可能多的數(shù)據(jù),，并利用細(xì)目表獲得物化視圖中沒有的數(shù)據(jù)。和往常一樣,，優(yōu)化器在做出執(zhí)行此操作的決定時考慮了有改寫和無改寫情況下的查詢成本,。

例如，清單 7中的查詢要求2000年至2003年之間的月銷售額,，它將使用從2000年至2002年的物化視圖,，而只需要2003年的細(xì)目表。

代碼清單 7：查詢物化視圖和細(xì)目表



SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year BETWEEN 2000 and 2003GROUP BY t.month, p.product_id; Id Operation Name __________________________________________________ 0 SELECT STATEMENT 1 SORT GROUP BY 2 VIEW 3 UNION-ALL 4 HASH JOIN 5 VIEW 6 SORT UNIQUE 7 TABLE ACCESS FULL TIME 8 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV 9 SORT GROUP BY 10 NESTED LOOPS 11 HASH JOIN 12 TABLE ACCESS FULL TIME 13 PARTITION RANGE ALL 14 TABLE ACCESS FULL PURCHASES 15 INDEX RANGE SCAN PRODUCT_PK_INDEX

使用失效的物化視圖進(jìn)行查詢改寫

你可能想知道如果細(xì)目表中的數(shù)據(jù)發(fā)生了變化會發(fā)生什么情況,。查詢改寫仍將使用物化視圖嗎,？答案決定于初始化參數(shù)QUERY_REWRITE_ INTEGRITY的設(shè)置。QUERY_REWRITE_INTEGRITY參數(shù)有三個取值： STALE_TOLERATED表示即使細(xì)目表中的數(shù)據(jù)已經(jīng)發(fā)生了變化,，也仍然使用物化視圖,。 TRUSTED 表示物化視圖未失效時才使用該視圖。但是,，查詢改寫可以使用信任關(guān)系,，如那些由維度對象或尚未生效的約束所聲明的關(guān)系,。 ENFORCED（缺省）表示當(dāng)物化視圖保證能給出與使用細(xì)目表相同的結(jié)果時才使用它,。使用這一參數(shù)意味著查詢改寫將不使用失效的物化視圖或信任關(guān)系,。
正確的設(shè)置決定于應(yīng)用程序的數(shù)據(jù)需求,。使用失效物化視圖的查詢改寫可能會產(chǎn)生與沒有使用查詢改寫時不同的結(jié)果,。然而，如果使用細(xì)目數(shù)據(jù),，可能會因?yàn)轫憫?yīng)查詢需要處理的大量數(shù)據(jù)而使性能惡化,。在一個數(shù)據(jù)倉庫中,，通常使用TRUSTED完整級別，因?yàn)檫@樣才可以保證你只使用那些具有最新數(shù)據(jù)的物化視圖,；然而,，被聲明為正確（可信任）的關(guān)系也可用于查詢改寫。在大多數(shù)數(shù)據(jù)倉庫中,，這些關(guān)系已經(jīng)在提取,、轉(zhuǎn)換和加載（ETL）過程得到了驗(yàn)證，因此不再需要進(jìn)行驗(yàn)證,。

分區(qū)變化跟蹤

在Oracle9i數(shù)據(jù)庫中,，Oracle引入了分區(qū)變化跟蹤（PCT，Partition Change Tracking）,。利用這一特性,，Oracle9i數(shù)據(jù)庫可以跟蹤物化視圖的哪一部分對應(yīng)于分區(qū)細(xì)目表的已更新部分。因此,，如果查詢不需要已更新表的部分,，那么該物化視圖仍然可以使用。

為了在物化視圖中跟蹤一個細(xì)目表的變化,，必須對該表進(jìn)行分區(qū),，并且該物化視圖（在SELECT列表中）必須包括細(xì)目表的分區(qū)鍵或一個特殊函數(shù)：DBMS_MVIEW.PMARKER。此函數(shù)為細(xì)目表中的每個分區(qū)生成一個唯一的標(biāo)識符,。

例如,，由time_key對采購表進(jìn)行分區(qū)。清單 8中創(chuàng)建的物化視圖與前面使用的monthly_sales_mv 物化視圖幾乎完全相同,，只是該物化視圖在采購表上包含了一個附加的DBMS_MVIEW.PMARKER函數(shù),。通過包含這一函數(shù)，當(dāng)更新采購表時該物化視圖允許PCT,。注意：該物化視圖自身并不需要被分區(qū),。

代碼清單 8：具有DBMS_MVIEW.PMARKER函數(shù)的物化視圖



CREATE MATERIALIZED VIEW monthly_sales_pct_mvENABLE QUERY REWRITEASSELECT DBMS_MVIEW.PMARKER(ps.rowid) pm, t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_idGROUP BY DBMS_MVIEW.PMARKER(ps.rowid), t.month, p.product_id;

現(xiàn)在，假設(shè)我們向采購表中增加一個2003年4月的新分區(qū),，而且一個用戶發(fā)出了一個請求2002年3月的數(shù)據(jù)的查詢,，如清單 9所示,。在此查詢中，我們并不關(guān)心2003年4月已更新的數(shù)據(jù),，所以將利用物化圖對其進(jìn)行改寫，即使該物化視圖已經(jīng)失效也是如此,。

代碼清單 9：使用失效的物化視圖進(jìn)行查詢改寫



SELECT t.month, p.product_id, SUM(ps.purchase_price)FROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND ps.time_key >= TO_DATE(‘01-03-2003‘, ‘DD-MM-YYYY‘) AND ps.time_key < TO_DATE(‘01-04-2003‘, ‘DD-MM-YYYY‘) GROUP BY t.month, p.product_id; Id Operation Name __________________________________________________ 0 SELECT STATEMENT 1 SORT GROUP BY 2 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_PCT_MV

如果查詢要求從1月至4月的數(shù)據(jù),，在Orcale9i中，將不會為使用物化視圖而對該查詢進(jìn)行改寫,。但在Oracle數(shù)據(jù)庫10g中,，可以使用MONTHLY_SALES_ PCT_MV和細(xì)目表的結(jié)合對該查詢進(jìn)行改寫。

使用多個物化視圖進(jìn)行查詢改寫

前面曾經(jīng)提到,，在Oracle10g數(shù)據(jù)庫中,，查詢改寫已經(jīng)得到了增強(qiáng)，所以它可以使用一個物化視圖的部分?jǐn)?shù)據(jù)以及細(xì)目表的其余數(shù)據(jù)來響應(yīng)查詢,。事實(shí)上,，查詢改寫可以結(jié)合使用兩個或多個物化視圖。例如,，假設(shè)你為每5年的數(shù)據(jù)價值維護(hù)一個獨(dú)立的物化視圖： monthly_sales_1990-1994,、 monthly_sales_1995_to_2000、 monthly_sales_2001_to_2005,，等等,。

那么，對于需要從1993年至2003年數(shù)據(jù)的清單 10中的查詢,，查詢改寫可以利用全部的三個物化視圖,。

代碼清單 10



SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales,FROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1993 and 2003GROUP BY t.month, p.product_id; Id Operation Name ---------------------------------------------------------0 SELECT STATEMENT 1 SORT GROUP BY 2 VIEW 3 UNION-ALL 4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_2001_TO_2005 5 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1995_TO_2000 6 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1990_TO_1994

代碼清單 11


SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1989 and 1999GROUP BY t.month, p.product_id; Id Operation Name ---------------------------------------------------------1 SELECT STATEMENT 1 SORT GROUP BY 2 VIEW 3 UNION-ALL 4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1995_TO_2000 5 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1990_TO_1994 6 SORT GROUP BY 7 NESTED LOOPS 8 NESTED LOOPS 9 TABLE ACCESS FULL TIME 10 PARTITION RANGE ITERATOR 11 TABLE ACCESS FULL PURCHASES 12 INDEX RANGE SCAN PRODUCT_PK_INDEX
清單 11中的查詢需要從1989年至1999年的數(shù)據(jù)，所以查詢改寫可以使用物化視圖monthly_sales_1990_to_1994和monthly_sales_1995_to_2000,，并由細(xì)目表獲得1989年的數(shù)據(jù),。這一過程實(shí)質(zhì)上會比由細(xì)目表中獲得所有數(shù)據(jù)更快一些。

Oracle10g數(shù)據(jù)庫在查詢改寫方面有其他幾個改進(jìn),。在這些改進(jìn)中值得注意的是Oracle10g數(shù)據(jù)庫能夠更好地支持集合運(yùn)算符（UNION,、 UNIONALL等）增強(qiáng)了對多個表實(shí)例的查詢的增強(qiáng)，并提供了對分區(qū)變化跟蹤中的LIST 和RANGE-LIST分區(qū)類型的支持,。

工具

你可能會一邊閱讀本文,，一邊自言自語：“嗯，我想我理解了你的意思,，但是否有某些工具可以為我完成所有這些工作呢,？”答案是肯定的。事實(shí)上,，這些工具還相當(dāng)多,。

Oracle9i數(shù)據(jù)庫引用了explain_mview和explain_rewrite,。應(yīng)用編程接口（API）EXPLAIN_MVIEW采用一個物化視圖定義，并建議可使用何種類型的分區(qū)變化跟蹤操作,、是否可能進(jìn)行快速刷新,，以及可以完成何種類型的查詢改寫。當(dāng)一個查詢未被改寫時,，API EXPLAIN_REWRITE將告訴你SQL查詢?yōu)槭裁床皇褂貌樵兏膶?。在兩種情況下，工具包都會告訴你問題所在--例如,，不能在一個特定列上進(jìn)行連接,，但兩個包都不會準(zhǔn)確告訴你如果解決這個問題。這時,，就可以使用包含在Oracle10g數(shù)據(jù)庫中的兩個新工具--TUNE_MVIEW和SQL Access Advisor來幫助你解決這個問題,。

TUNE_MVIEW API將告訴你如何編寫物化視圖，使其可以快速刷新,，并可以使用本文所描述的盡可能多的高級查詢改寫類型,。TUNE_MVIEW API 的使用非常簡單：只需要將你的物化視圖語句交給它，它就會判定該物化視圖的最佳形式,。但是,，如果你看到你的原始物化視圖已經(jīng)被轉(zhuǎn)換為多個新版本，也不要感到奇怪,。

讓我們來看看TUNE_MVIEW 如何能夠轉(zhuǎn)換你的物化視圖,。假設(shè)我們有一個簡單的查詢，并將其傳遞給EXPLAIN_MVIEW,，如清單 12所示,，判斷該物化視圖在當(dāng)前形式下是否可以快速刷新。

代碼清單 12



BEGIN dbms_mview.explain_mview( ‘CREATE MATERIALIZED VIEW customer_mv BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT c.customer_id, c.town, COUNT(DISTINCT(product_id)) AS dist_promo_cnt FROM purchases ps, customer c WHERE ps.customer_id = c.customer_id GROUP BY c.customer_id, c.town‘, ‘ID1‘); END; /-- see if REFRESH FAST capability is allowed (Y) or not (N)SELECT capability_name, possibleFROM mv_capabilities_tableWHERE capability_name = ‘REFRESH_FAST‘ and STATEMENT_ID = ‘ID1‘;CAPABILITY_NAME P---------------------------------REFRESH_FAST N

現(xiàn)在讓我們使用相同的查詢,，并將其傳遞給TUNE_MVIEW,，如以下代碼所示：


variable task_name varchar2(2000);BEGINDBMS_ADVISOR.TUNE_MVIEW (:task_name,‘CREATE MATERIALIZED VIEW customer_mv BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT c.customer_id, c.town, COUNT(DISTINCT(product_id)) AS dist_promo_cnt FROM purchases ps, customer c WHERE ps.customer_id = c.customer_id GROUP BY c.customer_id, c.town‘);END;/

代碼清單 13


SELECT statement FROM user_tune_mview WHERE task_name = :task_name;CREATE MATERIALIZED VIEW EASYDW.CUSTOMER_MV BUILD IMMEDIATEREFRESH FAST WITH ROWID ENABLE QUERY REWRITEAS SELECT EASYDW.PURCHASES.PRODUCT_ID C1, EASYDW.CUSTOMER.TOWN C2, EASYDW.CUSTOMER.CUSTOMER_ID C3, COUNT(*) M1FROM EASYDW.PURCHASES, EASYDW.CUSTOMERWHERE EASYDW.CUSTOMER.CUSTOMER_ID = EASYDW.PURCHASES.CUSTOMER_IDGROUP BY EASYDW.PURCHASES.PRODUCT_ID, EASYDW.CUSTOMER.TOWN, EASYDW.CUSTOMER.CUSTOMER_ID;目錄視圖USER_TUNE_MVIEW將顯示所得到的物化視圖，如清單 13所示,。盡管它看起


來與我們的原始物化視圖有點(diǎn)不同,，但在可以使用原始物化視圖的地方，仍然可以使用該

物化視圖改寫任何查詢,，此外,，還可以快速刷新。

你也可以生成一個腳本來執(zhí)行這些建議,，你可能希望做的僅有修改就是改變物化視圖的名稱,，以及指定物化視圖應(yīng)當(dāng)放在哪里的存儲語句和表空間。

現(xiàn)在,，我們已經(jīng)有了一個物化視圖,，但如果我們不知道創(chuàng)建什么物化視圖,，那么應(yīng)當(dāng)怎么辦？這時,，SQL Access Advisor可以幫助你,，因?yàn)樗鼤g覽你的系統(tǒng)，并它認(rèn)為需要的索引和物化視圖,。

這些建議是基于實(shí)際的工作負(fù)荷或根據(jù)你的模式所做出的假設(shè)提出的,。當(dāng)提供了SQL語句的實(shí)際工作負(fù)荷時，將得到最好的結(jié)果,。這一工作負(fù)荷可由SQL緩存的當(dāng)前內(nèi)容、SQL 調(diào)優(yōu)集合（Tuning Set）,、Oracle9i Summary Advisor工作負(fù)荷或用戶提供的工作負(fù)荷表（包含你已經(jīng)定義的SQL語句）獲得,。

SQL Access Advisor既可以通過命令行API使用，也可以通過企業(yè)管理器（Enterprise Manager）的一部分--SQL Access Advisor向?qū)褂?。使用該向?qū)?，在顯示這些建議之前只需要完成三個步驟。讓我們來看看如何通常命令行界面使用SQL Access Advisor：

首先,，創(chuàng)建一個包含,，這一調(diào)優(yōu)過程所有信息的任務(wù)。然后,，該任務(wù)將利用工作負(fù)荷信息來生成作為任務(wù)的一部分存儲的調(diào)優(yōu)建議,。因此，整個過程是完全獨(dú)立的,，而且允許各個任務(wù)稍有不同,，以便人們能夠看到對配置進(jìn)行修改后的效果。在清單 14所示的示例中,，是通過手工定義SQL語句對工作負(fù)荷進(jìn)行定義的,。

代碼清單 14



DECLAREtask_desc VARCHAR2(100);task_id NUMBER;task_name VARCHAR2(30);workload_name VARCHAR2(30);BEGIN task_name := ‘Task_mag‘; dbms_advisor.create_task (DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name, ‘My Advisor Task‘, DBMS_ADVISOR.SQLACCESS_WAREHOUSE); dbms_advisor.set_task_parameter (‘Task_mag‘, ‘EVALUATION_ONLY‘, ‘FALSE‘); dbms_advisor.set_task_parameter (‘Task_mag‘, ‘EXECUTION_TYPE‘, ‘FULL‘); -- create the workload workload_name :=‘Workload_mv‘; dbms_advisor.create_sqlwkld(workload_name, ‘MV workload‘ , NULL); -- now link the two together dbms_advisor.add_sqlwkld_ref(task_name, workload_name) ; -- add a SQL statement dbms_advisor.add_sqlwkld_statement (workload_name,‘App‘,‘a(chǎn)ction‘, NULL,15,3000,423,507,60,704, 3,‘16-FEB-2002‘,80, ‘EASYDW‘, ‘SELECT c.customer_id, c.town, COUNT(DISTINCT(product_id)) AS dist_promo_cnt FROM purchases ps, customer c WHERE ps.customer_id = c.customer_id GROUP BY c.customer_id, c.town‘);END;/

一旦定義了工作負(fù)荷和任務(wù)，就可以生成如下所示的建議,，該建議使用了EXECUTE_TASK 并指定了所創(chuàng)建任務(wù)的名字--Task_mag：


execute dbms_advisor.execute_task (‘Task_mag‘);



根據(jù)工作負(fù)荷的復(fù)雜性,，生成建議的時間可以由幾秒到幾分鐘不等。因此,，盡管這個過程可以交互式運(yùn)行,，但你可能希望考慮提交一個任務(wù)，這就是企業(yè)管理器中的向?qū)瓿傻墓ぷ鳌?br>
你可以通過查詢表USER_ADVISOR_RECOMMENDATIONS 來快速檢查是否有關(guān)于task_name的建議,。在對本例進(jìn)行此操作時,，我們會看到已經(jīng)提出了一個建議。


SELECT ‘No of Recommendations:‘ , COUNT(*) FROM user_advisor_recommendations r WHERE task_name=‘Task_mag‘;‘NOOFRECOMMENDATIONS:‘ COUNT(*)---------------------- ----------No of Recommendations: 1



單個建議可以導(dǎo)致多個操作,。對于此示例,，SQL Access Advisor建議創(chuàng)建物化視圖日志,、一個CREATE MATERIALIZED VIEW，以及一個用來分析物化視圖的調(diào)用（受版面限制,，這里未給出）,。

盡管你可以查詢各種目錄視圖來查看這些操作，但查看它們的最簡單方法就是生成一個腳本,，如下所示：


executedbms_advisor.create_file(dbms_advisor.get_task_script(‘Task_mag‘), ‘ADVISOR_RESULTS‘, ‘mag_example.sql‘);



在清單 15中,，你可以看到該腳本的一段摘錄，顯示了為我們的查詢所建立的物化視圖,。

代碼清單 15



Rem Access Advisor RemRem Username: EASYDWRem Task: My_TaskRem Execution date: 20/05/2003 14:36Rem...CREATE MATERIALIZED VIEW "EASYDW"."MV$$_002D0000"REFRESH FAST WITH ROWIDENABLE QUERY REWRITEAS SELECT EASYDW.PURCHASES.PRODUCT_ID C1, EASYDW.CUSTOMER.TOWN C2, EASYDW.CUSTOMER.CUSTOMER_ID C3, COUNT(*) M1 FROM EASYDW.PURCHASES, EASYDW.CUSTOMER WHERE EASYDW.CUSTOMER.CUSTOMER_ID = EASYDW.PURCHASES.CUSTOMER_ID GROUP BY EASYDW.PURCHASES.PRODUCT_ID, EASYDW.CUSTOMER.TOWN, EASYDW.CUSTOMER.CUSTOMER_ID;...

結(jié)論

通過使用查詢改寫,，你可以利用幾個物化視圖顯著改進(jìn)許多查詢的性能，從而減少了保持物化視圖與基礎(chǔ)細(xì)目數(shù)據(jù)同步所需要的磁盤空間占用與刷新時間,。