DB2 物化查詢表

----start

    DB2 物化查詢表MQT（MATERIALIZED QUERY TABLES）儲存了一個查詢的結果，當我們查詢相關表時，DB2會自動決定是使用原表還是使用物化查詢表。當資料庫中有海量資料時，使用物化查詢表可以極大的提高查詢速度。但是，有一利就有一弊，維護物化查詢表也是相當耗時的。所以，物化查詢表廣泛應用在資料倉儲和海量數量的報表查詢中，這類查詢的特點是：資料量大、經常需要分組統計、資料不會頻繁變更。正因為這些特點，在這些場合中物化查詢表可以充分發揮它的優勢。

文法：<br />CREATE TABLE <table-name> AS<br /><select stmtement><br />DATA INITIALLY DEFERRED<br />REFRESH [DEFERRED | IMMEDIATE]<br />[ENABLE QUREY OPTIMIZATION | DISABLE QUREY OPTIMIZATION]<br />[MAINTAINED BY [SYSTEM | USER | FEDERATED_TOOOL]]</p><p>樣本：<br />CREATE TABLE emp_summary AS<br />(<br /> SELECT<br /> workdept<br /> ,COUNT(*) AS crows<br /> ,SUM(empno) AS sumno<br /> FROM<br /> employee<br /> GROUP BY workdept<br />)<br />DATA INITIALLY DEFERRED<br />REFRESH IMMEDIATE; 

定義了物化查詢表後，如果我們執行以下SQL，DB2最佳化器將使用MQT

select workdept,avg(empno) from employee group by workdept

DB2 最佳化器將上面的SQL轉化成下面這樣

select workdept,sumno/crows from emp_summary 

    在定義物化查詢表時，我們可以指定在原始表資料改變時，是立即重新整理物化查詢表（REFRESH IMMEDIATE）呢，還是延遲重新整理（REFRESH DEFERRED ）；我們還可以指定，在適當的時候，允許最佳化器使用物化查詢表（ENABLE QUREY OPTIMIZATION）呢，還是禁止使用（DISABLE QUREY OPTIMIZATION]）；我們還可以指定，物化查詢表是由系統維護（MAINTAINED BY SYSTEM）呢，還是由使用者維護（MAINTAINED BY USER）。

    如果我們將物化查詢表定義為延遲重新整理（REFRESH DEFERRED ），那麼在使用物化查詢表之前，我們必須使用REFRESH TABLE 語句重新整理它。如果定義為由使用者負責維護物化查詢表時，使用者可以對物化查詢表進行insert update delete 等操作，此時，物化查詢表將不能REFRESH了。

    維護物化查詢表是相當耗時的，為了提高維護效率，我們可以給延遲重新整理（REFRESH DEFERRED）的物化查詢表定義一個staging 表。staging 表用來對物化查詢表執行增量重新整理，當重新整理完成時，staging 表就會被刪除。對於上面定義的物化查詢表，我們可以定義如下staging 表

CREATE TABLE emp_summary_st<br />(<br /> workdept,<br /> crows,<br /> sumno,<br /> GLOBALTRANSID,<br /> GLOBALTRANSTIME<br />)FOR emp_summary PROPAGATE IMMEDIATE; 

    PROPAGATE IMMEDIATE 子句表示，原始表做出的任何更改，都將被累積在 staging 表中。GLOBALTRANSID表示每個被傳播的行對應的全域事務 ID）。 GLOBALTRANSTIME表示事務的時間戳記。taging 表建立後，處於檢查暫掛狀態，我們可以使用 SET INTEGRITY 語句將表設定為正常狀態，這時候，我們就可以使用staging 表來重新整理物化查詢表了。

SET INTEGRITY FOR emp_summary_st STAGING IMMEDIATE UNCHECKED;<br />REFRESH TABLE emp_summary; 

更多細節請參考DB2資訊中心。

 

----更多參見：DB2 SQL 精萃

----聲明：轉載請註明出處。

----last updated on 2010.1.21

----written by ShangBo on 2010.1.21

----end