無(wú)需改變sql查詢就可以大幅提高查詢性能。
你是否為等待你的查詢返回結(jié)果而感到疲憊？你是否已經(jīng)為增強(qiáng)索引和調(diào)優(yōu)sql而感到疲憊，但仍然不能提高查詢性能？那么，你是否已經(jīng)考慮創(chuàng)建物化視圖？有了物化視圖，那些過(guò)去需要數(shù)小時(shí)運(yùn)行的報(bào)告可以在幾分鐘內(nèi)完成。物化視圖可以包括聯(lián)接（join）和集合（aggregate），它提供了一種儲(chǔ)存預(yù)計(jì)算結(jié)果的方法。
在執(zhí)行一個(gè)查詢時(shí)，優(yōu)化器會(huì)判定訪問(wèn)物化視圖或數(shù)據(jù)駐留的基礎(chǔ)表是否更快一些。如果優(yōu)化器判定查詢物化視圖是更好的解決方案，那么優(yōu)化器會(huì)在一個(gè)被稱為“查詢改寫(xiě)”（query rewrite）的過(guò)程中改寫(xiě)sql查詢。在這個(gè)過(guò)程中，不需要對(duì)任何sql或應(yīng)用程序代碼進(jìn)行修改，所以任何利用sql訪問(wèn)數(shù)據(jù)庫(kù)的應(yīng)用程序或特定查詢工具都可得益于使用物化視圖。當(dāng)為計(jì)算結(jié)果而需要訪問(wèn)的數(shù)據(jù)數(shù)量遠(yuǎn)大于結(jié)果（如集合）的大小時(shí)，最適合使用查詢改寫(xiě)，但是它也可被用于加速昂貴的聯(lián)接或規(guī)劃。
本文首先介紹了優(yōu)化器可以執(zhí)行的查詢改寫(xiě)類(lèi)型。然后，它討論了幫助確定創(chuàng)建最佳物化視圖集的工具，使優(yōu)化器能夠改寫(xiě)多個(gè)查詢。利用這些工具創(chuàng)建的物化視圖在其基礎(chǔ)數(shù)據(jù)發(fā)生變化時(shí)還可以快速刷新。如果你不知道創(chuàng)建一個(gè)物化視圖、一個(gè)索引或同時(shí)創(chuàng)建兩者哪種更好，那么在oracle數(shù)據(jù)庫(kù)10g中引入的sql access advisor可以通過(guò)分析給定的工作負(fù)荷幫助你做出決定。
查詢改寫(xiě)類(lèi)型
可能有許多類(lèi)型的查詢改寫(xiě)；當(dāng)物化視圖的定義查詢與查詢的文本完全匹配時(shí)，就發(fā)生最簡(jiǎn)單和最顯著類(lèi)型的查詢改寫(xiě)。但是，當(dāng)相同物化視圖可用于相應(yīng)多個(gè)查詢時(shí)，就可以實(shí)現(xiàn)查詢改寫(xiě)的最大好處?，F(xiàn)在，我們將舉例說(shuō)明一些oracle優(yōu)化器使用的規(guī)則，以確定它是否將使用物化視圖來(lái)響應(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ì)算銷(xiāo)售總額和銷(xiāo)售總次數(shù)。注意：對(duì)于用于查詢改寫(xiě)的物化視圖，必須有enable query rewrite子句。還有，初始化參數(shù)query_rewrite_enabled必須被設(shè)置為true。
代碼清單 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;
集合計(jì)算
在本文的示例中，我們將說(shuō)明物化視圖的查詢并顯示由explain plan得到的執(zhí)行計(jì)劃。清單 2中的查詢要求按月和按產(chǎn)品的平均采購(gòu)價(jià)格。優(yōu)化器可以使用物化視圖monthly_sales_mv，利用sum和count集合計(jì)算平均采購(gòu)價(jià)格。這個(gè)示例說(shuō)明了一種叫做“集合計(jì)算”的技術(shù)。
代碼清單 2：獲得平均(avg)采購(gòu)價(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)物化視圖中沒(méi)有列時(shí)進(jìn)行查詢改寫(xiě)。清單 3中的查詢要求按月和按產(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)行查詢改寫(xiě)
在一個(gè)使用維度建模技巧設(shè)計(jì)的典型數(shù)據(jù)倉(cāng)庫(kù)中，數(shù)據(jù)中存在著著名的“層次關(guān)系”。例如，在時(shí)間層次中，“天”積累成“月”，“月”又積累成“年”。在oracle數(shù)據(jù)庫(kù)中，可以使用create dimension語(yǔ)句創(chuàng)建一個(gè)叫做“diemnsion”的對(duì)象，向優(yōu)化器聲明這種關(guān)系。維度對(duì)象是一個(gè)描述性對(duì)象，除了其元數(shù)據(jù)外，它不占用空間。使用dimension對(duì)象聲明的關(guān)系據(jù)說(shuō)是可信的。oracle不會(huì)驗(yàn)證這一關(guān)系對(duì)于你的數(shù)據(jù)是否一定成立，它只是假設(shè)數(shù)據(jù)庫(kù)管理員已經(jīng)判定這些關(guān)系是正確的?？尚判畔⒌钠渌纠鞘褂胣ovalidate rely標(biāo)記的約束及注冊(cè)為物化視圖的先存表。
對(duì)于采用可信信息（包括維度）的查詢改寫(xiě)，初始化參數(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中要求按年的銷(xiāo)售額的查詢，你仍然可以使用monthly_sales_mv物化視圖，因?yàn)榫S度對(duì)象中的hierarchy子句告訴oracle數(shù)據(jù)庫(kù)月銷(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子句指明了一對(duì)一關(guān)系。例如，你可以判定從time_key開(kāi)始是一周中的哪一天。假設(shè)你希望得到每年1月份的銷(xiāo)售總額：你仍然可以使用清單 5中所示的monthly_sales_mv物化視圖。注意該查詢的where子句如何具有一個(gè)在物化視圖中沒(méi)有出現(xiàn)的選擇條件。
代碼清單 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è)查詢，可以使用dbms_mview .explain_rewrite 過(guò)程來(lái)診斷該問(wèn)題。這一特性出現(xiàn)在oracle9i數(shù)據(jù)庫(kù)及以后的版本中。
過(guò)濾后的數(shù)據(jù)
到目前為止，我們所給出的所有示例都使用了與采購(gòu)表中的所有數(shù)據(jù)對(duì)應(yīng)的物化視圖。oracle9i數(shù)據(jù)庫(kù)具備在物化視圖僅有一個(gè)數(shù)據(jù)子集情況下改寫(xiě)查詢的能力。例如，如果你只對(duì)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;
此物化視圖可用于響應(yīng)要求從1997年至2002年數(shù)據(jù)的查詢，例如，清單 6中的查詢要求2000年的銷(xiāo)售額。
代碼清單 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ù)庫(kù)中，如果物化視圖中沒(méi)有查詢所需要的全部數(shù)據(jù)，查詢就不會(huì)使用物化視圖。在oracle數(shù)據(jù)庫(kù)10g中，已經(jīng)放松了這一限制，因此查詢改寫(xiě)可以由物化視圖中獲得盡可能多的數(shù)據(jù)，并利用細(xì)目表獲得物化視圖中沒(méi)有的數(shù)據(jù)。和往常一樣，優(yōu)化器在做出執(zhí)行此操作的決定時(shí)考慮了有改寫(xiě)和無(wú)改寫(xiě)情況下的查詢成本。
例如，清單 7中的查詢要求2000年至2003年之間的月銷(xiāo)售額，它將使用從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)行查詢改寫(xiě)
你可能想知道如果細(xì)目表中的數(shù)據(jù)發(fā)生了變化會(huì)發(fā)生什么情況。查詢改寫(xiě)仍將使用物化視圖嗎？答案決定于初始化參數(shù)query_rewrite_ integrity的設(shè)置。query_rewrite_integrity參數(shù)有三個(gè)取值： stale_tolerated表示即使細(xì)目表中的數(shù)據(jù)已經(jīng)發(fā)生了變化，也仍然使用物化視圖。 trusted 表示物化視圖未失效時(shí)才使用該視圖。但是，查詢改寫(xiě)可以使用信任關(guān)系，如那些由維度對(duì)象或尚未生效的約束所聲明的關(guān)系。 enforced（缺?。┍硎井?dāng)物化視圖保證能給出與使用細(xì)目表相同的結(jié)果時(shí)才使用它。使用這一參數(shù)意味著查詢改寫(xiě)將不使用失效的物化視圖或信任關(guān)系。
正確的設(shè)置決定于應(yīng)用程序的數(shù)據(jù)需求。使用失效物化視圖的查詢改寫(xiě)可能會(huì)產(chǎn)生與沒(méi)有使用查詢改寫(xiě)時(shí)不同的結(jié)果。然而，如果使用細(xì)目數(shù)據(jù)，可能會(huì)因?yàn)轫憫?yīng)查詢需要處理的大量數(shù)據(jù)而使性能惡化。在一個(gè)數(shù)據(jù)倉(cāng)庫(kù)中，通常使用trusted完整級(jí)別，因?yàn)檫@樣才可以保證你只使用那些具有最新數(shù)據(jù)的物化視圖；然而，被聲明為正確（可信任）的關(guān)系也可用于查詢改寫(xiě)。在大多數(shù)數(shù)據(jù)倉(cāng)庫(kù)中，這些關(guān)系已經(jīng)在提取、轉(zhuǎn)換和加載（etl）過(guò)程得到了驗(yàn)證，因此不再需要進(jìn)行驗(yàn)證。
分區(qū)變化跟蹤
在oracle9i數(shù)據(jù)庫(kù)中，oracle引入了分區(qū)變化跟蹤（pct，partition change tracking）。利用這一特性，oracle9i數(shù)據(jù)庫(kù)可以跟蹤物化視圖的哪一部分對(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ì)采購(gòu)表進(jìn)行分區(qū)。清單 8中創(chuàng)建的物化視圖與前面使用的monthly_sales_mv 物化視圖幾乎完全相同，只是該物化視圖在采購(gòu)表上包含了一個(gè)附加的dbms_mview.pmarker函數(shù)。通過(guò)包含這一函數(shù)，當(dāng)更新采購(gòu)表時(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òu)表中增加一個(gè)2003年4月的新分區(qū)，而且一個(gè)用戶發(fā)出了一個(gè)請(qǐng)求2002年3月的數(shù)據(jù)的查詢，如清單 9所示。在此查詢中，我們并不關(guān)心2003年4月已更新的數(shù)據(jù)，所以將利用物化圖對(duì)其進(jìn)行改寫(xiě)，即使該物化視圖已經(jīng)失效也是如此。
代碼清單 9：使用失效的物化視圖進(jì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
如果查詢要求從1月至4月的數(shù)據(jù)，在orcale9i中，將不會(huì)為使用物化視圖而對(duì)該查詢進(jìn)行改寫(xiě)。但在oracle數(shù)據(jù)庫(kù)10g中，可以使用monthly_sales_ pct_mv和細(xì)目表的結(jié)合對(duì)該查詢進(jìn)行改寫(xiě)。
使用多個(gè)物化視圖進(jìn)行查詢改寫(xiě)
前面曾經(jīng)提到，在oracle10g數(shù)據(jù)庫(kù)中，查詢改寫(xiě)已經(jīng)得到了增強(qiáng)，所以它可以使用一個(gè)物化視圖的部分?jǐn)?shù)據(jù)以及細(xì)目表的其余數(shù)據(jù)來(lái)響應(yīng)查詢。事實(shí)上，查詢改寫(xiě)可以結(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中的查詢，查詢改寫(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中的查詢需要從1989年至1999年的數(shù)據(jù)，所以查詢改寫(xiě)可以使用物化視圖monthly_sales_1990_to_1994和monthly_sales_1995_to_2000，并由細(xì)目表獲得1989年的數(shù)據(jù)。這一過(guò)程實(shí)質(zhì)上會(huì)比由細(xì)目表中獲得所有數(shù)據(jù)更快一些。
oracle10g數(shù)據(jù)庫(kù)在查詢改寫(xiě)方面有其他幾個(gè)改進(jìn)。在這些改進(jìn)中值得注意的是oracle10g數(shù)據(jù)庫(kù)能夠更好地支持集合運(yùn)算符（union、 unionall等）增強(qiáng)了對(duì)多個(gè)表實(shí)例的查詢的增強(qiáng)，并提供了對(duì)分區(qū)變化跟蹤中的list 和range-list分區(qū)類(lèi)型的支持。
工具
你可能會(huì)一邊閱讀本文，一邊自言自語(yǔ)：“嗯，我想我理解了你的意思，但是否有某些工具可以為我完成所有這些工作呢？”答案是肯定的。事實(shí)上，這些工具還相當(dāng)多。
oracle9i數(shù)據(jù)庫(kù)引用了explain_mview和explain_rewrite。應(yīng)用編程接口（api）explain_mview采用一個(gè)物化視圖定義，并建議可使用何種類(lèi)型的分區(qū)變化跟蹤操作、是否可能進(jìn)行快速刷新，以及可以完成何種類(lèi)型的查詢改寫(xiě)。當(dāng)一個(gè)查詢未被改寫(xiě)時(shí)，api explain_rewrite將告訴你sql查詢?yōu)槭裁床皇褂貌樵兏膶?xiě)。在兩種情況下，工具包都會(huì)告訴你問(wèn)題所在--例如，不能在一個(gè)特定列上進(jìn)行連接，但兩個(gè)包都不會(huì)準(zhǔn)確告訴你如果解決這個(gè)問(wèn)題。這時(shí)，就可以使用包含在oracle10g數(shù)據(jù)庫(kù)中的兩個(gè)新工具--tune_mview和sql access advisor來(lái)幫助你解決這個(gè)問(wèn)題。
tune_mview api將告訴你如何編寫(xiě)物化視圖，使其可以快速刷新，并可以使用本文所描述的盡可能多的高級(jí)查詢改寫(xiě)類(lèi)型。tune_mview api 的使用非常簡(jiǎn)單：只需要將你的物化視圖語(yǔ)句交給它，它就會(huì)判定該物化視圖的最佳形式。但是，如果你看到你的原始物化視圖已經(jīng)被轉(zhuǎn)換為多個(gè)新版本，也不要感到奇怪。
讓我們來(lái)看看tune_mview 如何能夠轉(zhuǎn)換你的物化視圖。假設(shè)我們有一個(gè)簡(jiǎn)單的查詢，并將其傳遞給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所示。盡管它看起
來(lái)與我們的原始物化視圖有點(diǎn)不同，但在可以使用原始物化視圖的地方，仍然可以使用該
物化視圖改寫(xiě)任何查詢，此外，還可以快速刷新。
你也可以生成一個(gè)腳本來(lái)執(zhí)行這些建議，你可能希望做的僅有修改就是改變物化視圖的名稱，以及指定物化視圖應(yīng)當(dāng)放在哪里的存儲(chǔ)語(yǔ)句和表空間。
現(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語(yǔ)句的實(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語(yǔ)句）獲得。
sql access advisor既可以通過(guò)命令行api使用，也可以通過(guò)企業(yè)管理器（enterprise manager）的一部分--sql access advisor向?qū)褂?。使用該向?qū)В陲@示這些建議之前只需要完成三個(gè)步驟。讓我們來(lái)看看如何通常命令行界面使用sql access advisor：
首先，創(chuàng)建一個(gè)包含，這一調(diào)優(yōu)過(guò)程所有信息的任務(wù)。然后，該任務(wù)將利用工作負(fù)荷信息來(lái)生成作為任務(wù)的一部分存儲(chǔ)的調(diào)優(yōu)建議。因此，整個(gè)過(guò)程是完全獨(dú)立的，而且允許各個(gè)任務(wù)稍有不同，以便人們能夠看到對(duì)配置進(jìn)行修改后的效果。在清單 14所示的示例中，是通過(guò)手工定義sql語(yǔ)句對(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','action', 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è)過(guò)程可以交互式運(yùn)行，但你可能希望考慮提交一個(gè)任務(wù)，這就是企業(yè)管理器中的向?qū)瓿傻墓ぷ鳌?br>
你可以通過(guò)查詢表user_advisor_recommendations 來(lái)快速檢查是否有關(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è)用來(lái)分析物化視圖的調(diào)用（受版面限制，這里未給出）。
盡管你可以查詢各種目錄視圖來(lái)查看這些操作，但查看它們的最簡(jiǎn)單方法就是生成一個(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é)論
通過(guò)使用查詢改寫(xiě)，你可以利用幾個(gè)物化視圖顯著改進(jìn)許多查詢的性能，從而減少了保持物化視圖與基礎(chǔ)細(xì)目數(shù)據(jù)同步所需要的磁盤(pán)空間占用與刷新時(shí)間。
