自动化创建物化视图V1

需求背景： app 数据库类型OLTP 主要是数据仓库纬度数据表；pos数据库类型是OLAT 做数据分析用，大量门店销售数据，进行物流仓库货物调拨，通过系统进行分获配货调拨；

step 1.    删除所有pos 数据库正在进行的job任务
 

BEGIN
  FOR ITEM IN (SELECT *
                 FROM (select o.* from USER_JOBS o where o.WHAT like 'dbms_refresh.refresh%' or o.WHAT like '%dbms_ddl.analyze_object%' )) LOOP
    DBMS_JOB.REMOVE(ITEM.JOB);
  END LOOP;
  COMMIT;
END;
/

step2  app 数据库中重建物化视图日志

​
BEGIN
  FOR ITEM IN (SELECT A.*,
                      CASE
                        WHEN B.CONSTRAINT_NAME IS NULL THEN
                         'NO'
                        ELSE
                         'YES'
                      END HAS_PRIMARYKEY
                 FROM USER_MVIEW_LOGS A
                 LEFT JOIN (SELECT *
                             FROM USER_CONSTRAINTS
                            WHERE CONSTRAINT_TYPE = 'P') B
                   ON A.MASTER = B.TABLE_NAME) LOOP
    EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW LOG ON ' || ITEM.MASTER ;
    EXECUTE IMMEDIATE 'CREATE MATERIALIZED VIEW LOG ON ' || ITEM.MASTER ||
                         ' WITH ' || CASE WHEN ITEM.HAS_PRIMARYKEY = 'YES' THEN
                         'PRIMARY KEY' ELSE 'ROWID' END;
  END LOOP;
END;


​

step 3. 在重建物化视图之前，查询出pos 数据物化视图的索引，有两种方法，一是通过集合提取，另一种方法通过get_ddl 包提取索引的定义sql，方法二优于方法一，区别是索引中字段的顺序不会打乱，这样不会改变原有索引版本

DECLARE
  TYPE REC_NORMAL IS RECORD(
    COLUMN_NAME     USER_IND_COLUMNS.COLUMN_NAME%TYPE,
    COLUMN_POSITION USER_IND_COLUMNS.COLUMN_POSITION%TYPE);
  TYPE NNT_NORMAL IS TABLE OF REC_NORMAL;
  L_NNT_NORMAL1 NNT_NORMAL := NNT_NORMAL();
  L_NNT_NORMAL2 NNT_NORMAL := NNT_NORMAL();
  L_NNT_NORMAL3 NNT_NORMAL := NNT_NORMAL();
  L_NNT         NNT_NORMAL := NNT_NORMAL();
  L_SQL         VARCHAR2(3000);


  TYPE REC_EXP IS RECORD(
    COLUMN_EXPRESSION USER_IND_EXPRESSIONS.COLUMN_EXPRESSION%TYPE,
    COLUMN_POSITION   USER_IND_EXPRESSIONS.COLUMN_POSITION%TYPE);
  TYPE NNT_EXP IS TABLE OF REC_EXP;
  L_NNT_EXP    NNT_EXP := NNT_EXP();
  L_REC_NORMAL REC_NORMAL;
