簡介
物化查詢表 (MQT) 是一個這樣的表，它物化了一個涉及一個或多個表或昵稱的查詢的預先計算的結果。當創建並填充好 MQT 之後，一個任意的後續查詢就會由 MQT 來滿足，條件是 MQT 匹配該查詢的所有部分或一部分。要了解更多信息，請參見 [1]。大家都知道，使用 MQT 高速緩存數據和允許查詢優化器作出一個基於成本的決策以使用 MQT 來滿足查詢可以改善 Database Management System (DBMS) 中的查詢性能。在諸如 WebSphere Information Integrator 之類的聯邦系統中使用 MQT 出於以下原因可以更加有益：
聯邦查詢一般在遠程系統上執行部分（或整個）查詢，並通過網絡將即時結果返回給聯邦系統。由於網絡的延遲，以聯邦模式運行查詢一般比只訪問本地數據的相同查詢要慢一些。涉及昵稱的 MQT 使得遠程數據本地可用，因此省去了通過網絡到遠程數據源的來回。
如果需要從中獲得查詢數據的一個遠程 DBMS 不可用，那麼在查詢的訪問計劃決定使用 MQT 而非遠程數據來滿足查詢時，查詢將仍然能夠產生結果。
本文介紹 WebSphere Information Integrator 中的 MQT。展示如何定義適合於工作負載的 MQT，以及如何填充它們。給出在 WebSphere Information Integrator 中使用 MQT 的性能結果。給出用於確定查詢是否使用了 MQT 的提示和技巧，如果未使用，是什麼防止了 MQT 的使用。還例舉了使用 MQT 的限制。
概述
本文首先在 理解 MQT 一節給出一個啟發性例子，展示 MQT 如何可以改善查詢性能。然後簡要解釋了 如何作出在查詢中使用 MQT 的決策 和 在聯邦環境中使用 MQT 的優勢。創建和使用 MQT 的步驟 討論如何選擇可能改善工作負載的性能的 MQT。這一節詳細介紹了創建一個 MQT、用數據填充它以及為了優化而啟用它的所有步驟。關於 故障診斷 的一節幫助您確定為什麼沒有使用 MQT 來滿足查詢。這一節還列出了關於使用 MQT 的 限制。本文的最後一節描述我們的內部 性能試驗 和使用 MQT 獲得的性能優勢。
理解 MQT
本節介紹一個示例業務場景，並逐步介紹使用 MQT 的需要，以及創建和啟用它的步驟。
一個啟發性例子
考慮一家公司的總部在紐約，而數據倉庫在聖何塞。數據倉庫跟蹤該公司在美國銷售的所有產品的銷售記錄。銷售數據維護在具有以下模式的表 'sales' 中。
City State Product_name Quantity Price Transaction_date
sales 表中的一行是給定日期、城市和州銷售的特定產品的總數量和總價格。位於紐約的總部采用一個 WebSphere Information Integrator 系統。WebSphere Information Integrator 系統相應於聖何塞數據庫中的 'sales' 表具有一個昵稱 'divisional_sales'。
考慮總部的銷售經理想要查看加利福尼亞州在 2005 年銷售的產品。他們需要的是按城市聚集的信息。
按城市計算總銷售的查詢可以寫成：
SELECT CITY, SUM(PRICE) AS SUM_SALES
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY CITY;
經理們會經常運行這類查詢，有時會在 City 或 SUM_SALES 列添加更多的過濾條件。經理們希望找到的上一個查詢的一個變體是查看在城市聖何塞中銷售的產品。
這一要求可以表達為在上一個查詢中添加一個謂詞 CITY = 'SAN FRANCISCO'。該查詢類似於：
SELECT CITY, SUM(PRICE) AS SUM_SALES
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND CITY='SAN FRANCISCO'
AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005';
當經理們想要找到總銷售量小於 $100,000 的城市時，會產生另一個變體。該信息可以表達為在上面的查詢中添加一個謂詞 SUM_SALES < 100000，如下所示：
SELECT CITY, SUM(PRICE) AS SUM_SALES
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY CITY
HAVING SUM(PRICE) < 100000;
銷售信息經常更新到 sales 表中，但這不是說經理們必須得到最新的信息。查詢的業務環境能夠忍受已經過期幾個小時甚至幾天的信息。在本例中，得出總銷售信息是一個計算復雜的任務。而且，計算會對一些稍微不同的條件重復進行多次。一個需要指出的重要特征是，稍微過期的數據是可以接受的。可以定義一個 MQT，使得部分計算可以只執行一次並存儲結果，以便後續的查詢只需要很少的額外處理就可以從高速緩存的計算結果獲得答案。
滿足以上三個查詢變體的一個可能的 MQT 定義類似於：
CREATE TABLE AGGREGATE_SALES (CITY, TOTAL_SALES) AS
(SELECT CITY, SUM(PRICE)
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY CITY)
DATA INITIALLY DEFERRED REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM;
MQT 定義中的子句指出 MQT 的行為。稍後的幾節中將詳細討論這些子句。一旦 MQT 已經創建並填充好，當用戶提交一個查詢以找到總銷售數據或變體信息時，就會通過使用 aggregate_sales MQT 來滿足該查詢。
如何作出在查詢中使用 MQT 的決策
為了使用 MQT，用戶不必要更改查詢。一旦 MQT 已經創建並用數據填充好了，WebSphere Information Integrator 優化器就可以確定用戶查詢是否可以通過使用 MQT 來滿足，以及使用 MQT 是否會帶來性能優勢。為了優化器使用 MQT 來取代部分或整個查詢，需要滿足以下條件：
MQT 應用用戶查詢考慮應用的謂詞的相同集合或子集。即使 MQT 只包含用戶查詢應用的謂詞的一個子集，它仍然是可以使用的，因為優化器通過向從 MQT 返回的數據添加缺少的謂詞進行了彌補。
MQT 選擇用戶查詢考慮選擇的列的相同集合或超集。如果查詢比相應匹配的 MQT 選擇較少的列，那麼優化器可以消除不必要的列。
其他條件與 MQT 中數據的新鮮性和用於運行查詢滿足的優化級別有關。“如何使用和創建 MQT” 一節中將介紹這些條件。
在聯邦環境中使用 MQT 的優勢
出於各種原因，MQT 是有益的。下面描述了其中一些原因：
計算昂貴的查詢的性能可以得到改善。緩存昂貴計算的結果一次，並多次使用緩存的結果，可以帶來性能優勢。它也使用較少的 CPU，並防止系統過載。
對多個查詢公共的計算可以使用 MQT 來表示。MQT 使用數據填充一次，被多個查詢重用，從而帶來性能優勢。
MQT 將數據本地緩存在聯邦系統中，可以避免從後端數據源到傳輸數據的昂貴循環。
如果向查詢提供數據的後端數據源斷開了，並且優化器選擇使用 MQT 來滿足查詢，那麼查詢將會執行。因此，數據源的不可用性將不會妨礙查詢的執行。
MQT 向優化器提供重要的統計信息。更具體地說，收集了其最近的統計信息的 MQT 的存在可以幫助優化器確定謂詞的過濾級別，例如，以更加精確的方式計算謂詞的選擇。
創建和使用 MQT 的步驟
步驟 1：使用 Design Advisor 來確定將會有助於改善查詢工作負載性能的 MQT 集合
將會有助於改善工作負載性能的 MQT 的種類可由對查詢的分析或通過使用諸如 Design Advisor 之類的工具來確定。本節將討論確定 MQT 可能對工作負載是有益的。
DB2® Design Advisor 幫助用戶創建物化查詢表 (MQT) 和索引、重新分配表並轉換到多維集群 (MDC) 表，以及刪除未用的對象。所有的推薦基於用戶提供的一個或多個 SQL 語句。一組相關的 SQL 語句也叫做一個工作負載。用戶可以為一個工作負載中的每個語句指定重要級別，並指定工作負載中每個語句執行的頻率。Design Advisor 創建一個 SQL 腳本輸出，其中包含用於創建推薦的對象的 CREATE INDEX、CREATE SUMMARY TABLE (MQT) 和 CREATE TABLE 語句。
在本文中，我們著重講述如何使用 Design Advisor 來為聯邦查詢推薦 MQT。Design Advisor 是通過 DB2 Control Center 或 "db2advis" 命令調用的。
下面這個例子演示如何使用 Design Advisor 來為聯邦工作負載推薦 MQT。在這個例子中：
組成工作負載的查詢從文本文件 'my_querIEs.sql' 讀取，
工作負載應用於數據庫 'mydb' 中定義的對象上，且
MQT DDL 語句保存在文件 'my_rec_mqts.sql' 中。
Advisor 將推薦 REFRESH DEFFERED MQT。
db2advis -d mydb -i my_querIEs.sql -m M -o my_rec_mqts.sql -u -k OFF
Design Advisor 生成的輸出文件包含一組 DDL 語句，用於創建 MQT、刷新和更新 MQT 上的統計信息，以及在其上創建索引。輸出文件一生成就可以使用，或者為應用程序的特定需求進行定制。通過從 MQT 刪除過濾器（本地）謂詞，Design Advisor 為給定查詢一般化推薦的 MQT。例如，如果 Design Advisor 是在這樣一個查詢上調用，該查詢從三個表選擇數據，並且包含三個聯結謂詞和兩個過濾器謂詞，那麼推薦的 MQT 將不會包含任何過濾器謂詞。如果您認為自己的查詢總是使用相同的常量值過濾數據，那麼您可能選擇在 MQT 中包含一些過濾器謂詞。
步驟 2：創建 MQT
一旦確定了 MQT 定義，就可以使用 "CREATE TABLE" 語句創建 MQT 了。我們將使用前面 “理解 MQT” 一節中的例子。我們的示例 MQT 類似於：
CREATE TABLE AGGREGATE_SALES (CITY, TOTAL_SALES) AS
(SELECT CITY, SUM(PRICE)
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY CITY)
DATA INITIALLY DEFERRED REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM;
創建 MQT 時需要仔細地確定要使用的子句。
'DATA INITIALLY DEFERRED' 子句指出，當創建 MQT 時，不會作為 create table 語句的一部分填充 MQT。
'REFRESH DEFERRED' 子句指出，使用 'REFRESH' 語句將用數據填充 MQT。對 MQT 定義中使用的表或昵稱進行的數據插入、更新或刪除等形式的任何更改將不會自動導致 MQT 中數據的插入、更新或刪除。'REFRESH DEFERRED' 的一個替代是 'REFRESH IMMEDIATE' 選項。該選項指定，對 MQT 定義中使用的表進行的數據插入、更新或刪除等形式的任何更改將會自動導致 MQT 中數據的更改。該選項對其定義引用昵稱的 MQT 不可用。在本文中，我們將通過昵稱使用 MQT，因此這些 MQT 將總是定義為 'REFRESH DEFERRED' MQT。
MQT 可以通過設置 'ENABLE QUERY OPTIMIZATION' 子句而對優化器可用。'DISABLE QUERY OPTIMIZATION' 子句可以用來防止優化器考慮使用特定的 MQT。
'REPLICATED' 子句指出，在啟用 Data Partitioning Feature (DPF) 的系統的所有分區上建立 MQT 的一個副本。在這樣的系統中，有可能指定 MQT 的分區或者將它定義為 REPLICATED MQT。如果 MQT 被定義為 REPLICATED，那麼可以使用該 MQT 的操作（比如，聯結）就可能獲得更好的性能，並通過排列（collocation）將它與相同節點上的數據相關聯。
MQT 可以根據以下用數據填充它們時所使用的機制來分類：
System-Maintained MQTs：系統維護的 MQT 中的數據由 WebSphere Information Integrator 系統來管理。'REFRESH TABLE' 語句可用於用數據填充 MQT。不允許用戶直接在 MQT 中插入、更新或刪除數據。
User-Maintained MQTs：顧名思義，用戶維護的 MQT 由用戶維護。'REFRESH TABLE' 語句不適用於用戶維護的 MQT。用戶可以在用戶維護的 MQT 中插入、更新或刪除數據。用戶也可以使用 'LOAD' 實用工具來用數據填充 MQT。由用戶來負責確保用戶維護的 MQT 包含與 MQT 的定義一致的數據。
Federated_Tool- Maintained MQTs 或 Cache Tables：定義為 'MAINTAINED BY FEDERATED_TOOL' 的 MQT 由自動復制維護。這些 MQT 不支持使用 'REFRESH TABLE' 語句。Federated_tool 維護的 MQT 也叫做 Cache Tables。它們是用戶維護的 MQT 的特殊子類型，其中的數據是通過使用 Replication 填充的。"Cache tables" 只可使用 Control Center 來設置。有一些限制只應用於 Cache tables，不應用於系統維護的和用戶維護的 MQT。這些限制將在下面的 限制 和 故障診斷 小節中詳細介紹。
步驟 3：使用 SET INTEGRITY 語句使 MQT 脫離 check-pending 狀態
該步驟只應用於用戶維護的 MQT，並且啟用 MQT 的數據填充。在我們的例子中，'aggregate_sales' 被定義為用戶維護的 MQT，必須發出下面這個語句使 MQT 脫離 check-pending 狀態。
SET INTEGRITY FOR AGGREGATE_SALES ALL IMMEDIATE UNCHECKED;
"SET INTEGRITY" 語句指定，MQT 將打開完整性檢查，而不檢查完整性違反。
該步驟對於系統維護的 MQT 的不是必需的，因為用於填充數據的 REFRESH 語句使系統維護的 MQT 脫離 check-pending 狀態。
步驟 4：用數據填充 MQT
一旦創建了 MQT，就需要用數據填充它，以反映 MQT 將緩存的查詢結果。每當 MQT 的內容基於底層數據被更新時，也需要執行該步驟。
用數據填充系統維護的 MQT：
如果 MQT 是系統維護的 MQT，那麼可以使用 'REFRESH TABLE' 語句用數據填充它。可以針對我們的示例 MQT 調用 "REFRESH TABLE" 語句，如下所示：
REFRESH TABLE AGGREGATE_SALES;
每當針對一個其中定義了昵稱的 MQT 調用 REFRESH 語句時，該語句會刪除 MQT 中預先存在的任何數據並重新用數據進行填充。增量刷新不可用。根據查詢的復雜性和結果集的大小，刷新操作可能需要很長時間。您可以在任務中心設置任務，使 MQT 中諸如刷新之類的操作在您選擇的時間發生。
用數據填充用戶維護的 MQT：
如果 MQT 是用戶維護的，那麼可以使用 INSERT 或 LOAD 語句來填充它。如果我們的示例 MQT 是用戶維護的 MQT，那麼可以使用 INSERT 語句來填充它，如下所示：
INSERT INTO AGGREGATE_SALES (SELECT CITY, SUM(PRICE)
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY CITY);
用戶維護的 MQT 也可以使用 LOAD 語句來填充。下面列出了完成該操作的步驟：
使用 'DECLARE CURSOR' 語句在查詢的評估上定義一個游標。查詢的結果將在下一個 LOAD 語句中被插入 MQT 中。
DECLARE CUR1 CURSOR FOR
SELECT CITY, SUM(PRICE)
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY CITY;
使用 “Load 命令” 將數據加載到 MQT 中。
LOAD FROM CUR1 OF CURSOR INSERT INTO AGGREGATE_SALES;
LOAD 命令的 'INSERT' 選項將數據插入 AGGREGATE_SALES 表中。這可能會導致重復的值。LOAD 的 'REPLACE' 選項可用來首先刪除 AGGREGATE_SALES 表中現有的數據，然後再插入新的數據。
我們的試驗表明，當需要將相對較大的數據加載到 MQT 中時，LOAD 執行得比 INSERT 更快。您可能想要試驗這兩個選項，以確定 LOAD 和 INSERT 哪一個對您的場景來說性能更好一些。
您可以考慮在任務中心設置一個任務，使用戶維護的 MQT 中的數據更新發生在您選擇的時間。
用數據填充 federated_tool 維護的 MQT
一個復制計劃被自動設置在 MQT 和該 MQT 所基於的昵稱之間。復制使得數據保存在當前 MQT 中。
步驟 5：在 MQT 上創建索引
一旦創建了 MQT，您可能就想檢查是否應該在 MQT 上創建任何索引。這可通過 "CREATE INDEX" 語句來執行。因為 MQT 是緩存查詢結果的本地表，所以在本地表上創建索引的相同步驟也可應用於在 MQT 上創建索引。如果 MQT 具有一個鍵，那麼應該創建惟一的索引以反映這一事實。索引的創建，惟一或不惟一，都有助於以不同的方式從 MQT 讀取數據，導致更好的性能。
注意，惟一的索引不能創建在系統維護的 MQT 上。對於我們的例子來說，如果 'City' 是 MQT aggregate_sales 的鍵，那麼 aggregate_sales 是用戶維護的 MQT，可在該 MQT 上創建惟一的鍵，如下所示：
CREATE UNIQUE INDEX uniq_city_index ON aggregate_sales (City);
步驟 6：在昵稱上定義信息約束
在昵稱上定義 “信息約束” 以反映底層表上的約束，有助於甚至在缺少 MQT 的情況下優化性能。當存在 MQT 時，信息約束有時有助於優化器向 MQT 匹配一個查詢，這在缺少信息約束時 MQT 是不會考慮匹配的。我們用一個例子來演示這一點：
假設除了前面描述的 DIVISIONAL_SALES 昵稱以外，用戶還擁有昵稱 'OFFICE_INFO'。'OFFICE_INFO' 昵稱具有 CITY、Office_ADDR 和 PHONE_NUM 等列。
OFFICE_INFO 昵稱中的列 'CITY' 是惟一的，因此在列 CITY 上定義了昵稱 Office_INFO 的惟一約束，如下所示：
ALTER NICKNAME Office_INFO ADD CONSTRAINT UNIQ_CONS UNIQUE (CITY)
NOT ENFORCED ENABLE QUERY OPTIMIZATION;
昵稱 DIVISIONAL_SALES 中的列 CITY 總是在昵稱 OFFICE_INFO 中找到一個匹配的城市，因此在昵稱 Office_INFO 和 DIVISIONAL_SALES 之間的列 CITY 上定義了一個信息參考完整性約束，如下所示：
ALTER NICKNAME DIVISIONAL_SALES ADD CONSTRAINT CITY_CONSTRAINT
FOREIGN KEY (CITY) REFERENCES Office_INFO(CITY) NOT ENFORCED
ENABLE QUERY OPTIMIZATION;
還與前面描述的一樣，假設 MQT aggregate_sales 已經存在。
現在，用戶發出一個查詢：
SELECT DS.CITY, SUM(PRICE)
FROM DIVISIONAL_SALES DS, Office_INFO OI
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
AND DS.CITY = OI.CITY
GROUP BY DS.CITY;
因為參考完整性約束確保 DIVISIONAL SALES 中 CITY 的每個值在 OFFICE_INFO 中找到一個匹配，所以優化器可以安全地消除到 Office_INFO 昵稱的聯結和到 MQT AGGREGATE_SALES 的路由。
步驟 7：使用 runstats 收集 MQT 上的統計信息
一旦創建了 MQT 和相關的索引，就最好在 MQT 上運行 "runstats" 以收集統計信息。WebSphere Information Integrator 優化器是基於成本的，並依賴於所有對象（包括 MQT）的精確統計信息來作出執行計劃決策。對於我們的示例 MQT，runstats 可以運行來收集統計信息，如下所示：
RUNSTATS ON TABLE sample.aggregate_sales WITH DISTRIBUTION AND DETAILED INDEXES ALL;
這裡的 'sample' 是模式，MQT aggregate_sales 是在該模式下創建的。
步驟 8：允許為優化考慮 MQT
有兩種方式允許為優化考慮 MQT。一個 MQT 也叫做一個維護表（maintained table）。
使用 'CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION' 特殊注冊表。特殊注冊表可以設置為一個希望的值，以便為會話啟用特定的維護表對象。"SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement" 識別可在動態 SQL 查詢優化過程中考慮的 MQT 的類型。可能的值有：
SYSTEM：只為優化考慮系統維護的 MQT。這是默認值。
ALL：將為優化考慮所有維護表類型。
NONE：不會為優化考慮任何維護表類型。
FEDERATED_TOOL：將只為優化考慮定義為 'MAINTAINED BY FEDERATED_TOOL' 的維護表。
USER：將只為優化考慮定義為 'MAINTAINED BY USER' 的維護表。
例如，為了指示優化器應該為優化考慮所有維護表類型，您應該像下面這樣設置特殊注冊表：
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION ALL;
使用數據庫配置參數 'DFT_MTTB_TYPES'。這個 "dft_mttb_types - Default maintained table types for optimization configuration parameter" 為 CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION 特殊注冊表指定默認值。注冊表的值又確定在查詢優化期間將使用何種類型的刷新延遲物化查詢表。這一數據庫配置參數的有效值有 ALL、NONE、FEDERATED_TOOL、SYSTEM 和 USER，語義與上面為特殊注冊表的值描述的語義相同。該數據庫配置參數的默認值是 'SYSTEM'。
例如，為了指示只為優化考慮定義為 'MAINTAINED BY USER' 的維護表，您應該像下面這樣為數據庫 SAMPLE 設置配置參數：
DB2 UPDATE DATABASE CONFIGURATION FOR SAMPLE USING DFT_MTTB_TYPES USER;
步驟 9：設置 REFRESH AGE 特殊注冊表
REFRESH AGE 指定 MQT 中的過期數據是可忍受的。優化器將考慮只在 REFRESH AGE 被設置為非零值且優化級別被設置為 2 或 5 或更高時才使用 MQT 來處理查詢。REFRESH AGE 只應用於 'REFRESH DEFERRED' 類型的 MQT。當前支持兩個 REFRESH AGE 值。
0：這表明 'REFRESH DEFERRED' MQT 將不被優化器考慮來處理查詢。
關鍵字 'ANY'：該設置表明 MQT 將被優化器考慮來處理查詢。
REFRESH AGE 可以兩種方式之一進行設置：
“設置 CURRENT REFRESH AGE 特殊注冊表”
將 CURRENT REFRESH AGE 特殊注冊表設置為值 ANY 使得在當前連接上下文中，將為優化考慮所有刷新延遲的 MQT。
“設置 DFT_REFRESH_AGE 數據庫配置參數”
REFRESH_AGE 的默認值可以設置為數據庫配置參數。例如：
DB2 UPDATE DATABASE CONFIGURATION FOR SAMPLE USING DFT_REFRESH_AGE 0;
該設置防止為優化考慮刷新延遲的 MQT。如果設置了特殊注冊表的值，特殊注冊表將優於數據庫配置參數。如果沒有將特殊注冊表設置為 any 值，那麼數據庫配置參數的值將確定 REFRESH AGE。
兩種參數都以默認值 0 開始，例如，默認情況下不為優化考慮 'REFRESH DEFERRED' MQT。
步驟 10：找出是否使用 MQT 來回答查詢
您可以使用 Visual Explain、Dynamic Explain 或 db2exfmt 工具的輸出來找出是否使用 MQT 來滿足查詢。在本節中，我們將展示 db2exfmt 輸出的一個片段來解釋如何確定是否使用了 MQT。
當訪問計劃使用了 MQT 時，db2exfmt 輸出將展示一個被訪問的 MQT 對象。此外，對於某些查詢，db2exfmt 輸出中優化的 SQL（這是查詢重寫的輸出）展示了 MQT 的使用而非基本昵稱。在 db2exfmt 輸出中，優化的 SQL 緊接在最初的 SQL 語句後面。
對於下面整個查詢：
SELECT CITY, SUM_SALES
FROM
(SELECT CITY, SUM(PRICE) AS SUM_SALES
FROM DIVISIONAL_SALES
WHERE STATE ='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY CITY) AS TOTAL_SALES
WHERE SUM_SALES < 100000;
注意，該查詢被重寫來訪問 aggregate_sales MQT。
SELECT Q1.CITY AS "CITY", Q1.TOTAL_SALES AS "SUM_SALES"
FROM SAMPLE.AGGREGATE_SALES AS Q1
WHERE (Q1.TOTAL_SALES < 100000);
計劃片段類似於：
Rows
RETURN
( 1)
Cost
I/O
|
20.6667
TBSCAN
( 2)
20.1968
1
|
62
TABLE: SAMPLE
AGGREGATE_SALES
該計劃展示了 aggregate_sales MQT 上的一個表掃描。此外，操作符表掃描的詳細信息展示了以下信息：
Input Streams:
-------------------
1) From Object SAMPLE.AGGREGATE_SALES
Estimated number of rows: 62
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$+Q1.TOTAL_SALES+Q1.CITY
訪問計劃中使用的對象的列表顯示在 db2exfmt 輸出的末尾。對於我們的例子來說，顯示了以下信息。注意，昵稱 divisional_sales 顯示為訪問計劃中使用的對象中的一個。但是單詞 '(reference only)' 表明昵稱引用在查詢中，但是不被訪問來滿足查詢。
Objects Used in Access Plan:
---------------------------------------
Schema: SAMPLE
Name: DIVISIONAL_SALES
Type: Nickname (reference only)
Schema: SAMPLE
Name: AGGREGATE_SALES
Type: Table
Time of creation: 2006-02-17-17.16.21.751126
Last statistics update:
Number of columns: 2
Number of rows: 62
Width of rows: 44
Number of buffer pool pages: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
MQT 優於昵稱的限制
存在一些 MQT 優於昵稱的限制，以便聯邦查詢使用 MQT。這些限制可分為三類：
分區數據庫環境中的 MQT 優於昵稱限制
MQT 優於非關系昵稱限制
Design Advisor 建議的 MQT 優於昵稱限制
分區數據庫環境中的 MQT 優於昵稱限制
WebSphere Information Integrator 不支持在分區數據庫環境中引用昵稱的系統管理的物化查詢表。請參考 “分區數據庫環境中的 MQT 優於昵稱限制”，了解關於如何處理該限制的提示。
MQT 優於非關系昵稱限制
存在一組特定於每個非關系數據源的 MQT 優於非關系昵稱限制。這些限制由必需的謂詞所引入，並隱含了特定數據源的昵稱之間的關系。例如，Blast 和 HMMER 數據源的昵稱使用一些謂詞來將值組合到充當傳遞給 Blast 和 HMMER 工具的輸入參數的列。因此，定義在這類昵稱上的 MQT 必須指定這些謂詞；否則，MQT 上的刷新將會失敗，或者使用 MQT 的查詢將產生不正確的結果。另一個例子是 XML 昵稱的限制，即不能為子 XML 昵稱創建 MQT。MQT 必須從根昵稱到這個子昵稱引用昵稱的整個層次結構。
Design Advisor 推薦的 MQT 限制
Design Advisor 生成的 MQT 有很少的幾個限制和考慮因素，如下所示。
Design Advisor 不推薦用戶維護的 MQT。因為昵稱上的系統維護的 MQT 在分區數據庫配置中是不受支持的，所以您不能在這樣的環境中使用 Design Advisor。
當 Design Advisor 用於為聯邦查詢推薦 MQT 時，選項 -r 沒有效果。選項 -r 指示 Design Advisor 為考慮的 MQT 使用擴展的統計信息。擴展的統計信息是使用采樣在 MQT 定義中引用的對象上收集得到的。因為不支持在昵稱上采樣，所以 Design Advisor 使用優化器評估的統計信息。
為 MQT 推薦的索引被設計來改善工作負載性能而非 MQT 刷新性能。
如果更新、插入或刪除未包含在指定的工作負載中，則不考慮更
新推薦的 REFRESH IMMEDIATE MQT 的性能影響。
故障診斷
關於查詢為什麼不使用 MQT 的提示和技巧
如果您的查詢未使用 MQT，而您認為它應該使用，那麼請檢查：
確保 CURRENT REFRESH AGE 特殊注冊表被設置為 ANY。這個特殊注冊表是在發起到數據庫的連接時用 DFT_REFRESH_AGE 數據庫配置參數的值初始化的。也可以使用命令 SET CURRENT REFRESH AGE ANY 來顯式設置它。甚至在刷新年齡是 0 時，也會為優化考慮緩存表（Control Center 工具創建的 MQT）。
確保為優化啟用了 MQT。如果在 MQT 的 CREATE TABLE 或 ALTER TABLE 語句期間指定了選項 ENABLE QUERY OPTIMIZATION，那麼在優化期間會考慮 MQT。
對於刷新延遲的 MQT，確保特殊注冊表 CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION 被設置為一個包含您的 MQT 種類的值。例如，如果考慮的 MQT 是一個刷新延遲的系統維護的 MQT，那麼這個特殊注冊表必須設置為 ALL 或 SYSTEM。這個特殊注冊表是用 DFT_MTTB_TYPES 數據庫配置參數的值初始化的。
確保 MQT 中引用的表是查詢中引用的表的一個子集。在有些情況下，WebSphere Information Integrator 可能選擇一個從比查詢更多的表選擇的 MQT。例如，當 MQT 中的兩個或多個表定義了引用完整性關系並且聯結謂詞在它們自己的主鍵 - 外鍵列上時，就會出現這種情況。
確保 MQT 應用的謂詞是查詢應用的謂詞的一個子集。
確保查詢是一個動態編譯的語句。不為靜態編譯的查詢考慮 MQT。
作為一個好的實踐，確保 runstats 已經運行在 MQT 及其索引上以更新統計信息，因為 WebSphere Information Integrator 使用一個基於成本的優化器為查詢生成訪問計劃。
如果您創建了許多 MQT（所有這些 MQT 都可以用於滿足查詢），那麼查詢的編譯時間會增加。許多備選 MQT 的存在可能會防止編譯器為該查詢選擇最好的 MQT。
如果查詢是在比創建 MQT 時更高的隔離級別執行，那麼將不會為查詢的執行考慮該 MQT。
使用 MQT 時應該記住的一些事情：
如果查詢在謂詞或選擇列表中具有一個函數模板，那麼該函數模板必須是物化查詢表的一部分。MQT 中不包含函數模板會導致在運行查詢並選擇 MQT 來滿足查詢時出現 "statement not supported" 錯誤。
使用 INSERT 語句填充並更新了用戶維護的 MQT 後，通過檢查解釋輸出確保相同的 MQT 不被用作插入操作的源。將 REFRESH AGE 設置為 0 或者將特殊注冊表 'SET CURRENT MAINTAINED TABLE TYPE FOR OPTIMIZATION' 更改為一個不包含用戶維護的 MQT 的值，將確保 MQT 本身不會用作 INSERT 語句的源。
緩存表類似於具有以下限制的 MQT，即緩存表只支持單個表上的非聚集查詢。當 WebSphere Information Integrator 和遠程數據源之間的復制不被支持時，用戶維護的 MQT 應該用於取代緩存表。
我們的試驗的性能結果
要在昵稱上測試 MQT 的性能，我使用適應於聯邦環境的部分 "TPC-H" 基准測試工作負載。
我們生成了 4GB TPC-H 數據，並把 TPC-H 表分在兩個單獨物理機器上的兩個數據庫中。一個數據庫包含表 PART、SUPPLIER、PARTSUPP、NATION 和 REGION，另一個數據庫包含包含表 LINEITEM、ORDERS 和 CUSTOMERS。兩個數據庫都在 AIX® V5.2 on 4-CPU 機器上運行 DB2 Universal Database™ Version 8.2 Fixpack 11。在第三個 AIX V5.2, 4-CPU 機器上，我們安裝了 WebSphere Information Integrator Version 8.2 Fixpack 11，並創建了一個聯邦數據庫。在聯邦數據庫中，我們定義了服務器來訪問這兩個 DB2 數據庫和昵稱，以從這些 DB2 數據庫映射 TPC-H 表。
圖 1. 我們的試驗的配置
下一步是為 22 TPC-H 查詢中的參數標志插入常量值，以模擬一個典型的工作負載並度量聯邦服務器上這些查詢的執行時間。
然後我們在這 22 個查詢上運行 Design Advisor 以推薦 MQT。使用下面這個命令運行 Design Advisor：
db2advis -d tpcdfed -i tpch_querIEs.sql -m M -o tpch_mqts.sql -u -b tpch_tblsp1 -k OFF.
|-------10--------20--------30--------40--------50--------60--------70--------80--------9|
|-------- XML error: The previous line is longer than the max of 90 characters ---------|
通過從將路由到這些 MQT 的 TPC-H 查詢添加一些本地謂詞，我們定制了推薦的 MQT。
例如，Design Advisor 為查詢 #5 推薦以下 MQT：
CREATE SUMMARY TABLE "TEST1 "."MQT2" AS (
SELECT Q8.C0 AS "C0", Q8.C1 AS "C1", Q8.C3 AS "C2", Q8.C2 AS "C3"
FROM TABLE( SELECT Q7.C0 AS "C0", SUM(Q7.C1) AS "C1", Q7.C3 AS "C2", Q7.C2 AS "C3"
FROM TABLE( SELECT Q2.N_NAME AS "C0",
(Q4.L_EXTENDEDPRICE *(+1.00000000000000E+000 - Q4.L_DISCOUNT))
AS "C1",
Q5.O_ORDERDATE AS "C2", Q1.R_NAME AS "C3"
FROM TPCD1.REGION AS Q1,TPCD1.NATION AS Q2,
TPCD1.SUPPLIER AS Q3, TPCD2.LINEITEM AS Q4,
TPCD2.ORDERS AS Q5, TPCD2.CUSTOMER AS Q6
WHERE (Q2.N_REGIONKEY = Q1.R_REGIONKEY) AND
(Q3.S_NATIONKEY = Q2.N_NATIONKEY)
AND (Q6.C_NATIONKEY = Q3.S_NATIONKEY) AND (Q4.L_SUPPKEY = Q3.S_SUPPKEY)
AND (Q5.O_ORDERKEY = Q4.L_ORDERKEY) AND (Q6.C_CUSTKEY = Q5.O_CUSTKEY)) AS Q7
GROUP BY Q7.C2, Q7.C3, Q7.C0) AS Q8)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
通過添加謂詞 R_NAME = 'ASIA' 以便新的 MQT 定義變成下面這樣，我們定制了這個 MQT。
CREATE SUMMARY TABLE "TEST1 "."MQT2" AS (
SELECT Q8.C0 AS "C0", Q8.C1 AS "C1", Q8.C3 AS "C2", Q8.C2 AS "C3"
FROM TABLE( SELECT Q7.C0 AS "C0", SUM(Q7.C1) AS "C1", Q7.C3 AS "C2", Q7.C2 AS "C3"
FROM TABLE( SELECT Q2.N_NAME AS "C0",
(Q4.L_EXTENDEDPRICE *(+1.00000000000000E+000 - Q4.L_DISCOUNT)) AS "C1",
Q5.O_ORDERDATE AS "C2", Q1.R_NAME AS "C3"
FROM TPCD1.REGION AS Q1,TPCD1.NATION AS Q2,
TPCD1.SUPPLIER AS Q3, TPCD2.LINEITEM AS Q4,
TPCD2.ORDERS AS Q5, TPCD2.CUSTOMER AS Q6
WHERE (Q2.N_REGIONKEY = Q1.R_REGIONKEY) AND
(Q3.S_NATIONKEY = Q2.N_NATIONKEY)
AND (Q6.C_NATIONKEY = Q3.S_NATIONKEY) AND (Q4.L_SUPPKEY = Q3.S_SUPPKEY)
AND (Q5.O_ORDERKEY = Q4.L_ORDERKEY) AND (Q6..C_CUSTKEY = Q5.O_CUSTKEY
AND (R_NAME = 'ASIA'))) AS Q7
GROUP BY Q7.C2, Q7.C3, Q7.C0) AS Q8)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
接下來，我們在聯邦數據庫中創建並刷新了 MQT，我們再次運行工作負載以度量將路由到 MQT 的查詢的執行時間。
下表總結了試驗的結果。Design Advisor 推薦 8 個 MQT，對應於下表所示的 8 個 TPC-H 查詢。第一列表示 TPC-H 查詢號。下兩列表示禁用 MQT 和啟用 MQT 時以秒為單位的查詢執行時間。第四列展示 MQT 引入的改善，即第二列和第三列之間的時間差異，第五列展示改善的百分比。最後一列列出我們對 Design Advisor 推薦的 MQT 所做的定制。請注意，盡管我們的工作負載被定義為每個查詢只使用一個 MQT，但是也可能使用多個 MQT 來回答一個查詢。MQT 也可以定義來回答多個查詢。
Query #
Time w/o MQT（秒）
Time w/ MQT（秒）
Improvement（秒）
Improvement%
MQT 定制
3
25
12
13
52%
C_MKTSEGMENT =
'BUILDING'
5
78
1
77
99%
R_NAME = 'ASIA'
9
8
2
6
75%
10
68
50
18
26%
L_RETURNFLAG = 'R'
12
10
7
3
30%
L_SHIPMODE IN
('MAIL','SHIP')
14
25
2
23
92%
L_SHIPDATE >= DATE
('1995-09-01')
18
205
1
204
99%
19
141
1
140
99%
l_SHIPMODE IN
('AIR', 'AIR REG') AND
L_SHIPINSTRUCT =
'DELIVER IN PERSON'
AND ( P_BRAND =
'BRAND#12' OR
P_BRAND = 'BRAND#23'
OR P_BRAND = 'BRAND#34')
TOTAL
560
76
464
83%
*請注意，這些時間只是提供來演示定義在遠程表上的 MQT 所能帶來的性能改善。它們不是來自兼容的 TPC-H 基准測試，並且不應該拿來與任何現有的正式結果相比較。
結束語
WebSphere Information Integrator 為戰略性信息集成框架提供基礎，這樣的框架有助於客戶將新的應用程序快速推向市場，從現有資產獲得更多的回報，並且可控制 IT 成本。隨著更多的客戶采用數據聯邦技術，客戶的下一技術焦點通常是獲得更高的性能，因為他們的查詢的復雜性在增加。
我們考慮物化查詢表 (MQT) 支持一個重要的選項來進一步優化聯邦服務器的性能。通過基於您的查詢工作負載識別一套適當的 MQT，您可以用預先計算的查詢結果定義和填充這些 MQT，並通過利用這些 MQT 允許您的查詢工作負載運行得更快。正如本文所演示的，有些情況下的改善是相當顯著的。
最好的是，您不需要修改應用程序就可以利用 MQT 的優勢。聯邦查詢編譯器作出一個基於成本的決策，並使得使用 MQT 的過程對用戶是透明的。我們確信，您會發覺該特性是聯邦環境中的一個強大工具。
