db2建物化表讨论

andy632

由于应用有一个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、首先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；   
    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
     
     四、总结
       由于涉及到公司代码保密，大部分数据匿名处理。数据库对象及存储非常复杂，吾将上下而求索。