免费的av在线,男人天堂网站在线,日韩不卡一二区

導讀數據庫，簡而言之可視為電子化的文件柜——存儲電子文件的處所，用戶可以對文件中的數據進行新增、截取、更新、刪除等操作。所謂“數據庫”是以一定方式儲存在一起、能與多個用戶共享、具有盡可能小的冗余度、與應...

數據庫，簡而言之可視為電子化的文件柜——存儲電子文件的處所，用戶可以對文件中的數據進行新增、截取、更新、刪除等操作。所謂“數據庫”是以一定方式儲存在一起、能與多個用戶共享、具有盡可能小的冗余度、與應用程序彼此獨立的數據集合。

無需改變SQL查詢就可以大幅提高查詢性能。
你是否為等待你的查詢返回結果而感到疲憊？你是否已經為增強索引和調優SQL而感到疲憊，但仍然不能提高查詢性能？那么，你是否已經考慮創建物化視圖？有了物化視圖，那些過去需要數小時運行的報告可以在幾分鐘內完成。物化視圖可以包括聯接（join）和集合（aggregate），它提供了一種儲存預計算結果的方法。
在執行一個查詢時，優化器會判定訪問物化視圖或數據駐留的基礎表是否更快一些。如果優化器判定查詢物化視圖是更好的解決方案，那么優化器會在一個被稱為“查詢改寫”（query rewrite）的過程中改寫SQL查詢。在這個過程中，不需要對任何SQL或應用程序代碼進行修改，所以任何利用SQL訪問數據庫的應用程序或特定查詢工具都可得益于使用物化視圖。當為計算結果而需要訪問的數據數量遠大于結果（如集合）的大小時，最適合使用查詢改寫，但是它也可被用于加速昂貴的聯接或規劃。
本文首先介紹了優化器可以執行的查詢改寫類型。然后，它討論了幫助確定創建最佳物化視圖集的工具，使優化器能夠改寫多個查詢。利用這些工具創建的物化視圖在其基礎數據發生變化時還可以快速刷新。如果你不知道創建一個物化視圖、一個索引或同時創建兩者哪種更好，那么在Oracle數據庫10g中引入的SQL Access Advisor可以通過分析給定的工作負荷幫助你做出決定。
查詢改寫類型
可能有許多類型的查詢改寫；當物化視圖的定義查詢與查詢的文本完全匹配時，就發生最簡單和最顯著類型的查詢改寫。但是，當相同物化視圖可用于相應多個查詢時，就可以實現查詢改寫的最大好處�，F在，我們將舉例說明一些Oracle優化器使用的規則，以確定它是否將使用物化視圖來響應。
對于本文中的示例，可以考慮將一個星形模式中的PURCHASES表看作事實表（fact table），其范圍由time_key劃分。維度表（dimension table）--TIME、PRODUCT和CUSTOMERS--包含主鍵 time_key、product_id和cust_id。在PURCHASES表中有引用各個維度表的外鍵約束。
考慮一下清單 1中所創建的物化視圖，該視圖按月按product_id計算銷售總額和銷售總次數。注意：對于用于查詢改寫的物化視圖，必須有ENABLE QUERY REWRITE子句。還有，初始化參數QUERY_REWRITE_ENABLED必須被設置為TRUE。
代碼清單 1：創建月銷售物化視圖

CREATE MATERIALIZED VIEW monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales,
   COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
   ps.product_id = p.product_id
GROUP BY t.month, p.product_id;
集合計算
在本文的示例中，我們將說明物化視圖的查詢并顯示由EXPLAIN PLAN得到的執行計劃。清單 2中的查詢要求按月和按產品的平均采購價格。優化器可以使用物化視圖monthly_sales_mv，利用SUM和COUNT集合計算平均采購價格。這個示例說明了一種叫做“集合計算”的技術。
代碼清單 2：獲得平均(AVG)采購價格

SELECT t.month, p.product_id, AVG(ps.purchase_price) as avg_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
   ps.product_id = p.product_id
GROUP BY t.month, p.product_id;

   Id    Operation                      Name
________________________________________________
SELECT STATEMENT
  MAT_VIEW REWRITE ACCESS FULL           MONTHLY_SALES_MV

