物化视图几个知识点.doc

物化视图几个知识点 源表：物化视图数据源对应的表基表：物化视图对应的表本文主要内容包括： 1、如何使源表的数据变化不影响物化视图的快速刷新2、建好物化视图后，当基表或者源表的结构发生变化对物化视图刷新的影响。测试数据准备： sukORA9I CREATE TABLE T_MV(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1);Table created.sukORA9I CREATE MATERIALIZED VIEW LOG ON T_MV;Materialized view log created.-准备4种方法测试的MVsukORA9I CREATE MATERIALIZED VIEW MV_1 REFRESH FAST AS SELECT C1,C2 FROM T_MV;Materialized view created.sukORA9I CREATE MATERIALIZED VIEW MV_2 REFRESH FAST AS SELECT * FROM T_MV;Materialized view created.sukORA9I CREATE MATERIALIZED VIEW MV_3 REFRESH FAST AS SELECT * FROM T_MV T;Materialized view created.sukORA9I CREATE MATERIALIZED VIEW MV_4 REFRESH FAST AS SELECT T.* FROM T_MV T;Materialized view created.一、如何修改源表数据，而不会产生MLOG$_XXX 物化视图在快速刷新时是根据MLOG$_XXX的记录来决定那些数据需要刷新的，所以，如果想要源表修改的数据不被刷新的话，就需要把MLOG$_XXX对应的记录去掉。1、用函数包 sukORA9I INSERT INTO T_MV VALUES(1,1);1 row created.sukORA9I SELECT * FROM MLOG$_T_MV;C1 SNAPTIME$ D O CHANGE_VECTO- - - - -1 01-JAN-00 I N FEsukORA9I ROLLBACK;Rollback complete.sukORA9I EXEC DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION(SUK,T_MV);-用这个过程可以使得对源表的DML操作不产生MLOG，影响范围是从BEGIN_TABLE_REORGANIZATION到END_TABLE_REORGANIZATION其间PL/SQL procedure successfully completed.sukORA9I COMMIT;-一定要执行commit，否则还会产生MLOG$Commit complete.sukORA9I INSERT INTO T_MV VALUES(1,1);1 row created.sukORA9I SELECT COUNT(1) FROM MLOG$_T_MV;COUNT(1)-0sukORA9I ROLLBACK;Rollback complete.sukORA9I EXEC DBMS_MVIEW.END_TABLE_REORGANIZATION(SUK,T_MV);PL/SQL procedure successfully completed.-注意：对于表的DML非常频繁，如果只是想让某小部分数据不产生日志，则这种方法不适合。可以用第二种方法。2、删除MLOG$记录 第二种方法很直接，就是直接删除不想被刷新的数据对应的修改日志。这种方法的难点是如何准确找出那些是你需要删除的日志。方法很麻烦，这里不详细说了。二、如何修改物化视图数据时，不产生USLOG_XXX 这种情况只能用手工删除USLOG$_XXX的方法了。三、源表结构发生变化时 1、源表添加字段 -添加字段sukORA9I ALTER TABLE T_MV ADD(COL3 NUMBER);Table altered. -对mv_1进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,COMPELETE);PL/SQL procedure successfully completed.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,FAST);PL/SQL procedure successfully completed.-对mv_2进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_2,COMPELETE);PL/SQL procedure successfully completed.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_2,FAST);PL/SQL procedure successfully completed.-对mv_3进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_3,COMPELETE);PL/SQL procedure successfully completed.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_3,FAST);PL/SQL procedure successfully completed.-对mv_4进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_4,COMPELETE);BEGIN DBMS_MVIEW.REFRESH(MV_4,COMPELETE); END;*ERROR at line 1:ORA-12018: following error encountered during code generation for SUK.MV_4ORA-00904: COL3: invalid identifierORA-06512: at SYS.DBMS_SNAPSHOT, line 794ORA-06512: at SYS.DBMS_SNAPSHOT, line 851ORA-06512: at SYS.DBMS_SNAPSHOT, line 832ORA-06512: at line 1sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_4,FAST);PL/SQL procedure successfully completed.-从以上测试结果可以看出，源表添加字段时，只有MV_4在完全刷新时会出错。为什么会这样呢？-先看看MV的DDL：sukORA9I SELECT DBMS_METADATA.GET_DDL(MATERIALIZED_VIEW,MV_1,SUK) FROM DUAL;CREATE MATERIALIZED VIEW SUK.MV_1.AS SELECT C1,C2 FROM T_MVsukORA9I C/1/21* SELECT DBMS_METADATA.GET_DDL(MATERIALIZED_VIEW,MV_2,SUK) FROM DUALsukORA9I /CREATE MATERIALIZED VIEW SUK.MV_2.AS SELECT T_MV.C1 C1,T_MV.C2 C2 FROM T_MV T_MVsukORA9I C/2/31* SELECT DBMS_METADATA.GET_DDL(MATERIALIZED_VIEW,MV_3,SUK) FROM DUALsukORA9I /CREATE MATERIALIZED VIEW SUK.MV_3.AS SELECT T.C1 C1,T.C2 C2 FROM T_MV TsukORA9I C/3/41* SELECT DBMS_METADATA.GET_DDL(MATERIALIZED_VIEW,MV_4,SUK) FROM DUALsukORA9I /CREATE MATERIALIZED VIEW SUK.MV_4.AS SELECT T.* FROM T_MV T-看每一个MV的DDL的最后一行，不难发现问题了。-在前三种情况下，oracle在创建MV时会翻译成当前源表对应的字段名;但第四种情况则不然，它是在刷新时才翻译成源表对应的字段，如果源表的结构发生变化，那很明显，MV刷新会出现问题。-那MV_4为什么快速刷新就不会出错呢？通过trace文件，可以看出完全刷新和快速刷新的不同之处： -完全刷新INSERT INTO SUK.MV_4(C1,C2,COL3) SELECT T.C1,T.C2,T.COL3 FROM T_MV T -快速刷新INSERT INTO SUK.MV_4 (C1,C2)VALUES(:1,:2) -可以看出，完全刷新时，是根据源表的结构进行刷新的-快速刷新时，是根据MV的结构进行刷新的2、新添加的字段数据发生变化，快速刷新是否会刷新该记录 sukORA9I SELECT * FROM T_MV;C1 C2 COL3- - -1 3 3sukORA9I UPDATE T_MV SET COL3=4;1 row updated.sukORA9I COMMIT;Commit complete.sukORA9I begin_tracesukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,FAST);sukORA9I end_trace-从trace文件中可以发现如下语句：UPDATE SUK.MV_1 SET C1 = :1,C2 = :2WHEREC1 = :1 -说明在源表中且在MV不存在的字段的数值发生变化，MV也会刷新这条数据。且MV的刷新方式是把整条记录的所有字段都更新3、源表删除字段 sukORA9I ALTER TABLE T_MV DROP COLUMN COL3;Table altered.sukORA9I ALTER TABLE T_MV DROP COLUMN C2;Table altered.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,COMPELETE);BEGIN DBMS_MVIEW.REFRESH(MV_1,COMPELETE); END;*ERROR at line 1:ORA-12008: error in materialized view refresh pathORA-00904: T_MV.C2: invalid identifierORA-00904: C2: invalid identifierORA-06512: at SYS.DBMS_SNAPSHOT, line 794ORA-06512: at SYS.DBMS_SNAPSHOT, line 851ORA-06512: at SYS.DBMS_SNAPSHOT, line 832ORA-06512: at line 1sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,FAST);BEGIN DBMS_MVIEW.REFRESH(MV_1,FAST); END;*ERROR at line 1:ORA-12057: materialized view SUK.MV_1 is INVALID and must complete refreshORA-06512: at SYS.DBMS_SNAPSHOT, line 794ORA-06512: at SYS.DBMS_SNAPSHOT, line 851ORA-06512: at SYS.DBMS_SNAPSHOT, line 832ORA-06512: at line 1.MV_1到MV_3都包同样的错误.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_4,COMPELETE);PL/SQL procedure successfully completed.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_4,FAST);PL/SQL procedure successfully completed.-其实这个结果可以根据上一步推断出来了，现在用试验也证明了。-根本原因就是建立MV的DDL不同，也就是是否指定*导致的四、基表结构发生变化时 刚才讨论了源表的结构变化对MV刷新的影响，下面讨论基表的结构对MV刷新的影响。这个问题相对简单一点，我们知道，修改基表不会对MV建立的DDL造成影响，也就是不会改变MV的刷新语句，所以，很容易得到以下结论：1、如果基表添加字段，则不会影响快速刷新和完全刷新2、如果基表删除字段，则不能快速刷新和完全刷新sukORA9I CREATE TABLE T_MV(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1);Table created.sukORA9I CREATE MATERIALIZED VIEW LOG ON T_MV;Materialized view log created.sukORA9I CREATE MATERIALIZED VIEW MV_1 REFRESH FAST AS SELECT C1,C2 FROM T_MV;Materialized view created.sukORA9I CREATE MATERIALIZED VIEW MV_4 REFRESH FAST AS SELECT T.* FROM T_MV T;Materialized view created. -添加基表字段sukORA9I ALTER TABLE MV_1 ADD (C3 NUMBER);Table altered.sukORA9I ALTER TABLE MV_4 ADD (C3 NUMBER);Table altered. -对mv_1进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,COMPELETE);PL/SQL procedure successfully completed.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,FAST);PL/SQL procedure successfully completed.-对mv_4进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_4,COMPELETE);PL/SQL procedure successfully completed.sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_4,FAST);PL/SQL procedure successfully completed.-删除基表字段-删除字段sukORA9I ALTER TABLE MV_1 DROP COLUMN C3;Table altered.sukORA9I ALTER TABLE MV_1 DROP COLUMN C2;Table altered.sukORA9I ALTER TABLE MV_4 DROP COLUMN C3;Table altered.sukORA9I ALTER TABLE MV_4 DROP COLUMN C2;Table altered.-对mv_1进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,COMPELETE);BEGIN DBMS_MVIEW.REFRESH(MV_1,COMPELETE); END;*ERROR at line 1:ORA-12008: error in materialized view refresh pathORA-00904: C2: invalid identifierORA-00904: C2: invalid identifierORA-06512: at SYS.DBMS_SNAPSHOT, line 794ORA-06512: at SYS.DBMS_SNAPSHOT, line 851ORA-06512: at SYS.DBMS_SNAPSHOT, line 832ORA-06512: at line 1sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_1,FAST);BEGIN DBMS_MVIEW.REFRESH(MV_1,FAST); END;*ERROR at line 1:ORA-12057: materialized view SUK.MV_1 is INVALID and must complete refreshORA-06512: at SYS.DBMS_SNAPSHOT, line 794ORA-06512: at SYS.DBMS_SNAPSHOT, line 851ORA-06512: at SYS.DBMS_SNAPSHOT, line 832ORA-06512: at line 1-对mv_4进行两种方法刷新sukORA9I EXEC DBMS_MVIEW.REFRESH(MV_4,COMPELETE);BEGIN DBMS_MVIEW.REFRESH(MV_4,COMPELETE); END;*ERROR at line 1:ORA-12008: error in materialized view refresh pathORA-00904: C2: invalid identifierORA-00904