db2建物化表讨论

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

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

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

1、首先[b]REFRESH IMMEDIATE[/b]指定由系统维护的物化表，不允许对该物化表 insert、update 或 delete 操作。其中数据都是对底层表的更改（即 insert、update 或 delete 操作）来更新（系统触发）。
2、[b]DATA INITIALLY DEFERRED [/b]
在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。就处于检查暂挂（check pending）状态。
3、 [b]IMMEDIATE CHECKED [/b]子句规定，根据用于定义该 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；

set integrity for test2 materialized query immediate unchecked；

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

[b]注意：[/b]我的两个联表中有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

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