BEGIN
  FOR ITEM IN (SELECT UI.INDEX_NAME, UI.TABLE_NAME
                 FROM USER_INDEXES UI
                WHERE UI.TABLE_NAME IN
                      (SELECT UW.MVIEW_NAME
                         FROM USER_MVIEWS UW
                        WHERE UW.MASTER_LINK IS NOT NULL)
                  AND NOT EXISTS
                (SELECT 1
                         FROM USER_CONSTRAINTS UC
                        WHERE UI.TABLE_NAME = UC.TABLE_NAME
                          AND UI.INDEX_NAME = UC.
                        CONSTRAINT_NAME
                          AND UC.CONSTRAINT_TYPE = 'P')
                  AND UI.INDEX_NAME NOT LIKE 'I_SNAP$%') LOOP
    L_NNT_NORMAL1 := NNT_NORMAL();
    L_NNT_NORMAL2 := NNT_NORMAL();
    L_NNT_NORMAL3 := NNT_NORMAL();
    L_NNT_EXP     := NNT_EXP();
    L_SQL         := 'CREATE INDEX ' || ITEM.INDEX_NAME || ' ON ' ||
                     ITEM.TABLE_NAME || '(';
    SELECT UIC.COLUMN_NAME, UIC.COLUMN_POSITION
      BULK COLLECT
      INTO L_NNT_NORMAL1
      FROM USER_IND_COLUMNS UIC
     WHERE UIC.INDEX_NAME = ITEM.INDEX_NAME 
       AND NOT EXISTS
     (SELECT 1
              FROM USER_IND_EXPRESSIONS UIE
             WHERE UIC.INDEX_NAME = UIE.INDEX_NAME
               AND UIC.COLUMN_POSITION = UIE.COLUMN_POSITION);
 
    SELECT UIE.COLUMN_EXPRESSION, UIE.COLUMN_POSITION
      BULK COLLECT
      INTO L_NNT_EXP
      FROM USER_IND_EXPRESSIONS UIE
     WHERE UIE.INDEX_NAME = ITEM.INDEX_NAME;
 
    IF L_NNT_EXP.COUNT > 0 THEN
      FOR I IN L_NNT_EXP.FIRST .. L_NNT_EXP.LAST LOOP
        L_REC_NORMAL.COLUMN_NAME     := SUBSTR(L_NNT_EXP(I)
                                               .COLUMN_EXPRESSION,
                                               1,
                                               4000);
        L_REC_NORMAL.COLUMN_POSITION := L_NNT_EXP(I).COLUMN_POSITION;
        L_NNT_NORMAL2.EXTEND();
        L_NNT_NORMAL2(L_NNT_NORMAL2.LAST) := L_REC_NORMAL;
      END LOOP;
    END IF;
    ----L_NNT_NORMAL3=L_NNT_NORMAL1+L_NNT_NORMAL2
    L_NNT_NORMAL3 := L_NNT_NORMAL1 MULTISET UNION L_NNT_NORMAL2;
 
    ----GENERATE INDEX COLUMN LIST FROM L_NNT_NORMAL3
    FOR J IN 1 .. L_NNT_NORMAL3.COUNT LOOP
   
      FOR I IN L_NNT_NORMAL3.FIRST .. L_NNT_NORMAL3.LAST LOOP
        IF J = L_NNT_NORMAL3(I).COLUMN_POSITION THEN
          L_SQL := L_SQL || CASE
                     WHEN J = 1 THEN
                      ''
                     ELSE
                      ','
                   END || L_NNT_NORMAL3(I).COLUMN_NAME;
        END IF;
      END LOOP;
    END LOOP;
    L_SQL := L_SQL || ');';
    DBMS_OUTPUT.PUT_LINE(L_SQL);
  END LOOP;
END;
/

step 4，重建物化视图，在此之前不要忘记保留索引。

BEGIN
  FOR ITEM IN (SELECT A.*,
                      B.MASTER,
                      CASE
                        WHEN B.PRIMARY_KEY = 'YES' THEN
                         1
                        ELSE
                         0
                      END R_PRIMARY_KEY
                 FROM USER_MVIEWS A
                 LEFT JOIN (SELECT * FROM USER_MVIEW_LOGS@APPLINK) B
                   ON REPLACE(A.MVIEW_NAME, 'MV_', '') = B.MASTER
                WHERE MASTER_LINK IS NOT NULL) LOOP
   EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || ITEM.MVIEW_NAME ;
   EXECUTE IMMEDIATE 'CREATE MATERIALIZED VIEW ' || ITEM.MVIEW_NAME ||
                         ' REFRESH FORCE WITH ' || CASE WHEN
                         ITEM.R_PRIMARY_KEY = 1 THEN 'PRIMARY KEY' ELSE
                         'ROWID' END ||
                         ' ON DEMAND START WITH SYSDATE NEXT SYSDATE + 4/24 AS SELECT * FROM ' ||
                         ITEM.MASTER || ITEM.MASTER_LINK ;
  END LOOP;
END;
/

step 5. 重新编译用户对象。

BEGIN
DBMS_UTILITY.compile_schema(schema => 'POSUSER');
END;
/