db2建物化表讨论

来源：互联网发布：ssl模块编译进nginx 编辑：程序博客网时间：2024/06/05 20:05

由于应用有一个3表联查视图，造成查询3表行锁过多，引起性能下降。这两天考虑建物化表替换视图查询。
也是遇到了几个问题，现在晒晒已做参考。

一、先建一张简单的物化表

CREATE TABLE TEST1 AS (
SELECT A.REF_ID AS ID FROM PHOTO_REF AS A
)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE；

CREATE TABLE TEST1 AS (      SELECT A.REF_ID AS ID FROM PHOTO_REF AS A    )    DATA INITIALLY DEFERRED REFRESH  IMMEDIATE；

set integrity for test1 immediate checked not incremental

 set integrity for test1  immediate checked not incremental

1、首先REFRESH IMMEDIATE指定由系统维护的物化表，不允许对该物化表 insert、update 或 delete 操作。其中数据都是对底层表的更改（即 insert、update 或 delete 操作）来更新（系统触发）。
2、DATA INITIALLY DEFERRED
在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。就处于检查暂挂（check pending）状态。
3、 IMMEDIATE CHECKED 子句规定，根据用于定义该 MQT 的查询对数据进行检查，并刷新数据。NOT INCREMENTAL 子句规定对整个表进行完整性检查。

二、建一张3表联查的物化表
我的这个视图有一个基表ISSUE,它与另两表联查并UNION ALL成视图。我照着这个语法建物化表却失败了。

CREATE TABLE TEST2 AS(
SELECT
A.REF_ID AS ID,
A.PHOTO_NAME AS PHOTO_NAME,
C.ISSUE_ID AS ISSUE_ID
FROM
PHOTO_REF AS A,ISSUE AS C
UNION ALL
SELECT
B.PROCESS_ID AS ID,
B.PHOTO_NAME AS PHOTO_NAME,
C.ISSUE_ID AS ISSUE_ID
FROM
PHOTO_PROCESS AS B,ISSUE AS C
)
DATA INITIALLY DEFERRED REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY USER；

CREATE TABLE TEST2 AS(     SELECT        A.REF_ID AS ID,        A.PHOTO_NAME AS PHOTO_NAME,        C.ISSUE_ID AS ISSUE_ID     FROM         PHOTO_REF AS A,ISSUE AS C   UNION ALL     SELECT        B.PROCESS_ID AS ID,        B.PHOTO_NAME AS PHOTO_NAME,        C.ISSUE_ID AS ISSUE_ID     FROM        PHOTO_PROCESS AS B,ISSUE AS C    )    DATA INITIALLY DEFERRED REFRESH DEFERRED     ENABLE QUERY OPTIMIZATION    MAINTAINED BY USER；

set integrity for test2 materialized query immediate unchecked；

set integrity for test2 materialized query immediate unchecked；

1、REFRESH DEFERRED
因为是union表，系统无法确定唯一的基表，保证数据完整性。系统维护该物化表便不可能了。所以采用用户维护。
2、 MAINTAINED BY USER
而就因为我的物化表不是单表为基表，所以也不能MAINTAINED BY SYSTEM，用REFRESH TABLE 来更改值。所以采取 MAINTAINED BY USER，这样我将有完全insert、update 或 delete 操作权限。
3、MATERIALIZED QUERY IMMEDIATE UNCHECKED
该表将启用完整性检查，但是不必检查它是否违反了完整性约束，便可以使之脱离检查暂挂状态。

注意：我的两个联表中有LONG VARCHAR 字段，我建物化表时就报错。在网上查资料后知道，物化表不能建大字段。

三、建立触发器维护物化表
对于这个3表联查的物化表，我建了两组触发器处理基表和2个关联表insert、update、delete操作。

CREATE TRIGGER TEST2_REF_I_TRIG
AFTER INSERT ON DB2INST1.ISSUE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE REF_ID_S VARCHAR(30);
DECLARE PHOTO_NAME_S VARCHAR(90);
SET REF_ID_S =
(SELECT I.REF_ID FROM ISSUE AS I WHERE I.ISSUE_ID = N.ISSUE_ID);
SET PHOTO_NAME_S =
(SELECT I.PHOTO_NAME FROM PHOTO_REF AS I WHERE I.REF_ID = REF_ID_S);
INSERT INTO DB2INST1.TEST4(ID,PHOTO_NAME,ISSUE_ID) VALUES (REF_ID_S,PHOTO_NAME_S,N.ISSUE_ID);
END

CREATE TRIGGER TEST2_REF_I_TRIG        AFTER INSERT ON DB2INST1.ISSUE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL        BEGIN ATOMIC            DECLARE REF_ID_S VARCHAR(30);             DECLARE PHOTO_NAME_S VARCHAR(90);           SET REF_ID_S =                   (SELECT I.REF_ID FROM ISSUE AS I WHERE I.ISSUE_ID = N.ISSUE_ID);            SET PHOTO_NAME_S =                   (SELECT I.PHOTO_NAME FROM PHOTO_REF AS I WHERE I.REF_ID = REF_ID_S);           INSERT INTO DB2INST1.TEST4(ID,PHOTO_NAME,ISSUE_ID) VALUES (REF_ID_S,PHOTO_NAME_S,N.ISSUE_ID);         END

四、总结
由于涉及到公司代码保密，大部分数据匿名处理。数据库对象及存储非常复杂，吾将上下而求索。