助力商業智慧的MQT

現在，商業智慧和資料倉儲產品，在給使用者業務帶來價值方面，變得越來越有力，越來越多樣化。這些產品的核心當然是使用者線上交易系統中收集的資料。而其中主要的一個步驟是將這些資料整合、分類、匯總以便提取出有用的資訊。典型的應用情境是，使用者會重複查詢同一個待用資料集。舉一個例子，使用者想要總結昨天或者上個星期的銷售情況或者某階段末（如月末或年末）的摘要報告，而下一階段線上資料還在不間斷積累。如果資料庫管理系統可以提供一種機制來事先為使用者歸納、摘要資料就太好了。通過建立和使用物化查詢表（Materialized query tables， MQTs），IBM DB2 for i5/OS為你實現了這個功能。

MQTs是在i5/OS的V5R3版本引入的，在V5R4版本做了增強。一個物化查詢表可以被看成是一個物化視圖或者一個自動匯總的表；通過預先執行一部分查詢並且儲存結果，它也可以用於顯著提高複雜查詢的效能。由於不再查詢一個或多個具體的表，而是查詢一個MQT，查詢最佳化工具不用要求終端使用者或程式修改SQL，就可以識別MQTs並且隱式地重寫查詢，以獲得更好的效能。在某些情況下，使用MQT的查詢效能會快好幾個數量級。

MQTs對於使用複雜串連與彙總的查詢尤其有益，因為這些查詢會讀取和處理大量資料。商業智慧和資料倉儲會分階段地載入資料，這是MQT特別適用的應用情境。按日、按周以及按月來處理資料的方式提供了一個天然的契機來重新整理或更新MQTs。在這種情況下，MQTs不需要與其對應的表保持資料同步，重新整理MQT只要作為已有日終，周終或月結的批處理操作一部分就可以了。

找到查詢語句

為了說明MQTs如何提高如多個使用者對同一組表查詢幾乎相同資訊這樣的查詢的效能，讓我們先來查看下效能可能會受益於MQT的SQL語句。例如，10個部門的經理都會在每周一早上查詢銷售趨勢的資料。這就會導致10組SQL分組查詢語句都查相同的資訊。每周一早上都運行這些的查詢是一件浪費時間及資源的事情。利用MQTs與查詢最佳化工具的重寫查詢（query-rewrite）功能，可以極大地縮短回應時間和減少資源使用，同時仍會完成各個部門的查詢。通過使用這樣一個方法——建立和匯入一個存有所有銷售資料，以年、周以及部門分組的MQT，隨後讓最佳化器處理這個MQT——10個查詢中的每個查詢都可以訪問MQT的預摘要資料，而不是去相應基表中讀取和處理詳細資料。終端使用者或應用程式不需要改變它們的查詢，最佳化器為其重寫查詢。此外，作為周末處理流程的一部分，MQT可以在那時被更新，準備好摘要資料集，以備周一早上的查詢。

為了在這樣的應用情境中確定一個潛在的SQL語句集，我們可以開啟System i導航器的SQL 查詢計劃緩衝查看器。SQL查詢計劃緩衝包含了所有當前的查詢計劃以及相關的運行態資訊。在System i導航器中按右鍵資料庫檔案夾，然後選擇“SQL計劃快取”->“顯示語句”，可以開啟這個視圖（如下圖1）。

圖1

在這種情況下，使用者感興趣的語句是使用者程式中已耗用時間最長的那些。為了找到它們，可以選擇“引用以下對象的語句”過濾器，並填上模式名稱和表中列名。通過重新整理得到SQL語句列表，並將它們按SQL執行次數進行排序，最終可以找到想要的查詢語句。使用者還可以根據已耗用時間這一列進行排序，這樣就可以看到從耗時最長到最短的語句列表了。在語句列表中，若干查詢都用到了同樣的幾個表，並且只有查詢的列和使用不同的查詢條件這樣的細微差別。基於上述兩點，我們就知道哪些語句是建立一個MQT的候選語句了。

使用者最感興趣的是運行頻率最高的語句。如下面的例子，這是一個經理用到的查詢語句：
SELECT YEAR, WEEK, DEPARTMENT, SUM(SALES), COUNT (DISTINCT ORDER_NO)
FROM ORDERS_TABLE
WHERE DEPARTMENT = 'ABC123'
GROUP BY YEAR, WEEK, DEPARTMENT
ORDER BY YEAR, WEEK, DEPARTMENT;

設計這個應用情境的時候，使用者已經知道關於這個查詢和潛在資料的一些詳細資料了：ORDERS_TABLE包含3年的1百萬條資料，每年52周，共有25個部門；資料如按YEAR，WEEK 以及 DEPARTMENT 分組，最多會有3900個不同的組。

在語句列表中選擇這個select語句，右擊選擇“使用SQL語句”，此時會開啟一個“運行SQL 指令碼”視窗，並顯示這個select語句。在“運行SQL 指令碼”工具列中選擇“VisualExpain”->“說明”，這樣就能看到這個語句的查詢最佳化計划了（如圖2）。

圖2

此時會進行表掃描並得到一個臨時的、獨立的雜湊表，而VE的運行結果就是顯示了對這個雜湊表的排序列表掃描。