【挑战问题】DB2物化查询表(MQT)经验点滴 欢迎讨论！

7
将具体化查询表创建为 REFRESH DEFERRED，或者

更正 CREATE TABLE 语句以确保所有 GROUP BY 项在选择列表中

更正 GROUP BY 子句以确保没有重复分组集合

在选择列表中除去可空列 C 或添加 GROUPING(C)

更正 CREATE TABLE 语句以确保查询中引用的每个表中的至少一个

唯一键出现在选择列表中

8
将具体化查询表创建为 REFRESH DEFERRED，或者

将 COUNT(*) 或 COUNT_BIG(*) 添加至选择列表或除去 GROUP BY

子句

将 COUNT(*) 添加至选择列表或除去 SUM(C) 或将列 C 改变为不可

空

除去不受支持的聚集函数或将它们替换为受支持的函数

除去 HAVING 子句

更正 CREATE TABLE 语句以确保 GROUP BY 子句包含所有分区键列

9
将具体化查询表创建为 REFRESH DEFERRED，或者将表定义更改为子查

询或 GROUP BY 的输入表表达式中的 UNION ALL。

10
将具体化查询表创建为 REFRESH DEFERRED，或者使用内连接（不使用

显式 INNER JOIN 语法）。

11
将具体化查询表创建为 REFRESH DEFERRED，或从 UNION ALL 或 JOIN

的输入表中除去聚集函数。

12
将具体化查询表创建为 REFRESH DEFERRED 或降低总行宽或列数，或者

创建具有适当页大小的系统临时表空间。

Reference

•
利用 DB2 说明工具深入理解物化查询表（MQT）刷新机制

•
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1005haoqy/index.html

•
DB2 中 MQT 的匹配原理及使用技巧

•
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1003lihf/

•
使用实例化的查询表加速 DB2 UDB EEE 中的查询

•
http://www.ibm.com/developerworks/cn/data/library/techarticles/0208kuznetsov/index.html

•
DB2 分区特性

•
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0608mcinerney/?ca=drs-tp3706

•
Automatic table and index reorganization in DB2 for Linux, UNIX, and Windows

•
http://www.ibm.com/developerworks/data/library/techarticle/dm-0707tang/index.html

创建登台表时可能会发生此错误。在这种情况下，该错误适用于在与登台表相关

联的具体化查询表的定义中使用的查询。

因为该语句违反下列原因码所指示的限制，所以不能对其进行处理：

1
每个选择列表元素必须有名称。

2
全查询不能引用下列任何对象类型：

具体化查询表

登台表

声明的全局临时表

类型表

系统目录表

违反任何具体化查询表限制的视图

受保护的表

使用 CREATE NICKNAME 或 ALTER NICKNAME 语句中的 DISALLOW

CACHING 子句创建的昵称

直接或间接依赖于受保护表的视图

3
全查询不能包含下列数据类型的任何列引用或表达式：

LOB、LONG、DATALINK、XML、引用、用户定义的结构化类型或基于

这些数据类型的任何单值类型

4
全查询不能包含符合以下条件的任何列引用、表达式或函数：

取决于数据的物理特征，例如 DBPARTITIONNUM、HASHEDVALUE、RID

_BIT 和 RID

取决于对数据所作的更改，例如行更改表达式或 ROW CHANGE

TIMESTAMP 列

被定义为 EXTERNAL ACTION

被定义为 LANGUAGE SQL、CONTAINS SQL、READS SQL DATA 或

MODIFIES SQL DATA

5
当指定 REPLICATED 时，下列限制适用：

不允许聚集函数和 GROUP BY 子句

具体化查询表只能引用单个表；即，它不能包括连接、并集或子查询

不能指定 PARTITIONING KEY 子句

6
指定 REFRESH IMMEDIATE 时，全查询不能包含：

对昵称的引用

SELECT DISTINCT

对专用寄存器的引用

对全局变量的引用

不具确定性的函数

OLAP 函数、抽样函数和文本函数

使用聚集函数的结果的任何表达式

没有还包含 GROUP BY 子句的全查询的聚集函数

递归公共表表达式

子查询

7
当指定 REFRESH IMMEDIATE 时：

具体化查询表不能包含重复行

当指定 GROUP BY 子句时，所有 GROUP BY 荐必须包括在选择列表中

当指定包含 GROUPING SETS、CUBE 或 ROLLUP 的 GROUP BY 子句时

