無(wú)需改變SQL查詢(xún)就可以大幅提高查詢(xún)性能。
你是否為等待你的查詢(xún)返回結果而感到疲憊？你是否已經(jīng)為增強索引和調優(yōu)SQL而感到疲憊，但仍然不能提高查詢(xún)性能？那么，你是否已經(jīng)考慮創(chuàng )建物化視圖？有了物化視圖，那些過(guò)去需要數小時(shí)運行的報告可以在幾分鐘內完成。物化視圖可以包括聯(lián)接（join）和集合（aggregate），它提供了一種儲存預計算結果的方法。
在執行一個(gè)查詢(xún)時(shí)，優(yōu)化器會(huì )判定訪(fǎng)問(wèn)物化視圖或數據駐留的基礎表是否更快一些。如果優(yōu)化器判定查詢(xún)物化視圖是更好的解決方案，那么優(yōu)化器會(huì )在一個(gè)被稱(chēng)為“查詢(xún)改寫(xiě)”（query rewrite）的過(guò)程中改寫(xiě)SQL查詢(xún)。在這個(gè)過(guò)程中，不需要對任何SQL或應用程序代碼進(jìn)行修改，所以任何利用SQL訪(fǎng)問(wèn)數據庫的應用程序或特定查詢(xún)工具都可得益于使用物化視圖。當為計算結果而需要訪(fǎng)問(wèn)的數據數量遠大于結果（如集合）的大小時(shí)，最適合使用查詢(xún)改寫(xiě)，但是它也可被用于加速昂貴的聯(lián)接或規劃。
本文首先介紹了優(yōu)化器可以執行的查詢(xún)改寫(xiě)類(lèi)型。然后，它討論了幫助確定創(chuàng )建最佳物化視圖集的工具，使優(yōu)化器能夠改寫(xiě)多個(gè)查詢(xún)。利用這些工具創(chuàng )建的物化視圖在其基礎數據發(fā)生變化時(shí)還可以快速刷新。如果你不知道創(chuàng )建一個(gè)物化視圖、一個(gè)索引或同時(shí)創(chuàng )建兩者哪種更好，那么在Oracle數據庫10g中引入的SQL Access Advisor可以通過(guò)分析給定的工作負荷幫助你做出決定。
查詢(xún)改寫(xiě)類(lèi)型
可能有許多類(lèi)型的查詢(xún)改寫(xiě)；當物化視圖的定義查詢(xún)與查詢(xún)的文本完全匹配時(shí)，就發(fā)生最簡(jiǎn)單和最顯著(zhù)類(lèi)型的查詢(xún)改寫(xiě)。但是，當相同物化視圖可用于相應多個(gè)查詢(xún)時(shí)，就可以實(shí)現查詢(xún)改寫(xiě)的最大好處?，F在，我們將舉例說(shuō)明一些Oracle優(yōu)化器使用的規則，以確定它是否將使用物化視圖來(lái)響應。
對于本文中的示例，可以考慮將一個(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計算銷(xiāo)售總額和銷(xiāo)售總次數。注意：對于用于查詢(xún)改寫(xiě)的物化視圖，必須有ENABLE QUERY REWRITE子句。還有，初始化參數QUERY_REWRITE_ENABLED必須被設置為T(mén)RUE。
代碼清單 1：創(chuàng )建月銷(xiāo)售物化視圖
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;
集合計算
在本文的示例中，我們將說(shuō)明物化視圖的查詢(xún)并顯示由EXPLAIN PLAN得到的執行計劃。清單 2中的查詢(xún)要求按月和按產(chǎn)品的平均采購價(jià)格。優(yōu)化器可以使用物化視圖monthly_sales_mv，利用SUM和COUNT集合計算平均采購價(jià)格。這個(gè)示例說(shuō)明了一種叫做“集合計算”的技術(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ù)非常有用，因為它允許當物化視圖中沒(méi)有列時(shí)進(jìn)行查詢(xún)改寫(xiě)。清單 3中的查詢(xún)要求按月和按產(chǎn)品類(lèi)別的銷(xiāo)售總額，而該物化視圖中并沒(méi)有product.category列。然而，產(chǎn)品表的主鍵product_id列則位于物化視圖中。因此，優(yōu)化器可以將物化視圖與產(chǎn)品表聯(lián)接起來(lái)以得到產(chǎn)品類(lèi)別。
代碼清單 3：通過(guò)joinback獲得銷(xiāo)售總額
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)行查詢(xún)改寫(xiě)
在一個(gè)使用維度建模技巧設計的典型數據倉庫中，數據中存在著(zhù)著(zhù)名的“層次關(guān)系”。例如，在時(shí)間層次中，“天”積累成“月”，“月”又積累成“年”。在Oracle數據庫中，可以使用CREATE DIMENSION語(yǔ)句創(chuàng )建一個(gè)叫做“DIEMNSION”的對象，向優(yōu)化器聲明這種關(guān)系。維度對象是一個(gè)描述性對象，除了其元數據外，它不占用空間。使用DIMENSION對象聲明的關(guān)系據說(shuō)是可信的。Oracle不會(huì )驗證這一關(guān)系對于你的數據是否一定成立，它只是假設數據庫管理員已經(jīng)判定這些關(guān)系是正確的?？尚判畔⒌钠渌纠鞘褂肗OVALIDATE RELY標記的約束及注冊為物化視圖的先存表。
對于采用可信信息（包括維度）的查詢(xún)改寫(xiě)，初始化參數QUERY_ REWRITE_INTEGRITY必須被設置為T(mén)RUSTED，如下所示：
ALTER SESSION SET query_rewrite_integrity = TRUSTED;
例如，假設有一個(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);
現在，如果具有清單 4中要求按年的銷(xiāo)售額的查詢(xún)，你仍然可以使用monthly_sales_mv物化視圖，因為維度對象中的HIERARCHY子句告訴Oracle數據庫月銷(xiāo)售額可以積累成年銷(xiāo)售額。它利用前面描述的joinback技巧由物化視圖中的“月”列得到“年”列的值。
代碼清單 4：通過(guò)joinback和HIERARCHY獲得銷(xiāo)售總額
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開(kāi)始是一周中的哪一天。假設你希望得到每年1月份的銷(xiāo)售總額：你仍然可以使用清單 5中所示的monthly_sales_mv物化視圖。注意該查詢(xún)的WHERE子句如何具有一個(gè)在物化視圖中沒(méi)有出現的選擇條件。
代碼清單 5：通過(guò)joinback和ATTRIBUTE獲得銷(xiāo)售總額
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)化器并未如期改寫(xiě)一個(gè)查詢(xún)，可以使用DBMS_MVIEW .EXPLAIN_REWRITE 過(guò)程來(lái)診斷該問(wèn)題。這一特性出現在Oracle9i數據庫及以后的版本中。
過(guò)濾后的數據
到目前為止，我們所給出的所有示例都使用了與采購表中的所有數據對應的物化視圖。Oracle9i數據庫具備在物化視圖僅有一個(gè)數據子集情況下改寫(xiě)查詢(xún)的能力。例如，如果你只對1997年到2002年的銷(xiāo)售額感興趣，你可以將物化視圖修改如下：
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;
此物化視圖可用于響應要求從1997年至2002年數據的查詢(xún)，例如，清單 6中的查詢(xún)要求2000年的銷(xiāo)售額。
代碼清單 6：只查詢(xún)物化視圖
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數據庫中，如果物化視圖中沒(méi)有查詢(xún)所需要的全部數據，查詢(xún)就不會(huì )使用物化視圖。在Oracle數據庫10g中，已經(jīng)放松了這一限制，因此查詢(xún)改寫(xiě)可以由物化視圖中獲得盡可能多的數據，并利用細目表獲得物化視圖中沒(méi)有的數據。和往常一樣，優(yōu)化器在做出執行此操作的決定時(shí)考慮了有改寫(xiě)和無(wú)改寫(xiě)情況下的查詢(xún)成本。
例如，清單 7中的查詢(xún)要求2000年至2003年之間的月銷(xiāo)售額，它將使用從2000年至2002年的物化視圖，而只需要2003年的細目表。
代碼清單 7：查詢(xún)物化視圖和細目表
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ún)改寫(xiě)
你可能想知道如果細目表中的數據發(fā)生了變化會(huì )發(fā)生什么情況。查詢(xún)改寫(xiě)仍將使用物化視圖嗎？答案決定于初始化參數QUERY_REWRITE_ INTEGRITY的設置。QUERY_REWRITE_INTEGRITY參數有三個(gè)取值： STALE_TOLERATED表示即使細目表中的數據已經(jīng)發(fā)生了變化，也仍然使用物化視圖。 TRUSTED 表示物化視圖未失效時(shí)才使用該視圖。但是，查詢(xún)改寫(xiě)可以使用信任關(guān)系，如那些由維度對象或尚未生效的約束所聲明的關(guān)系。 ENFORCED（缺?。┍硎井斘锘晥D保證能給出與使用細目表相同的結果時(shí)才使用它。使用這一參數意味著(zhù)查詢(xún)改寫(xiě)將不使用失效的物化視圖或信任關(guān)系。
正確的設置決定于應用程序的數據需求。使用失效物化視圖的查詢(xún)改寫(xiě)可能會(huì )產(chǎn)生與沒(méi)有使用查詢(xún)改寫(xiě)時(shí)不同的結果。然而，如果使用細目數據，可能會(huì )因為響應查詢(xún)需要處理的大量數據而使性能惡化。在一個(gè)數據倉庫中，通常使用TRUSTED完整級別，因為這樣才可以保證你只使用那些具有最新數據的物化視圖；然而，被聲明為正確（可信任）的關(guān)系也可用于查詢(xún)改寫(xiě)。在大多數數據倉庫中，這些關(guān)系已經(jīng)在提取、轉換和加載（ETL）過(guò)程得到了驗證，因此不再需要進(jìn)行驗證。
分區變化跟蹤
在Oracle9i數據庫中，Oracle引入了分區變化跟蹤（PCT，Partition Change Tracking）。利用這一特性，Oracle9i數據庫可以跟蹤物化視圖的哪一部分對應于分區細目表的已更新部分。因此，如果查詢(xún)不需要已更新表的部分，那么該物化視圖仍然可以使用。
為了在物化視圖中跟蹤一個(gè)細目表的變化，必須對該表進(jìn)行分區，并且該物化視圖（在SELECT列表中）必須包括細目表的分區鍵或一個(gè)特殊函數：DBMS_MVIEW.PMARKER。此函數為細目表中的每個(gè)分區生成一個(gè)唯一的標識符。
例如，由time_key對采購表進(jìn)行分區。清單 8中創(chuàng )建的物化視圖與前面使用的monthly_sales_mv 物化視圖幾乎完全相同，只是該物化視圖在采購表上包含了一個(gè)附加的DBMS_MVIEW.PMARKER函數。通過(guò)包含這一函數，當更新采購表時(shí)該物化視圖允許PCT。注意：該物化視圖自身并不需要被分區。
代碼清單 8：具有DBMS_MVIEW.PMARKER函數的物化視圖
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;
現在，假設我們向采購表中增加一個(gè)2003年4月的新分區，而且一個(gè)用戶(hù)發(fā)出了一個(gè)請求2002年3月的數據的查詢(xún)，如清單 9所示。在此查詢(xún)中，我們并不關(guān)心2003年4月已更新的數據，所以將利用物化圖對其進(jìn)行改寫(xiě)，即使該物化視圖已經(jīng)失效也是如此。
代碼清單 9：使用失效的物化視圖進(jìn)行查詢(xún)改寫(xiě)
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
如果查詢(xún)要求從1月至4月的數據，在Orcale9i中，將不會(huì )為使用物化視圖而對該查詢(xún)進(jìn)行改寫(xiě)。但在Oracle數據庫10g中，可以使用MONTHLY_SALES_ PCT_MV和細目表的結合對該查詢(xún)進(jìn)行改寫(xiě)。
使用多個(gè)物化視圖進(jìn)行查詢(xún)改寫(xiě)
前面曾經(jīng)提到，在Oracle10g數據庫中，查詢(xún)改寫(xiě)已經(jīng)得到了增強，所以它可以使用一個(gè)物化視圖的部分數據以及細目表的其余數據來(lái)響應查詢(xún)。事實(shí)上，查詢(xún)改寫(xiě)可以結合使用兩個(gè)或多個(gè)物化視圖。例如，假設你為每5年的數據價(jià)值維護一個(gè)獨立的物化視圖： monthly_sales_1990-1994、 monthly_sales_1995_to_2000、 monthly_sales_2001_to_2005，等等。
那么，對于需要從1993年至2003年數據的清單 10中的查詢(xún)，查詢(xún)改寫(xiě)可以利用全部的三個(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中的查詢(xún)需要從1989年至1999年的數據，所以查詢(xún)改寫(xiě)可以使用物化視圖monthly_sales_1990_to_1994和monthly_sales_1995_to_2000，并由細目表獲得1989年的數據。這一過(guò)程實(shí)質(zhì)上會(huì )比由細目表中獲得所有數據更快一些。
Oracle10g數據庫在查詢(xún)改寫(xiě)方面有其他幾個(gè)改進(jìn)。在這些改進(jìn)中值得注意的是Oracle10g數據庫能夠更好地支持集合運算符（UNION、 UNIONALL等）增強了對多個(gè)表實(shí)例的查詢(xún)的增強，并提供了對分區變化跟蹤中的LIST 和RANGE-LIST分區類(lèi)型的支持。
工具
你可能會(huì )一邊閱讀本文，一邊自言自語(yǔ)：“嗯，我想我理解了你的意思，但是否有某些工具可以為我完成所有這些工作呢？”答案是肯定的。事實(shí)上，這些工具還相當多。
Oracle9i數據庫引用了explain_mview和explain_rewrite。應用編程接口（API）EXPLAIN_MVIEW采用一個(gè)物化視圖定義，并建議可使用何種類(lèi)型的分區變化跟蹤操作、是否可能進(jìn)行快速刷新，以及可以完成何種類(lèi)型的查詢(xún)改寫(xiě)。當一個(gè)查詢(xún)未被改寫(xiě)時(shí)，API EXPLAIN_REWRITE將告訴你SQL查詢(xún)?yōu)槭裁床皇褂貌樵?xún)改寫(xiě)。在兩種情況下，工具包都會(huì )告訴你問(wèn)題所在--例如，不能在一個(gè)特定列上進(jìn)行連接，但兩個(gè)包都不會(huì )準確告訴你如果解決這個(gè)問(wèn)題。這時(shí)，就可以使用包含在Oracle10g數據庫中的兩個(gè)新工具--TUNE_MVIEW和SQL Access Advisor來(lái)幫助你解決這個(gè)問(wèn)題。
TUNE_MVIEW API將告訴你如何編寫(xiě)物化視圖，使其可以快速刷新，并可以使用本文所描述的盡可能多的高級查詢(xún)改寫(xiě)類(lèi)型。TUNE_MVIEW API 的使用非常簡(jiǎn)單：只需要將你的物化視圖語(yǔ)句交給它，它就會(huì )判定該物化視圖的最佳形式。但是，如果你看到你的原始物化視圖已經(jīng)被轉換為多個(gè)新版本，也不要感到奇怪。
讓我們來(lái)看看TUNE_MVIEW 如何能夠轉換你的物化視圖。假設我們有一個(gè)簡(jiǎn)單的查詢(xún)，并將其傳遞給EXPLAIN_MVIEW，如清單 12所示，判斷該物化視圖在當前形式下是否可以快速刷新。
代碼清單 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
現在讓我們使用相同的查詢(xú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所示。盡管它看起
來(lái)與我們的原始物化視圖有點(diǎn)不同，但在可以使用原始物化視圖的地方，仍然可以使用該
物化視圖改寫(xiě)任何查詢(xún)，此外，還可以快速刷新。
你也可以生成一個(gè)腳本來(lái)執行這些建議，你可能希望做的僅有修改就是改變物化視圖的名稱(chēng)，以及指定物化視圖應當放在哪里的存儲語(yǔ)句和表空間。
現在，我們已經(jīng)有了一個(gè)物化視圖，但如果我們不知道創(chuàng )建什么物化視圖，那么應當怎么辦？這時(shí)，SQL Access Advisor可以幫助你，因為它會(huì )瀏覽你的系統，并它認為需要的索引和物化視圖。
這些建議是基于實(shí)際的工作負荷或根據你的模式所做出的假設提出的。當提供了SQL語(yǔ)句的實(shí)際工作負荷時(shí)，將得到最好的結果。這一工作負荷可由SQL緩存的當前內容、SQL 調優(yōu)集合（Tuning Set）、Oracle9i Summary Advisor工作負荷或用戶(hù)提供的工作負荷表（包含你已經(jīng)定義的SQL語(yǔ)句）獲得。
SQL Access Advisor既可以通過(guò)命令行API使用，也可以通過(guò)企業(yè)管理器（Enterprise Manager）的一部分--SQL Access Advisor向導使用。使用該向導，在顯示這些建議之前只需要完成三個(gè)步驟。讓我們來(lái)看看如何通常命令行界面使用SQL Access Advisor：
首先，創(chuàng )建一個(gè)包含，這一調優(yōu)過(guò)程所有信息的任務(wù)。然后，該任務(wù)將利用工作負荷信息來(lái)生成作為任務(wù)的一部分存儲的調優(yōu)建議。因此，整個(gè)過(guò)程是完全獨立的，而且允許各個(gè)任務(wù)稍有不同，以便人們能夠看到對配置進(jìn)行修改后的效果。在清單 14所示的示例中，是通過(guò)手工定義SQL語(yǔ)句對工作負荷進(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;/
一旦定義了工作負荷和任務(wù)，就可以生成如下所示的建議，該建議使用了EXECUTE_TASK 并指定了所創(chuàng )建任務(wù)的名字--Task_mag：
execute dbms_advisor.execute_task (‘Task_mag‘);
根據工作負荷的復雜性，生成建議的時(shí)間可以由幾秒到幾分鐘不等。因此，盡管這個(gè)過(guò)程可以交互式運行，但你可能希望考慮提交一個(gè)任務(wù)，這就是企業(yè)管理器中的向導所要完成的工作。
你可以通過(guò)查詢(xún)表USER_ADVISOR_RECOMMENDATIONS 來(lái)快速檢查是否有關(guān)于task_name的建議。在對本例進(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è)建議可以導致多個(gè)操作。對于此示例，SQL Access Advisor建議創(chuàng )建物化視圖日志、一個(gè)CREATE MATERIALIZED VIEW，以及一個(gè)用來(lái)分析物化視圖的調用（受版面限制，這里未給出）。
盡管你可以查詢(xún)各種目錄視圖來(lái)查看這些操作，但查看它們的最簡(jiǎn)單方法就是生成一個(gè)腳本，如下所示：
executedbms_advisor.create_file(dbms_advisor.get_task_script(‘Task_mag‘), ‘ADVISOR_RESULTS‘, ‘mag_example.sql‘);
在清單 15中，你可以看到該腳本的一段摘錄，顯示了為我們的查詢(xún)所建立的物化視圖。
代碼清單 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;...
結論
通過(guò)使用查詢(xún)改寫(xiě)，你可以利用幾個(gè)物化視圖顯著(zhù)改進(jìn)許多查詢(xún)的性能，從而減少了保持物化視圖與基礎細目數據同步所需要的磁盤(pán)空間占用與刷新時(shí)間。
