DB2 物化查询表以及其他常用表的介绍

文章主要描述的是DB2 物化查询表，同时也有对MQT、总结表(summary)和staging表的介绍，我们为了更好的对其进行解说，我们是以一些实用的例子展示如何创建和使用物化查询表。物化查询表(MQT)的定义是以一次查询的结果为基础的。

MQT 可以显著提高查询的性能。本文将介绍 MQT、总结表(summary)和 staging 表，并通过一些实用的例子展示如何创建和使用物化查询表。

物化查询表(MQT)是一种以一次查询的结果为基础定义的表。包含在物化查询表中的数据来自定义物化查询表时所基于的一个或多个表。而总结表(也称自动总结表，AST)对于 IBM® DB2® Universal Database™(UDB)for Linux、 UNIX® 和 Windows®(DB2 UDB)的用户来说应该感到比较熟悉，它们可以看作是特殊的 MQT。fullselect 是总结表定义的一部分，它包含一个 GROUP BY 子句，该子句总结 fullselect 中所引用表中的数据。

您可以将 MQT 看作一种物化的视图。视图和 MQT 都是基于一个查询来定义的。每当视图被引用时，视图所基于的查询便会运行。但是，MQT 实际上则是将查询结果保存为数据，您可以使用 MQT 中的这些数据，而不是使用底层表中的数据。

物化查询表可以显著提高查询的性能，尤其是提高复杂查询的性能。如果优化器确定查询或查询的一部分可以用一个 MQT 来解决，那么就会重写查询，以便利用 MQT。

MQT 可以在创建表时定义，或者定义为系统维护的 MQT，或者定义为用户维护的 MQT。下面的几个小节将介绍这两种类型的 MQT，另外再介绍总结表和 staging 表。后面的例子要求连接到 SAMPLE 数据库。如果您系统上还没有创建 SAMPLE 数据库，那么可以通过在命令行提示符下输入 db2sampl 命令来创建这个数据库。

系统维护的 MQT

这种物化查询表中的数据是由系统维护的。当创建这种类型的 MQT 时，可以指定表数据是 REFRESH IMMEDIATE 还是 REFRESH DEFERRED。通过 REFRESH 关键字可以指定如何维护数据。DEFERRED 的意思是，表中的数据可以在任何时候通过 REFRESH TABLE 语句来刷新。

不管是 REFRESH DEFERRED 还是 REFRESH IMMEDIATE 类型的系统维护的 MQT，对它们的 insert、update 或 delete 操作都是不允许的。但是，对于 REFRESH IMMEDIATE 类型的系统维护的 MQT，可以通过对底层表的更改(即 insert、update 或 delete 操作)来更新。

清单 1 展示了一个创建 REFRESH IMMEDIATE 类型的系统维护的 MQT 的例子。这个表名为 EMP，它基于 SAMPLE 数据库中的底层表 EMPLOYEE 和 DEPARTMENT。由于 REFRESH IMMEDIATE MQT 要求查询的 select 列表中引用的每个表中至少有一个惟一键，所以我们首先在 EMPLOYEE 表的 EMPNO 列上定义一个惟一性约束，另外还在 DEPARTMENT 表的 DEPTNO 列上定义一个惟一性约束。

DATA INITIALLY DEFERRED 子句的意思是，在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。MQT 被创建好之后，就处于检查暂挂(check pending)状态(请参阅 DB2 基础: 阐明表和表空间的状态)，在对它执行 SET INTEGRITY 语句之前，不能查询它。IMMEDIATE CHECKED 子句规定，根据用于定义该 MQT 的查询对数据进行检查，并刷新数据。

NOT INCREMENTAL 子句规定对整个表进行完整性检查。通过查询 EMP 物化查询表发现，它现在已经填入了数据。

清单 1. 创建由系统维护的 MQT

connect to sample   
...   
alter table employee add unique (empno)   
alter table department add unique (deptno)   
create table emp as (select e.empno, e.firstnme, e.lastname, e.phoneno, d.deptno,   
substr(d.deptname, 1, 12) as department, d.mgrno from employee e, department d   
where e.workdept = d.deptno)   
data initially deferred refresh immediate   
set integrity for emp immediate checked not incremental   
select * from emp   
EMPNO FIRSTNME LASTNAME PHONENO DEPTNO DEPARTMENT MGRNO   
------ ------------ --------------- ------- ------ ------------ ------   
000010 CHRISTINE HAAS 3978 A00 SPIFFY COMPU 000010   
000020 MICHAEL THOMPSON 3476 B01 PLANNING 000020   
000030 SALLY KWAN 4738 C01 INFORMATION 000030   
000050 JOHN GEYER 6789 E01 SUPPORT SERV 000050   
000060 IRVING STERN 6423 D11 MANUFACTURIN 000060   
000070 EVA PULASKI 7831 D21 ADMINISTRATI 000070   
000090 EILEEN HENDERSON 5498 E11 OPERATIONS 000090   
000100 THEODORE SPENSER 0972 E21 SOFTWARE SUP 000100   
000110 VINCENZO LUCCHESSI 3490 A00 SPIFFY COMPU 000010   
000120 SEAN O'CONNELL 2167 A00 SPIFFY COMPU 000010   
000130 DOLORES QUINTANA 4578 C01 INFORMATION 000030   
...   
000340 JASON GOUNOT 5698 E21 SOFTWARE SUP 000100   
32 record(s) selected.   
connect reset