，不能重复任何分组集合，并且如果 C 是出现在 GROUPING SETS、

CUBE 或 ROLLUP 中的可空 GROUP BY 项，那么 GROUPING(C) 必须

出现在选择列表中

如果没有 GROUP BY 子句，那么每个基础表必须至少定义一个唯一

键，而且这些键的所有列必须出现在具体化查询表定义的选择列表

中

8
如果指定 REFRESH IMMEDIATE，那么在全查询包含 GROUP BY 子句时下

列限制适用：

选择列表必须包含 COUNT(*) 或 COUNT_BIG(*)

对于每个可空列 C，如果选择列表包含 SUM(C)，那么还需要

COUNT(C)

必须出现至少下列其中一个聚集函数（并且没有其他聚集函数）：

SUM()、COUNT()、COUNT_BIG() 或 GROUPING()

不能指定 HAVING 子句

在分区数据库环境中，GROUP BY 列必须包含具体化查询表的分区键

不允许聚集函数的嵌套

9
如果指定 REFRESH IMMEDIATE，那么全查询必须是子查询，但存在一个

例外，就是 UNION ALL 在 GROUP BY 的输入表表达式中受支持。

10
如果指定了 REFRESH IMMEDIATE 并且 FROM 子句引用了多个表，那么

只支持内连接（不使用显式 INNER JOIN 语法）。

11
如果指定 REFRESH IMMEDIATE，那么 UNION ALL 或 JOIN 的输入表表

达式不能包含聚集函数。

12
此具体化查询表的增量备份维护需要系统临时表，该表的行宽或列数超

出数据库上当前可用的最大系统临时表空间可以容纳的限制。

用户响应:

更改 CREATE TABLE 语句中的全查询，以使其符合基于具体化查询表选项和该具

体化查询表是否是复制的规则。

与原因码对应的操作是：

1
更正 CREATE TABLE 语句以确保所有元素都有名称（可使用 AS 子句

来命名表达式或对具体化查询表定义的列列表中的所有列显式命名）。

2
更正 CREATE TABLE 语句以确保只引用受支持的对象。

3
更正 CREATE TABLE 语句以确保只引用受支持的列或表达式类型。

4
更正 CREATE TABLE 语句以确保只引用受支持的列、表达式或函数。

5
创建未复制的具体化查询表，或更正 CREATE TABLE 语句以确保该查询

引用单个表而且不包含子查询、聚集或 PARTITIONING 子句。

6
将具体化查询表创建为 REFRESH DEFERRED，或者

除去昵称引用

除去 DISTINCT

除去专用寄存器

除去非确定性函数或将其替换为确定性函数

除去所有 OLAP、抽样函数和文本函数

从表达式中除去聚集函数或将表达式更改为对聚集函数的简单引用

除去聚集函数或添加 GROUP BY 子句

更正 CREATE TABLE 语句以确保未引用递归公共表表达式

除去子查询

使用延迟刷新的方式，当对基表进行任何 insert/update/delete 等操作时，MQT 中的数据没有进行相应的刷新，而是等到用户（DBA）阶段性的执行刷新命令时才进行刷新。这种方式适合用在绝大部分时间都是只读的应用系统中，或者数据的更新只发生维护窗口时间。

MQT 刷新方式还可以从数据刷新范围的角度分成两类，一类是完全刷新，一类是增量刷新。

完全刷新方式是将基表中的所有数据重新进行计算和处理从而更新 MQT 的数据。这种方式的缺点是 当基表的数据量大而且 MQT 定义复杂时，刷新过程可能会比较慢。

增量更新方式是针对 insert/update/delete 等操作更新过的基表中的那一部分数据，对 MQT 中相应数据进行刷新，而不需要访问基表中所有数据。

将两种分类方法结合在一起讨论，即时刷新方式必然是增量刷新方式，延迟刷新方式采用的是完全刷新，但不是所有的延迟刷新方式都是完全刷新，有一种特殊的延迟刷新即“使用登台表（staging table）的延迟刷新”采用的是增量更新。当 MQT 的基表被修改时，变化就会传播过来，并立即被添加到 staging 表中，这样就可以利用 staging 表增量刷新，而不是从头开始重新生成 MQT，从而可以显著提高性能。当刷新操作完成时，staging 表中的数据就会被删除。

创建延迟刷新的 MQT 并创建登台表

FULL REFRESH will re-calculate and process all data of
base table, then update MQT data.