Joinback
joinback技術非常有用，因為它允許當物化視圖中沒有列時進行查詢改寫。清單 3中的查詢要求按月和按產品類別的銷售總額，而該物化視圖中并沒有product.category列。然而，產品表的主鍵product_id列則位于物化視圖中。因此，優化器可以將物化視圖與產品表聯接起來以得到產品類別。
代碼清單 3：通過joinback獲得銷售總額

SELECT t.month, p.category, SUM(ps.purchase_price) as sum_of_sales

FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP 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

使用維度進行查詢改寫
在一個使用維度建模技巧設計的典型數據倉庫中，數據中存在著著名的“層次關系”。例如，在時間層次中，“天”積累成“月”，“月”又積累成“年”。在Oracle數據庫中，可以使用CREATE DIMENSION語句創建一個叫做“DIEMNSION”的對象，向優化器聲明這種關系。維度對象是一個描述性對象，除了其元數據外，它不占用空間。使用DIMENSION對象聲明的關系據說是可信的。Oracle不會驗證這一關系對于你的數據是否一定成立，它只是假設數據庫管理員已經判定這些關系是正確的�？尚判畔⒌钠渌纠鞘褂肗OVALIDATE RELY標記的約束及注冊為物化視圖的先存表。
對于采用可信信息（包括維度）的查詢改寫，初始化參數QUERY_ REWRITE_INTEGRITY必須被設置為TRUSTED，如下所示：

ALTER SESSION SET query_rewrite_integrity = TRUSTED;

例如，假設有一個時間維度，其聲明如下：
[page_break]CREATE DIMENSION time_dim
LEVEL time_key IS time.time_key
LEVEL month IS time.month
LEVEL quarter IS time.quarter
LEVEL year IS time.year
HIERARCHY 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中要求按年的銷售額的查詢，你仍然可以使用monthly_sales_mv物化視圖，因為維度對象中的HIERARCHY子句告訴Oracle數據庫月銷售額可以積累成年銷售額。它利用前面描述的joinback技巧由物化視圖中的“月”列得到“年”列的值。
代碼清單 4：通過joinback和HIERARCHY獲得銷售總額

SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
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
維度的ATTRIBUTE子句指明了一對一關系。例如，你可以判定從time_key開始是一周中的哪一天。假設你希望得到每年1月份的銷售總額：你仍然可以使用清單 5中所示的monthly_sales_mv物化視圖。注意該查詢的WHERE子句如何具有一個在物化視圖中沒有出現的選擇條件。
代碼清單 5：通過joinback和ATTRIBUTE獲得銷售總額

SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE 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

如果優化器并未如期改寫一個查詢，可以使用DBMS_MVIEW .EXPLAIN_REWRITE 過程來診斷該問題。這一特性出現在Oracle9i數據庫及以后的版本中。
過濾后的數據
到目前為止，我們所給出的所有示例都使用了與采購表中的所有數據對應的物化視圖。Oracle9i數據庫具備在物化視圖僅有一個數據子集情況下改寫查詢的能力。例如，如果你只對1997年到2002年的銷售額感興趣，你可以將物化視圖修改如下：

