DB2 物化查询表

----start

    DB2 物化查询表MQT（MATERIALIZED QUERY TABLES）存储了一个查询的结果，当我们查询相关表时，DB2会自动决定是使用原表还是使用物化查询表。当数据库中有海量数据时，使用物化查询表可以极大的提高查询速度。但是，有一利就有一弊，维护物化查询表也是相当耗时的。所以，物化查询表广泛应用在数据仓库和海量数量的报表查询中，这类查询的特点是：数据量大、经常需要分组统计、数据不会频繁变更。正因为这些特点，在这些场合中物化查询表可以充分发挥它的优势。

语法： CREATE TABLE <table-name> AS <select stmtement> DATA INITIALLY DEFERRED REFRESH [DEFERRED | IMMEDIATE] [ENABLE QUREY OPTIMIZATION | DISABLE QUREY OPTIMIZATION] [MAINTAINED BY [SYSTEM | USER | FEDERATED_TOOOL]] 示例： CREATE TABLE emp_summary AS ( SELECT workdept ,COUNT(*) AS crows ,SUM(empno) AS sumno FROM employee GROUP BY workdept ) DATA INITIALLY DEFERRED 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 ( workdept, crows, sumno, GLOBALTRANSID, GLOBALTRANSTIME )FOR emp_summary PROPAGATE IMMEDIATE; 

    PROPAGATE IMMEDIATE 子句表示，原始表做出的任何更改，都将被累积在 staging 表中。GLOBALTRANSID表示每个被传播的行对应的全局事务 ID）。 GLOBALTRANSTIME表示事务的时间戳。taging 表创建后，处于检查暂挂状态，我们可以使用 SET INTEGRITY 语句将表设置为正常状态，这时候，我们就可以使用staging 表来刷新物化查询表了。

SET INTEGRITY FOR emp_summary_st STAGING IMMEDIATE UNCHECKED; REFRESH TABLE emp_summary; 

更多细节请参考DB2信息中心。

 

----更多参见：DB2 SQL 精萃

----声明：转载请注明出处。

----last updated on 2010.1.21

----written by ShangBo on 2010.1.21

----end