Disadvantage: Refresh Processing slowly while there are huge records in Base table.

INCREMENTIAL REFRESH: That only focus the change data of th insert/update/delete etc operation, then refresh related data, not need access all records of base table.

REFRESH IMMEDIATE MQTs affect performance of queries just like indexes do.

They:

Speed up the performance of relevant select statements.

Are automatically chosen by the optimizer whenever it makes sense.

Can degrade the performance of insert, update and delete statements.

Cannot be updated directly.

May occupy considerable disk space.

May have exclusive locks held during updates of their base tables.

On the other hand, REFRESH DEFRERRED MQTs have no effect on the performance of insert, update and delete operations. as a rule of thumb, use REFRESH IMMEDIATE MQTS in moderation to optimize frequently run queries in which current data is import.

对具体化查询表 "<表名>" 指定的全查询无效。原因码 =

"<原因码>"。

说明:

具体化查询表定义中包含与全查询的内容相关的特定规则。某些规则基于具体化

查询表选项（REFRESH DEFERRED 或 REFRESH IMMEDIATE），而另一些规则基于该

表是否是复制的。返回此状态的 CREATE TABLE 语句中的全查询至少违反了 SQL

Reference 中描述的其中一条规则。

查询 SYSCAT.TABDEP 系统编目视图，以查看 MQT 在其他数据库对象上的依赖关系

我们看到了一个物化查询表，其定义基于某个查询的结果，可以将它看作一种物化视图。MQT 很重要，因为它们可以显著减少复杂查询的响应时间。

分为两种类型，

一种是系统维护的 MQT， ---------广泛的应用

一种是用户维护的 MQT，

系统维护的 MQT 的刷新方式，根据刷新时间的不同可以分为两种，

一种是 REFRESH IMMEDIATE 也就是即时刷新，

一种是 REFRESH DEFERRED 也就是延迟刷新。

使用即时刷新的方式，当对基表进行任何 insert/update/delete 等操作时，MQT 中的数据也自动的进行相应的刷新，其优点是能保证 MQT 中的数据总是最新的，但是由于对基表进行操作的同时还需要维护 MQT，负载有所增加。

如果 REFRESH DEFERRED MQT 有一个相关联的 staging 表，那么可以对其执行增量刷新。staging 表 收集更改，以便应用这些更改，使得 MQT 与它的底层表同步。可以使用 CREATE TABLE 语句创建一个 staging 表。然后，当 MQT 的底层表被修改时，变化就会传播过来，并立即被添加到 staging 表中。其思想是，使用 staging 表增量式地刷新 MQT，而不是从头开始重新生成 MQT。增量式维护这种方式可以显著提高性能。当刷新操作完成时，staging 表就会被删除。

staging 表被创建之后，便处于悬挂（不一致）状态。在开始收集底层表上的更改之前，它必须脱离这种状态。为此，可以使用 SET INTEGRITY 语句。

清单 4 展示了一个使用有关联总结表的 staging 表的例子。总结表名为 EMP_SUMMARY，它基于 SAMPLE 数据库中的底层表 EMPLOYEE。您应该还记得，DATA INITIALLY DEFERRED 子句的意思是，在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。而 REFRESH DEFERRED 子句的意思是，在任何时候都可以使用 REFRESH TABLE 语句刷新该表中的数据。staging 表名为 EMP_SUMMARY_S，它与总结表 EMP_SUMMARY 相关联。PROPAGATE IMMEDIATE 子句规定，在 insert、update 或 delete 操作中对底层表做出的任何更改，都将被累积在 staging 表中。对于这两个表，都发出 SET INTEGRITY 语句，以便使它们都脱离悬挂状态。

不出所料，此时对总结表的查询没有返回任何数据。REFRESH TABLE 语句返回一条警告，提示说 "integrity of non-incremental data remains unverified"。这也不值得惊讶。再次查询总结表，仍然没有返回任何数据。但是，当我们插入新的一行数据到底层的 EMPLOYEE 表之后，再次查询 staging 表 EMP_SUMMARY_S 便返回一行记录，这行数据与刚才插入的数据是一致的。staging 表中有三个列与其底层总结表中的三列相同，另外还有两个由系统使用的列：GLOBALTRANSID（每个被传播的行对应的全局事务 ID）和 GLOBALTRANSTIME（事务的时间戳）。再次查询总结表，又是没有返回数据，但是当执行了 REFRESH TABLE 语句之后，查询得以成功运行。