CREATE MATERIALIZED VIEW five_yr_monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id,
       SUM(ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year between 1997 and 2002
GROUP BY t.month, p.product_id;

此物化視圖可用于響應要求從1997年至2002年數據的查詢，例如，清單 6中的查詢要求2000年的銷售額。
代碼清單 6：只查詢物化視圖
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year = 2000
GROUP 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數據庫中，如果物化視圖中沒有查詢所需要的全部數據，查詢就不會使用物化視圖。在Oracle數據庫10g中，已經放松了這一限制，因此查詢改寫可以由物化視圖中獲得盡可能多的數據，并利用細目表獲得物化視圖中沒有的數據。和往常一樣，優化器在做出執行此操作的決定時考慮了有改寫和無改寫情況下的查詢成本。
例如，清單 7中的查詢要求2000年至2003年之間的月銷售額，它將使用從2000年至2002年的物化視圖，而只需要2003年的細目表。
代碼清單 7：查詢物化視圖和細目表

SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year BETWEEN 2000 and 2003
GROUP 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

使用失效的物化視圖進行查詢改寫
你可能想知道如果細目表中的數據發生了變化會發生什么情況。查詢改寫仍將使用物化視圖嗎？答案決定于初始化參數QUERY_REWRITE_ INTEGRITY的設置。QUERY_REWRITE_INTEGRITY參數有三個取值：
STALE_TOLERATED表示即使細目表中的數據已經發生了變化，也仍然使用物化視圖。
TRUSTED 表示物化視圖未失效時才使用該視圖。但是，查詢改寫可以使用信任關系，如那些由維度對象或尚未生效的約束所聲明的關系。
ENFORCED（缺�。┍硎井斘锘晥D保證能給出與使用細目表相同的結果時才使用它。使用這一參數意味著查詢改寫將不使用失效的物化視圖或信任關系。
正確的設置決定于應用程序的數據需求。使用失效物化視圖的查詢改寫可能會產生與沒有使用查詢改寫時不同的結果。然而，如果使用細目數據，可能會因為響應查詢需要處理的大量數據而使性能惡化。在一個數據倉庫中，通常使用TRUSTED完整級別，因為這樣才可以保證你只使用那些具有最新數據的物化視圖；然而，被聲明為正確（可信任）的關系也可用于查詢改寫。在大多數數據倉庫中，這些關系已經在提取、轉換和加載（ETL）過程得到了驗證，因此不再需要進行驗證。
分區變化跟蹤
在Oracle9i數據庫中，Oracle引入了分區變化跟蹤（PCT，Partition Change Tracking）。利用這一特性，Oracle9i數據庫可以跟蹤物化視圖的哪一部分對應于分區細目表的已更新部分。因此，如果查詢不需要已更新表的部分，那么該物化視圖仍然可以使用。
為了在物化視圖中跟蹤一個細目表的變化，必須對該表進行分區，并且該物化視圖（在SELECT列表中）必須包括細目表的分區鍵或一個特殊函數：DBMS_MVIEW.PMARKER。此函數為細目表中的每個分區生成一個唯一的標識符。
例如，由time_key對采購表進行分區。清單 8中創建的物化視圖與前面使用的monthly_sales_mv 物化視圖幾乎完全相同，只是該物化視圖在采購表上包含了一個附加的DBMS_MVIEW.PMARKER函數。通過包含這一函數，當更新采購表時該物化視圖允許PCT。注意：該物化視圖自身并不需要被分區。
代碼清單 8：具有DBMS_MVIEW.PMARKER函數的物化視圖
[page_break]  使用查詢改寫提高查詢性能

［作者：chensheng913    轉貼自：csdn    點擊數：1    更新時間：2005-11-9  ］
減小字體增大字體
無需改變SQL查詢就可以大幅提高查詢性能。
你是否為等待你的查詢返回結果而感到疲憊？你是否已經為增強索引和調優SQL而感到疲憊，但仍然不能提高查詢性能？那么，你是否已經考慮創建物化視圖？有了物化視圖，那些過去需要數小時運行的報告可以在幾分鐘內完成。物化視圖可以包括聯接（join）和集合（aggregate），它提供了一種儲存預計算結果的方法。
在執行一個查詢時，優化器會判定訪問物化視圖或數據駐留的基礎表是否更快一些。如果優化器判定查詢物化視圖是更好的解決方案，那么優化器會在一個被稱為“查詢改寫”（query rewrite）的過程中改寫SQL查詢。在這個過程中，不需要對任何SQL或應用程序代碼進行修改，所以任何利用SQL訪問數據庫的應用程序或特定查詢工具都可得益于使用物化視圖。當為計算結果而需要訪問的數據數量遠大于結果（如集合）的大小時，最適合使用查詢改寫，但是它也可被用于加速昂貴的聯接或規劃。
本文首先介紹了優化器可以執行的查詢改寫類型。然后，它討論了幫助確定創建最佳物化視圖集的工具，使優化器能夠改寫多個查詢。利用這些工具創建的物化視圖在其基礎數據發生變化時還可以快速刷新。如果你不知道創建一個物化視圖、一個索引或同時創建兩者哪種更好，那么在Oracle數據庫10g中引入的SQL Access Advisor可以通過分析給定的工作負荷幫助你做出決定。
查詢改寫類型
可能有許多類型的查詢改寫；當物化視圖的定義查詢與查詢的文本完全匹配時，就發生最簡單和最顯著類型的查詢改寫。但是，當相同物化視圖可用于相應多個查詢時，就可以實現查詢改寫的最大好處。現在，我們將舉例說明一些Oracle優化器使用的規則，以確定它是否將使用物化視圖來響應。
對于本文中的示例，可以考慮將一個星形模式中的PURCHASES表看作事實表（fact table），其范圍由time_key劃分。維度表（dimension table）--TIME、PRODUCT和CUSTOMERS--包含主鍵 time_key、product_id和cust_id。在PURCHASES表中有引用各個維度表的外鍵約束。
考慮一下清單 1中所創建的物化視圖，該視圖按月按product_id計算銷售總額和銷售總次數。注意：對于用于查詢改寫的物化視圖，必須有ENABLE QUERY REWRITE子句。還有，初始化參數QUERY_REWRITE_ENABLED必須被設置為TRUE。
代碼清單 1：創建月銷售物化視圖

CREATE MATERIALIZED VIEW monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales,
   COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
   ps.product_id = p.product_id
GROUP BY t.month, p.product_id;
集合計算
在本文的示例中，我們將說明物化視圖的查詢并顯示由EXPLAIN PLAN得到的執行計劃。清單 2中的查詢要求按月和按產品的平均采購價格。優化器可以使用物化視圖monthly_sales_mv，利用SUM和COUNT集合計算平均采購價格。這個示例說明了一種叫做“集合計算”的技術。
代碼清單 2：獲得平均(AVG)采購價格

SELECT t.month, p.product_id, AVG(ps.purchase_price) as avg_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
   ps.product_id = p.product_id
GROUP BY t.month, p.product_id;

   Id    Operation                      Name
________________________________________________
SELECT STATEMENT
  MAT_VIEW REWRITE ACCESS FULL           MONTHLY_SALES_MV

Joinback
joinback技術非常有用，因為它允許當物化視圖中沒有列時進行查詢改寫。清單 3中的查詢要求按月和按產品類別的銷售總額，而該物化視圖中并沒有product.category列。然而，產品表的主鍵product_id列則位于物化視圖中。因此，優化器可以將物化視圖與產品表聯接起來以得到產品類別。
代碼清單 3：通過joinback獲得銷售總額

SELECT t.month, p.category, SUM(ps.purchase_price) as sum_of_sales

FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP 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

使用維度進行查詢改寫
在一個使用維度建模技巧設計的典型數據倉庫中，數據中存在著著名的“層次關系”。例如，在時間層次中，“天”積累成“月”，“月”又積累成“年”。在Oracle數據庫中，可以使用CREATE DIMENSION語句創建一個叫做“DIEMNSION”的對象，向優化器聲明這種關系。維度對象是一個描述性對象，除了其元數據外，它不占用空間。使用DIMENSION對象聲明的關系據說是可信的。Oracle不會驗證這一關系對于你的數據是否一定成立，它只是假設數據庫管理員已經判定這些關系是正確的。可信信息的其他示例是使用NOVALIDATE RELY標記的約束及注冊為物化視圖的先存表。
對于采用可信信息（包括維度）的查詢改寫，初始化參數QUERY_ REWRITE_INTEGRITY必須被設置為TRUSTED，如下所示：

ALTER SESSION SET query_rewrite_integrity = TRUSTED;

例如，假設有一個時間維度，其聲明如下：

CREATE DIMENSION time_dim
LEVEL time_key IS time.time_key
LEVEL month IS time.month
LEVEL quarter IS time.quarter
LEVEL year IS time.year
HIERARCHY 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中要求按年的銷售額的查詢，你仍然可以使用monthly_sales_mv物化視圖，因為維度對象中的HIERARCHY子句告訴Oracle數據庫月銷售額可以積累成年銷售額。它利用前面描述的joinback技巧由物化視圖中的“月”列得到“年”列的值。
代碼清單 4：通過joinback和HIERARCHY獲得銷售總額

SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
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
維度的ATTRIBUTE子句指明了一對一關系。例如，你可以判定從time_key開始是一周中的哪一天。假設你希望得到每年1月份的銷售總額：你仍然可以使用清單 5中所示的monthly_sales_mv物化視圖。注意該查詢的WHERE子句如何具有一個在物化視圖中沒有出現的選擇條件。
代碼清單 5：通過joinback和ATTRIBUTE獲得銷售總額

SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE 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

如果優化器并未如期改寫一個查詢，可以使用DBMS_MVIEW .EXPLAIN_REWRITE 過程來診斷該問題。這一特性出現在Oracle9i數據庫及以后的版本中。
過濾后的數據
到目前為止，我們所給出的所有示例都使用了與采購表中的所有數據對應的物化視圖。Oracle9i數據庫具備在物化視圖僅有一個數據子集情況下改寫查詢的能力。例如，如果你只對1997年到2002年的銷售額感興趣，你可以將物化視圖修改如下：

CREATE MATERIALIZED VIEW five_yr_monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id,
       SUM(ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year between 1997 and 2002
GROUP BY t.month, p.product_id;

此物化視圖可用于響應要求從1997年至2002年數據的查詢，例如，清單 6中的查詢要求2000年的銷售額。
代碼清單 6：只查詢物化視圖
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year = 2000
GROUP 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數據庫中，如果物化視圖中沒有查詢所需要的全部數據，查詢就不會使用物化視圖。在Oracle數據庫10g中，已經放松了這一限制，因此查詢改寫可以由物化視圖中獲得盡可能多的數據，并利用細目表獲得物化視圖中沒有的數據。和往常一樣，優化器在做出執行此操作的決定時考慮了有改寫和無改寫情況下的查詢成本。
例如，清單 7中的查詢要求2000年至2003年之間的月銷售額，它將使用從2000年至2002年的物化視圖，而只需要2003年的細目表。
代碼清單 7：查詢物化視圖和細目表

SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year BETWEEN 2000 and 2003
GROUP 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

使用失效的物化視圖進行查詢改寫
你可能想知道如果細目表中的數據發生了變化會發生什么情況。查詢改寫仍將使用物化視圖嗎？答案決定于初始化參數QUERY_REWRITE_ INTEGRITY的設置。QUERY_REWRITE_INTEGRITY參數有三個取值：
STALE_TOLERATED表示即使細目表中的數據已經發生了變化，也仍然使用物化視圖。
TRUSTED 表示物化視圖未失效時才使用該視圖。但是，查詢改寫可以使用信任關系，如那些由維度對象或尚未生效的約束所聲明的關系。
ENFORCED（缺�。┍硎井斘锘晥D保證能給出與使用細目表相同的結果時才使用它。使用這一參數意味著查詢改寫將不使用失效的物化視圖或信任關系。
正確的設置決定于應用程序的數據需求。使用失效物化視圖的查詢改寫可能會產生與沒有使用查詢改寫時不同的結果。然而，如果使用細目數據，可能會因為響應查詢需要處理的大量數據而使性能惡化。在一個數據倉庫中，通常使用TRUSTED完整級別，因為這樣才可以保證你只使用那些具有最新數據的物化視圖；然而，被聲明為正確（可信任）的關系也可用于查詢改寫。在大多數數據倉庫中，這些關系已經在提取、轉換和加載（ETL）過程得到了驗證，因此不再需要進行驗證。
分區變化跟蹤
在Oracle9i數據庫中，Oracle引入了分區變化跟蹤（PCT，Partition Change Tracking）。利用這一特性，Oracle9i數據庫可以跟蹤物化視圖的哪一部分對應于分區細目表的已更新部分。因此，如果查詢不需要已更新表的部分，那么該物化視圖仍然可以使用。
為了在物化視圖中跟蹤一個細目表的變化，必須對該表進行分區，并且該物化視圖（在SELECT列表中）必須包括細目表的分區鍵或一個特殊函數：DBMS_MVIEW.PMARKER。此函數為細目表中的每個分區生成一個唯一的標識符。
例如，由time_key對采購表進行分區。清單 8中創建的物化視圖與前面使用的monthly_sales_mv 物化視圖幾乎完全相同，只是該物化視圖在采購表上包含了一個附加的DBMS_MVIEW.PMARKER函數。通過包含這一函數，當更新采購表時該物化視圖允許PCT。注意：該物化視圖自身并不需要被分區。
代碼清單 8：具有DBMS_MVIEW.PMARKER函數的物化視圖

CREATE MATERIALIZED VIEW monthly_sales_pct_mv
ENABLE QUERY REWRITE
AS
SELECT 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_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY DBMS_MVIEW.PMARKER(ps.rowid), t.month, p.product_id;

現在，假設我們向采購表中增加一個2003年4月的新分區，而且一個用戶發出了一個請求2002年3月的數據的查詢，如清單 9所示。在此查詢中，我們并不關心2003年4月已更新的數據，所以將利用物化圖對其進行改寫，即使該物化視圖已經失效也是如此。
代碼清單 9：使用失效的物化視圖進行查詢改寫

SELECT t.month, p.product_id, SUM(ps.purchase_price)
FROM time t, product p, purchases ps
WHERE 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月的數據，在Orcale9i中，將不會為使用物化視圖而對該查詢進行改寫。但在Oracle數據庫10g中，可以使用MONTHLY_SALES_ PCT_MV和細目表的結合對該查詢進行改寫。
使用多個物化視圖進行查詢改寫
前面曾經提到，在Oracle10g數據庫中，查詢改寫已經得到了增強，所以它可以使用一個物化視圖的部分數據以及細目表的其余數據來響應查詢。事實上，查詢改寫可以結合使用兩個或多個物化視圖。例如，假設你為每5年的數據價值維護一個獨立的物化視圖： monthly_sales_1990-1994、 monthly_sales_1995_to_2000、 monthly_sales_2001_to_2005，等等。
那么，對于需要從1993年至2003年數據的清單 10中的查詢，查詢改寫可以利用全部的三個物化視圖。
代碼清單 10

SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales,
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year between 1993 and 2003
GROUP 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_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id AND
      t.year between 1989 and 1999
GROUP 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年的數據，所以查詢改寫可以使用物化視圖monthly_sales_1990_to_1994和monthly_sales_1995_to_2000，并由細目表獲得1989年的數據。這一過程實質上會比由細目表中獲得所有數據更快一些。
Oracle10g數據庫在查詢改寫方面有其他幾個改進。在這些改進中值得注意的是Oracle10g數據庫能夠更好地支持集合運算符（UNION、 UNIONALL等）增強了對多個表實例的查詢的增強，并提供了對分區變化跟蹤中的LIST 和RANGE-LIST分區類型的支持。
工具
你可能會一邊閱讀本文，一邊自言自語：“嗯，我想我理解了你的意思，但是否有某些工具可以為我完成所有這些工作呢？”答案是肯定的。事實上，這些工具還相當多。
Oracle9i數據庫引用了explain_mview和explain_rewrite。應用編程接口（API）EXPLAIN_MVIEW采用一個物化視圖定義，并建議可使用何種類型的分區變化跟蹤操作、是否可能進行快速刷新，以及可以完成何種類型的查詢改寫。當一個查詢未被改寫時，API EXPLAIN_REWRITE將告訴你SQL查詢為什么不使用查詢改寫。在兩種情況下，工具包都會告訴你問題所在--例如，不能在一個特定列上進行連接，但兩個包都不會準確告訴你如果解決這個問題。這時，就可以使用包含在Oracle10g數據庫中的兩個新工具--TUNE_MVIEW和SQL Access Advisor來幫助你解決這個問題。
TUNE_MVIEW API將告訴你如何編寫物化視圖，使其可以快速刷新，并可以使用本文所描述的盡可能多的高級查詢改寫類型。TUNE_MVIEW API 的使用非常簡單：只需要將你的物化視圖語句交給它，它就會判定該物化視圖的最佳形式。但是，如果你看到你的原始物化視圖已經被轉換為多個新版本，也不要感到奇怪。
讓我們來看看TUNE_MVIEW 如何能夠轉換你的物化視圖。假設我們有一個簡單的查詢，并將其傳遞給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, possible
FROM mv_capabilities_table
WHERE capability_name = ’REFRESH_FAST’ and STATEMENT_ID = ’ID1’;

CAPABILITY_NAME                P
---------------------------------
REFRESH_FAST                   N
現在讓我們使用相同的查詢，并將其傳遞給TUNE_MVIEW，如以下代碼所示：

variable task_name varchar2(2000);
BEGIN
DBMS_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 IMMEDIATE
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 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;
[page_break]
目錄視圖USER_TUNE_MVIEW將顯示所得到的物化視圖，如清單 13所示。盡管它看起
來與我們的原始物化視圖有點不同，但在可以使用原始物化視圖的地方，仍然可以使用該
物化視圖改寫任何查詢，此外，還可以快速刷新。
你也可以生成一個腳本來執行這些建議，你可能希望做的僅有修改就是改變物化視圖的名稱，以及指定物化視圖應當放在哪里的存儲語句和表空間。
現在，我們已經有了一個物化視圖，但如果我們不知道創建什么物化視圖，那么應當怎么辦？這時，SQL Access Advisor可以幫助你，因為它會瀏覽你的系統，并它認為需要的索引和物化視圖。
這些建議是基于實際的工作負荷或根據你的模式所做出的假設提出的。當提供了SQL語句的實際工作負荷時，將得到最好的結果。這一工作負荷可由SQL緩存的當前內容、SQL 調優集合（Tuning Set）、Oracle9i Summary Advisor工作負荷或用戶提供的工作負荷表（包含你已經定義的SQL語句）獲得。
SQL Access Advisor既可以通過命令行API使用，也可以通過企業管理器（Enterprise Manager）的一部分--SQL Access Advisor向導使用。使用該向導，在顯示這些建議之前只需要完成三個步驟。讓我們來看看如何通常命令行界面使用SQL Access Advisor：
首先，創建一個包含，這一調優過程所有信息的任務。然后，該任務將利用工作負荷信息來生成作為任務的一部分存儲的調優建議。因此，整個過程是完全獨立的，而且允許各個任務稍有不同，以便人們能夠看到對配置進行修改后的效果。在清單 14所示的示例中，是通過手工定義SQL語句對工作負荷進行定義的。
代碼清單 14

DECLARE

task_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;
/
一旦定義了工作負荷和任務，就可以生成如下所示的建議，該建議使用了EXECUTE_TASK 并指定了所創建任務的名字--Task_mag：

execute dbms_advisor.execute_task (’Task_mag’);

根據工作負荷的復雜性，生成建議的時間可以由幾秒到幾分鐘不等。因此，盡管這個過程可以交互式運行，但你可能希望考慮提交一個任務，這就是企業管理器中的向導所要完成的工作。
你可以通過查詢表USER_ADVISOR_RECOMMENDATIONS 來快速檢查是否有關于task_name的建議。在對本例進行此操作時，我們會看到已經提出了一個建議。

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

單個建議可以導致多個操作。對于此示例，SQL Access Advisor建議創建物化視圖日志、一個CREATE MATERIALIZED VIEW，以及一個用來分析物化視圖的調用（受版面限制，這里未給出）。
盡管你可以查詢各種目錄視圖來查看這些操作，但查看它們的最簡單方法就是生成一個腳本，如下所示：

execute
dbms_advisor.create_file(dbms_advisor.get_task_script(’Task_mag’),
’ADVISOR_RESULTS’, ’mag_example.sql’);

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

Rem  Access Advisor
Rem
Rem  Username:        EASYDW
Rem  Task:            My_Task
Rem  Execution date:  20/05/2003 14:36
Rem
...
CREATE MATERIALIZED VIEW "EASYDW"."MV$$_002D0000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 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;
...
結論
通過使用查詢改寫，你可以利用幾個物化視圖顯著改進許多查詢的性能，從而減少了保持物化視圖與基礎細目數據同步所需要的磁盤空間占用與刷新時間

全新的路由器不僅讓你更穩定快速地連接無線網絡，更可以讓家中的智能設備連接在一起。

溫馨提示：喜歡本站的話，請收藏一下本站！

當前位置:雨林木風下載站 > 應用軟件教程 > 詳細頁面

使用查詢改寫提高查詢性能

本類教程下載

系統下載排行