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

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

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

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

查詢改寫類型

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

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

考慮一下清單 1中所創(chuàng)建的物化視圖，該視圖按月按product_id計(jì)算銷售總額和銷售總次數(shù)。注意：對(duì)于用于查詢改寫的物化視圖，必須有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)品的平均采購價(jià)格。優(yōu)化器可以使用物化視圖monthly_sales_mv，利用SUM和COUNT集合計(jì)算平均采購價(jià)格。這個(gè)示例說明了一種叫做“集合計(jì)算”的技術(shù)。

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



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)物化視圖中沒有列時(shí)進(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)行查詢改寫

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

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


ALTER SESSION SET query_rewrite_integrity = TRUSTED;



例如，假設(shè)有一個(gè)時(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度對(duì)象中的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子句指明了一對(duì)一關(guān)系。例如，你可以判定從time_key開始是一周中的哪一天。假設(shè)你希望得到每年1月份的銷售總額：你仍然可以使用清單 5中所示的monthly_sales_mv物化視圖。注意該查詢的WHERE子句如何具有一個(gè)在物化視圖中沒有出現(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)化器并未如期改寫一個(gè)查詢，可以使用DBMS_MVIEW .EXPLAIN_REWRITE 過程來診斷該問題。這一特性出現(xiàn)在Oracle9i數(shù)據(jù)庫及以后的版本中。

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

到目前為止，我們所給出的所有示例都使用了與采購表中的所有數(shù)據(jù)對(duì)應(yīng)的物化視圖。Oracle9i數(shù)據(jù)庫具備在物化視圖僅有一個(gè)數(shù)據(jù)子集情況下改寫查詢的能力。例如，如果你只對(duì)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ù)，查詢就不會(huì)使用物化視圖。在Oracle數(shù)據(jù)庫10g中，已經(jīng)放松了這一限制，因此查詢改寫可以由物化視圖中獲得盡可能多的數(shù)據(jù)，并利用細(xì)目表獲得物化視圖中沒有的數(shù)據(jù)。和往常一樣，優(yōu)化器在做出執(zhí)行此操作的決定時(shí)考慮了有改寫和無改寫情況下的查詢成本。

例如，清單 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ā)生了變化會(huì)發(fā)生什么情況。查詢改寫仍將使用物化視圖嗎？答案決定于初始化參數(shù)QUERY_REWRITE_ INTEGRITY的設(shè)置。QUERY_REWRITE_INTEGRITY參數(shù)有三個(gè)取值： STALE_TOLERATED表示即使細(xì)目表中的數(shù)據(jù)已經(jīng)發(fā)生了變化，也仍然使用物化視圖。 TRUSTED 表示物化視圖未失效時(shí)才使用該視圖。但是，查詢改寫可以使用信任關(guān)系，如那些由維度對(duì)象或尚未生效的約束所聲明的關(guān)系。 ENFORCED（缺?。┍硎井?dāng)物化視圖保證能給出與使用細(xì)目表相同的結(jié)果時(shí)才使用它。使用這一參數(shù)意味著查詢改寫將不使用失效的物化視圖或信任關(guān)系。
正確的設(shè)置決定于應(yīng)用程序的數(shù)據(jù)需求。使用失效物化視圖的查詢改寫可能會(huì)產(chǎn)生與沒有使用查詢改寫時(shí)不同的結(jié)果。然而，如果使用細(xì)目數(shù)據(jù)，可能會(huì)因?yàn)轫憫?yīng)查詢需要處理的大量數(shù)據(jù)而使性能惡化。在一個(gè)數(shù)據(jù)倉庫中，通常使用TRUSTED完整級(jí)別，因?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ù)庫可以跟蹤物化視圖的哪一部分對(duì)應(yīng)于分區(qū)細(xì)目表的已更新部分。因此，如果查詢不需要已更新表的部分，那么該物化視圖仍然可以使用。

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

例如，由time_key對(duì)采購表進(jìn)行分區(qū)。清單 8中創(chuàng)建的物化視圖與前面使用的monthly_sales_mv 物化視圖幾乎完全相同，只是該物化視圖在采購表上包含了一個(gè)附加的DBMS_MVIEW.PMARKER函數(shù)。通過包含這一函數(shù)，當(dāng)更新采購表時(shí)該物化視圖允許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è)我們向采購表中增加一個(gè)2003年4月的新分區(qū)，而且一個(gè)用戶發(fā)出了一個(gè)請(qǐng)求2002年3月的數(shù)據(jù)的查詢，如清單 9所示。在此查詢中，我們并不關(guān)心2003年4月已更新的數(shù)據(jù)，所以將利用物化圖對(duì)其進(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中，將不會(huì)為使用物化視圖而對(duì)該查詢進(jìn)行改寫。但在Oracle數(shù)據(jù)庫10g中，可以使用MONTHLY_SALES_ PCT_MV和細(xì)目表的結(jié)合對(duì)該查詢進(jìn)行改寫。

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

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

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

代碼清單 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ì)上會(huì)比由細(xì)目表中獲得所有數(shù)據(jù)更快一些。

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

工具

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

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

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

讓我們來看看TUNE_MVIEW 如何能夠轉(zhuǎn)換你的物化視圖。假設(shè)我們有一個(gè)簡單的查詢，并將其傳遞給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)不同，但在可以使用原始物化視圖的地方，仍然可以使用該

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

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

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

這些建議是基于實(shí)際的工作負(fù)荷或根據(jù)你的模式所做出的假設(shè)提出的。當(dāng)提供了SQL語句的實(shí)際工作負(fù)荷時(shí)，將得到最好的結(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ū)В陲@示這些建議之前只需要完成三個(gè)步驟。讓我們來看看如何通常命令行界面使用SQL Access Advisor：

首先，創(chuàng)建一個(gè)包含，這一調(diào)優(yōu)過程所有信息的任務(wù)。然后，該任務(wù)將利用工作負(fù)荷信息來生成作為任務(wù)的一部分存儲(chǔ)的調(diào)優(yōu)建議。因此，整個(gè)過程是完全獨(dú)立的，而且允許各個(gè)任務(wù)稍有不同，以便人們能夠看到對(duì)配置進(jìn)行修改后的效果。在清單 14所示的示例中，是通過手工定義SQL語句對(duì)工作負(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ù)雜性，生成建議的時(shí)間可以由幾秒到幾分鐘不等。因此，盡管這個(gè)過程可以交互式運(yùn)行，但你可能希望考慮提交一個(gè)任務(wù)，這就是企業(yè)管理器中的向?qū)瓿傻墓ぷ鳌?br>
你可以通過查詢表USER_ADVISOR_RECOMMENDATIONS 來快速檢查是否有關(guān)于task_name的建議。在對(duì)本例進(jìn)行此操作時(shí)，我們會(huì)看到已經(jīng)提出了一個(gè)建議。


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



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

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


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é)論

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