db2建物化表讨论

LXY3800

由于应用有一个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

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