MQT用法小结
一、MQT常见类型比较表：_______________________________________________________________________MATERIALIZED QUERY TABLE ALLOWABLE ACTIONS ON TABLE------------------------------------------- --...显示全部
一、MQT常见类型比较表：
_______________________________________________________________________
MATERIALIZED QUERY TABLE ALLOWABLE ACTIONS ON TABLE
------------------------------------------- ------------------------------------------------------------------
REFRESH MAINTAINED BY REFRESH TABLE INSERT/UPDATE/DELETE
-------------- --------------------- ------------------------ ----------------------------------
DEFERRED SYSTEM YES NO
USER NO YES
IMMEDIATE SYSTEM YEST NO
______________________________________________________________________
二、系统维护的 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.创建系统维护的MQT
create table emp_mqt 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
2.解除表的检查暂挂(check pending)状态
set integrity for emp immediate checked not incremental
3.查询MQT的数据
select * from emp_mqt
三、用户维护的 MQT
这种物化查询表中的数据是由用户维护的。只有 REFRESH DEFERRED 物化查询表可以定义为 MAINTAINED BY USER。不能对用户维护的 MQT 发出 REFRESH TABLE 语句(用于系统维护的 MQT)。但是，用户维护的 MQT 却 允许对它们执行 insert、update 或 delete 操作。
1.创建用户维护的MQT
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
2.解除表的检查暂挂(check pending)状态
set integrity for ontario_1995_sales_team materialized query immediate unchecked
3.生成MQT数据
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
insert into ontario_1995_sales_team values('006900', 'RUSS', 'DYERS', 'D44', '1234', 'Ontario', 1995)
4.查询MQT的数据
select * from ontario_1995_sales_team
四、总结表
总结表是一种特殊类型的 MQT，它们的 fullselect 包含一个 GROUP BY 子句，该子句总结 fullselect 中所引用表中的数据。同样，DATA INITIALLY DEFERRED 子句的意思是，在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。REFRESH DEFERRED 子句的意思是，在任何时候都可以用 REFRESH TABLE 语句刷新该表中的数据。当这个 MQT 刚创建且还没有发出 REFRESH TABLE 语句的时候，对它的查询将返回一个错误。而执行了 REFRESH TABLE 语句之后，对它的查询可以成功运行。
1.创建总结表
create table sales_summary as (select sales_person, region, sum(sales) as total_sales from sales group by sales_person, region)
data initially deferred refresh deferred
2.刷新表的数据
refresh table sales_summary
3.查询表
select * from sales_summary
4.原表被插入新纪录后再查询总结表
insert into sales values ('06/28/2005', 'LEE', 'Ontario-South', 100) ;
refresh table sales_summary ;
select * from sales_summary ;
5.更新原表纪录后再查询总结表
update sales set sales = 50 where sales_date = '06/28/2005' and
sales_person = 'LEE'
and region = 'Ontario-South'
refresh table sales_summary
select * from sales_summary
6.删除原表纪录后再查询总结表
delete from sales where sales_date = '06/28/2005' and sales_person = 'LEE'
and region = 'Ontario-South'
refresh table sales_summary
select * from sales_summary
五、staging 表
如果 REFRESH DEFERRED MQT 有一个相关联的 staging 表，那么可以对其执行增量刷新。staging 表 收集更改，以便应用这些更改，使得 MQT 与它的底层表同步。可以使用 CREATE TABLE 语句创建一个 staging 表。然后，当 MQT 的底层表被修改时，变化就会传播过来，并立即被添加到 staging 表中。其思想是，使用 staging 表增量式地刷新 MQT，而不是从头开始重新生成 MQT。增量式维护这种方式可以显著提高性能。当刷新操作完成时，staging 表就会被删除。staging 表被创建之后，便处于悬挂(不一致)状态。在开始收集底层表上的更改之前，它必须脱离这种状态。为此，可以使用 SET INTEGRITY 语句。
1.创建STAGING表“emp_sumry_s”与总结表emp_sumry关联
CREATE TABLE emp_sumry AS
(SELECT workdept AS dept
,COUNT(*) AS #rows
,COUNT(salary) AS #sal
,SUM(salary) AS sum_sal
FROM employee emp
where salary >0
GROUP BY emp.workdept
)DATA INITIALLY DEFERRED REFRESH DEFERRED;
CREATE TABLE emp_sumry_s
(dept
,num_rows
,num_sal
,sum_sal
,GLOBALTRANSID
,GLOBALTRANSTIME
)FOR emp_sumry PROPAGATE IMMEDIATE;
SET INTEGRITY FOR emp_sumry IMMEDIATE CHECKED;
SET INTEGRITY FOR emp_sumry_s STAGING IMMEDIATE UNCHECKED;
db2 => select * from emp_sumry
DEPT #ROWS #SAL SUM_SAL
---- ----------- ----------- ---------------------------------
-- --------------------------------（由于字数限制，不能全不贴上，请大家谅解）
E21 6 6 282520.00
8 条记录已选择。
2.修改原表
insert into employee(EMPNO,FIRSTNME,LASTNAME,WORKDEPT,EDLEVEL,SALARY)values('15','15','15','A00',1,200000);
insert into employee(EMPNO,FIRSTNME,LASTNAME,WORKDEPT,EDLEVEL,SALARY)values('14','14','14','B01',1,200000);
UPDATE employee SET FIRSTNME='15ABC',SALARY=1 where FIRSTNME='15';
delete from employee where FIRSTNME='11';
emp_sumry的响应变化量记录在emp_sumry_s之中了
db2 => select * from emp_sumry_s
DEPT NUM_ROWS NUM_SAL SUM_SAL GLOBALTRANSID GLOBALTRANSTIME
---- ----------- ----------- --------------------------------- ----------------------------- ---------------------------------
A00 1 1 100000.00 x'000000000007809B' x'20091202142340692947000000'
B01 1 1 200000.00 x'00000000000780AD' x'20091202142640171950000000'
A00 1 1 200000.00 x'00000000000780B0' x'20091202142718221404000000'
A00 0 0 -199999.00 x'00000000000780BC' x'20091202142920584766000000'
4 rows。
3.将增量变化刷新到emp_sumry
REFRESH TABLE emp_sumry INCREMENTAL;
4.emp_sumry_s表为空，emp_sumry数据发生改变
db2 => select * from emp_sumry
DEPT #ROWS #SAL SUM_SAL
---- ----------- ----------- ---------------------------------
A00 9 9 654251.00
---------
8 rows。
六、Optimizer Options
1.CURRENT REFRESH AGE
SET CURRENT REFRESH AGE 0;
SET CURRENT REFRESH AGE = ANY;
SET CURRENT REFRESH AGE = 99999999999999;
UPDATE DATABASE CONFIGURATION USING dft_refresh_age ANY;
2.CURRENT QUERY OPTIMIZATION
SET CURRENT QUERY OPTIMIZATION = 7;
UPDATE DATABASE CONFIGURATION USING DFT_QUERYOPT 5;
3.CURRENT MAINTAINED TYPES
SET CURRENT MAINTAINED TYPES = ALL;
SET CURRENT MAINTAINED TABLE TYPES = SYSTEM;
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = USER, SYSTEM;
NOTE:如果CURRENT QUERY OPTIMIZATION SPECIAL REGISTER 设为5或大于5，当满足下面的情况时，DB2优化器会考虑使用MQT而不是基表
_______________________________________________________________________
物化查询表MQT 数据库 /进程的状态 DB2是否考虑使用MQT
------------------------------------------- ------------------------------------------------------------------
REFRESH MAINTAINED BY REFRESH-AGE ------
-------------- --------------------- ------------------------ ----------------------------------
IMMEDIATE SYSTEM -- YES
DEFERRED SYSTEM ANY YES
DEFERRED USER ANY YES
DEFERRED SYSTEM ANY YES
______________________________________________________________________
查询当前MQT相关的专用寄存器的值：
SELECT CURRENT REFRESH AGE AS age_ts,CURRENT MAINTAINED TYPES AS currt_mttd_tp,CURRENT QUERY OPTIMIZATION AS q_opt FROM sysibm.sysdummy1收起
_______________________________________________________________________
MATERIALIZED QUERY TABLE ALLOWABLE ACTIONS ON TABLE
------------------------------------------- ------------------------------------------------------------------
REFRESH MAINTAINED BY REFRESH TABLE INSERT/UPDATE/DELETE
-------------- --------------------- ------------------------ ----------------------------------
DEFERRED SYSTEM YES NO
USER NO YES
IMMEDIATE SYSTEM YEST NO
______________________________________________________________________
二、系统维护的 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.创建系统维护的MQT
create table emp_mqt 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
2.解除表的检查暂挂(check pending)状态
set integrity for emp immediate checked not incremental
3.查询MQT的数据
select * from emp_mqt
三、用户维护的 MQT
这种物化查询表中的数据是由用户维护的。只有 REFRESH DEFERRED 物化查询表可以定义为 MAINTAINED BY USER。不能对用户维护的 MQT 发出 REFRESH TABLE 语句(用于系统维护的 MQT)。但是，用户维护的 MQT 却 允许对它们执行 insert、update 或 delete 操作。
1.创建用户维护的MQT
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
2.解除表的检查暂挂(check pending)状态
set integrity for ontario_1995_sales_team materialized query immediate unchecked
3.生成MQT数据
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
insert into ontario_1995_sales_team values('006900', 'RUSS', 'DYERS', 'D44', '1234', 'Ontario', 1995)
4.查询MQT的数据
select * from ontario_1995_sales_team
四、总结表
总结表是一种特殊类型的 MQT，它们的 fullselect 包含一个 GROUP BY 子句，该子句总结 fullselect 中所引用表中的数据。同样，DATA INITIALLY DEFERRED 子句的意思是，在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。REFRESH DEFERRED 子句的意思是，在任何时候都可以用 REFRESH TABLE 语句刷新该表中的数据。当这个 MQT 刚创建且还没有发出 REFRESH TABLE 语句的时候，对它的查询将返回一个错误。而执行了 REFRESH TABLE 语句之后，对它的查询可以成功运行。
1.创建总结表
create table sales_summary as (select sales_person, region, sum(sales) as total_sales from sales group by sales_person, region)
data initially deferred refresh deferred
2.刷新表的数据
refresh table sales_summary
3.查询表
select * from sales_summary
4.原表被插入新纪录后再查询总结表
insert into sales values ('06/28/2005', 'LEE', 'Ontario-South', 100) ;
refresh table sales_summary ;
select * from sales_summary ;
5.更新原表纪录后再查询总结表
update sales set sales = 50 where sales_date = '06/28/2005' and
sales_person = 'LEE'
and region = 'Ontario-South'
refresh table sales_summary
select * from sales_summary
6.删除原表纪录后再查询总结表
delete from sales where sales_date = '06/28/2005' and sales_person = 'LEE'
and region = 'Ontario-South'
refresh table sales_summary
select * from sales_summary
五、staging 表
如果 REFRESH DEFERRED MQT 有一个相关联的 staging 表，那么可以对其执行增量刷新。staging 表 收集更改，以便应用这些更改，使得 MQT 与它的底层表同步。可以使用 CREATE TABLE 语句创建一个 staging 表。然后，当 MQT 的底层表被修改时，变化就会传播过来，并立即被添加到 staging 表中。其思想是，使用 staging 表增量式地刷新 MQT，而不是从头开始重新生成 MQT。增量式维护这种方式可以显著提高性能。当刷新操作完成时，staging 表就会被删除。staging 表被创建之后，便处于悬挂(不一致)状态。在开始收集底层表上的更改之前，它必须脱离这种状态。为此，可以使用 SET INTEGRITY 语句。
1.创建STAGING表“emp_sumry_s”与总结表emp_sumry关联
CREATE TABLE emp_sumry AS
(SELECT workdept AS dept
,COUNT(*) AS #rows
,COUNT(salary) AS #sal
,SUM(salary) AS sum_sal
FROM employee emp
where salary >0
GROUP BY emp.workdept
)DATA INITIALLY DEFERRED REFRESH DEFERRED;
CREATE TABLE emp_sumry_s
(dept
,num_rows
,num_sal
,sum_sal
,GLOBALTRANSID
,GLOBALTRANSTIME
)FOR emp_sumry PROPAGATE IMMEDIATE;
SET INTEGRITY FOR emp_sumry IMMEDIATE CHECKED;
SET INTEGRITY FOR emp_sumry_s STAGING IMMEDIATE UNCHECKED;
db2 => select * from emp_sumry
DEPT #ROWS #SAL SUM_SAL
---- ----------- ----------- ---------------------------------
-- --------------------------------（由于字数限制，不能全不贴上，请大家谅解）
E21 6 6 282520.00
8 条记录已选择。
2.修改原表
insert into employee(EMPNO,FIRSTNME,LASTNAME,WORKDEPT,EDLEVEL,SALARY)values('15','15','15','A00',1,200000);
insert into employee(EMPNO,FIRSTNME,LASTNAME,WORKDEPT,EDLEVEL,SALARY)values('14','14','14','B01',1,200000);
UPDATE employee SET FIRSTNME='15ABC',SALARY=1 where FIRSTNME='15';
delete from employee where FIRSTNME='11';
emp_sumry的响应变化量记录在emp_sumry_s之中了
db2 => select * from emp_sumry_s
DEPT NUM_ROWS NUM_SAL SUM_SAL GLOBALTRANSID GLOBALTRANSTIME
---- ----------- ----------- --------------------------------- ----------------------------- ---------------------------------
A00 1 1 100000.00 x'000000000007809B' x'20091202142340692947000000'
B01 1 1 200000.00 x'00000000000780AD' x'20091202142640171950000000'
A00 1 1 200000.00 x'00000000000780B0' x'20091202142718221404000000'
A00 0 0 -199999.00 x'00000000000780BC' x'20091202142920584766000000'
4 rows。
3.将增量变化刷新到emp_sumry
REFRESH TABLE emp_sumry INCREMENTAL;
4.emp_sumry_s表为空，emp_sumry数据发生改变
db2 => select * from emp_sumry
DEPT #ROWS #SAL SUM_SAL
---- ----------- ----------- ---------------------------------
A00 9 9 654251.00
---------
8 rows。
六、Optimizer Options
1.CURRENT REFRESH AGE
SET CURRENT REFRESH AGE 0;
SET CURRENT REFRESH AGE = ANY;
SET CURRENT REFRESH AGE = 99999999999999;
UPDATE DATABASE CONFIGURATION USING dft_refresh_age ANY;
2.CURRENT QUERY OPTIMIZATION
SET CURRENT QUERY OPTIMIZATION = 7;
UPDATE DATABASE CONFIGURATION USING DFT_QUERYOPT 5;
3.CURRENT MAINTAINED TYPES
SET CURRENT MAINTAINED TYPES = ALL;
SET CURRENT MAINTAINED TABLE TYPES = SYSTEM;
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = USER, SYSTEM;
NOTE:如果CURRENT QUERY OPTIMIZATION SPECIAL REGISTER 设为5或大于5，当满足下面的情况时，DB2优化器会考虑使用MQT而不是基表
_______________________________________________________________________
物化查询表MQT 数据库 /进程的状态 DB2是否考虑使用MQT
------------------------------------------- ------------------------------------------------------------------
REFRESH MAINTAINED BY REFRESH-AGE ------
-------------- --------------------- ------------------------ ----------------------------------
IMMEDIATE SYSTEM -- YES
DEFERRED SYSTEM ANY YES
DEFERRED USER ANY YES
DEFERRED SYSTEM ANY YES
______________________________________________________________________
查询当前MQT相关的专用寄存器的值：
SELECT CURRENT REFRESH AGE AS age_ts,CURRENT MAINTAINED TYPES AS currt_mttd_tp,CURRENT QUERY OPTIMIZATION AS q_opt FROM sysibm.sysdummy1收起