db2 物化表

最新推荐文章于 2019-12-15 10:21:54 发布

pianzif

最新推荐文章于 2019-12-15 10:21:54 发布

阅读量2.7k

点赞数 1

分类专栏： DB2@DEV 文章标签： db2 物化表

DB2@DEV 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

1.DB2物化查询表与视图的区别

视图是一个查询语句。物化查询表是一个查询的结果集。

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

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

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

2.创建DB2物化查询表

物化查询表可以在创建临时表的时候定义，分为2种。

·系统维护的MQT

系统维护的MQT分为2种，一种是refresh immediate，另一种是refresh deferred。前者在你修改了底层表后能自动刷新，后者可以随时通过
refresh table 语句来进行刷新。对于refresh immediate类型的MQT在select时必须包含所FROM的每个表的至少一个唯一键。假如不包含唯一键，则会报错：SQLSTATE:428EC （为具体化查询表，指定的全查询无效）。
PS：增加唯一性约束的语句是：
alter table dm_gj add unique(gj_dm)

不管是 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 列上定义一个惟一性约束。DA

TA 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
　　　...
　　000340 JASON GOUNOT 5698 E21 SOFTWARE SUP 000100
　　32 record(s) selected.
　　connect reset

创建MQT语句：
create table topicis.mqt_test as
(select a.gj_dm,a.gj_mc from topicis.dm_gj a) data initially deferred refresh immediate

data initially deferred refresh immediate 翻译成中文的意思是：数据初始化延后，立即刷新。

在creat table 之后，MQT处于暂挂状态，还需要填充数据方能用于查询：
语句：
set integrity for topicis.mqt_test immediate checked not incremental

set integrity 使完整
immediate cheched 根据定义的SELECT对数据进行检查，并刷新
not incremental 对整个表进行完整性检查

set之后，该MQT才可以用于查询

需要注意的是对于系统维护的物化查询表，进行insert，update，delete都是不允许的。你可以通过对底层表的insert,update,delete来改变MQT的数据。

用户维护的MQT

这种物化查询表中的数据是由用户维护的。只有 REFRESH DEFERRED 物化查询表可以定义为 MAINTAINED BY USER。不能对用户维护的 MQT 发出 REFRESH TABLE 语句(用于系统维护的 MQT)。但是，用户维护的 MQT 却允许对它们执行 insert、update 或 delete 操作。

用户维护的MQT与系统维护的区别在于，系统维护的MQT可以使用refresh table命令来刷新数据，用户维护的MQT不可以。用户维护的MQT可以直接insert，update，delete，系统维护的MQT不可以。

清单 2 展示了一个创建 REFRESH DEFERRED 类型的用户维护的 MQT 的例子。这个表名为 ON

TARIO_1995_SALES_TEAM，它基于数据库 SAMPLE 中的底层表 EMPLOYEE 和 SALES。同样，DATA INITIALLY DEFERRED 子句的意思是，在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。MQT 被创建之后，便处于检查暂挂状态(请参阅 DB2 基础: 阐明表和表空间的状态)，在对它执行 SET INTEGRITY 语句之前，不能查询它。MATERIALIZED QUERY IMMEDIATE UNCHECKED 子句规定，该表将启用完整性检查，但是不必检查它是否违反了完整性约束，便可以使之脱离检查暂挂状态。

　　接下来，为了填充数据到 MQT 中，我们将导入从 EMPLOYEE 和 SALES 表中导出的数据。用于导出数据的查询与用于定义 MQT 的查询是一致的。然后，我们将插入另外一条记录到 ONTARIO_1995_SALES_TEAM 表中。

　　通过查询 ONTARIO_1995_SALES_TEAM 物化查询表发现，它现在已经填入了刚才导入的和插入的数据，这表明用户维护的 MQT 的确可以直接被修改。

　　清单 2. 创建由用户维护的 MQT

connect to sample
　　...
　　 create table ontario_1995_sales_team as (select distinct e.empno, e.firstnme,
　　e.lastname, e.workdept, e.phoneno, 'Ontario' as region,
　　year(s.sales_date) as year from employee e, sales s
　　where e.lastname = s.sales_person and year(s.sales_date) = 1995
　　and left(s.region, 3) = 'Ont')
　　data initially deferred refresh deferred maintained by user
　　set integrity for ontario_1995_sales_team materialized query immediate
　　unchecked
　　export to ontario_1995_sales_team.del of del
　　 select distinct e.empno, e.firstnme, e.lastname, e.workdept, e.phoneno,
　　'Ontario' as region, year(s.sales_date) as year from employee e,
　　sales s
　　where e.lastname = s.sales_person and year(s.sales_date) = 1995
　　and left(s.region, 3) = 'Ont'
　　...
　　Number of rows exported: 2
　　import from ontario_1995_sales_team.del of del insert into
　　ontario_1995_sales_team
　　...
　　Number of rows committed = 2
　　insert into ontario_1995_sales_team
　　values ('006900', 'RUSS', 'DYERS', 'D44', '1234', 'Ontario', 1995)
　　select * from ontario_1995_sales_team
　　EMPNO FIRSTNME LASTNAME WORKDEPT PHONENO REGION YEAR
　　 ------ ------------ --------------- -------- ------- ------- -----------
　　000110 VINCENZO LUCCHESSI A00 3490 Ontario 1995
　　000330 WING LEE E21 2103 Ontario 1995
　　006900 RUSS DYERS D44 1234 Ontario 1995
　　3 record(s) selected.
　　connect reset

创建MQT的语句：
create table topicis.mqt_test as
(select a.gj_dm ,a.gj_mc from topicis.dm_gj a) data initially deferred refresh deferred maintained by user
对于用户维护的MQT来说，必然是refresh deferred 的。maintained 这个单词的意思是保持，维护.maintained by user 即为用户维护。

和系统维护的MQT一样，还需要set integrity
set integrity for topicis.mqt_test materialized query immediate unchecked

materialized：物化
immediate unchecked 表明对该表不进行完整性约束的检查。
此时，若对MQT进行查询，你会发现里面的数据为0条。因为此时该MQT里还没有数据。你需要手工插入数据，或者通过刚才的SELECT语句从底层表export，然后再import进来。
所以总体来说，用户维护的DB2物化查询表相当于基于原来的几个底层表创建一个新的实体表。

---

1、创建结果表

　　create table new_table_name as

　　(select * from table_name) definition only;

　　2、创建物化查询表（MQT）

　　create table new_table_name as

　　(select * from table_name)

　　data initially deferred refresh deferred;

　　refresh table new_table_name;

　　注意：物化表类似一个查询，没有真正形成表，类型显示为Query。但它完全可以当表来用。

　　3、复制表结构

　　create table new_table_name like table_name;