Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  302 随笔 :: 26 文章 :: 82 评论 :: 0 Trackbacks
源表:物化视图数据源对应的表
基表:物化视图对应的表

本文主要内容包括:

1、如何使源表的数据变化不影响物化视图的快速刷新
2、建好物化视图后,当基表或者源表的结构发生变化对物化视图刷新的影响。


测试数据准备:

suk@ORA9I> CREATE TABLE T_MV(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1));

Table created.

suk@ORA9I> CREATE MATERIALIZED VIEW LOG ON T_MV;

Materialized view log created.

--准备4种方法测试的MV
suk@ORA9I> CREATE MATERIALIZED VIEW MV_1 REFRESH FAST AS SELECT C1,C2 FROM T_MV;

Materialized view created.

suk@ORA9I> CREATE MATERIALIZED VIEW MV_2 REFRESH FAST AS SELECT * FROM T_MV;

Materialized view created.

suk@ORA9I> CREATE MATERIALIZED VIEW MV_3 REFRESH FAST AS SELECT * FROM T_MV T;

Materialized view created.

suk@ORA9I> CREATE MATERIALIZED VIEW MV_4 REFRESH FAST AS SELECT T.* FROM T_MV T;

Materialized view created.

一、如何修改源表数据,而不会产生MLOG$_XXX

物化视图在快速刷新时是根据MLOG$_XXX的记录来决定那些数据需要刷新的,所以,如果想要源表修改的数据不被刷新的话,就需要把MLOG$_XXX对应的记录去掉。
1、用函数包
suk@ORA9I> INSERT INTO T_MV VALUES(1,1);

1 row created.

suk@ORA9I> SELECT * FROM MLOG$_T_MV;

C1         SNAPTIME$ D O CHANGE_VECTO
---------- --------- - - ------------
         01-JAN-00 I N FE

suk@ORA9I> ROLLBACK;

Rollback complete.

suk@ORA9I> EXEC DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SUK','T_MV');
--用这个过程可以使得对源表的DML操作不产生MLOG,影响范围是从BEGIN_TABLE_REORGANIZATION到END_TABLE_REORGANIZATION其间

PL/SQL procedure successfully completed.

suk@ORA9I> COMMIT;--一定要执行commit,否则还会产生MLOG$

Commit complete.

suk@ORA9I> INSERT INTO T_MV VALUES(1,1);

1 row created.

suk@ORA9I> SELECT COUNT(1) FROM MLOG$_T_MV;

COUNT(1)
----------
0

suk@ORA9I> ROLLBACK;

Rollback complete.

suk@ORA9I> EXEC DBMS_MVIEW.END_TABLE_REORGANIZATION('SUK','T_MV');

PL/SQL procedure successfully completed.

--注意:对于表的DML非常频繁,如果只是想让某小部分数据不产生日志,则这种方法不适合。可以用第二种方法。

2、删除MLOG$记录

第二种方法很直接,就是直接删除不想被刷新的数据对应的修改日志。
这种方法的难点是如何准确找出那些是你需要删除的日志。方法很麻烦,这里不详细说了。

二、如何修改物化视图数据时,不产生USLOG_XXX

这种情况只能用手工删除USLOG$_XXX的方法了。

三、源表结构发生变化时

1、源表添加字段

--添加字段
suk@ORA9I> ALTER TABLE T_MV ADD(COL3 NUMBER);

Table altered.

--对mv_1进行两种方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','COMPELETE');

PL/SQL procedure successfully completed.

suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');

PL/SQL procedure successfully completed.

--对mv_2进行两种方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_2','COMPELETE');

PL/SQL procedure successfully completed.

suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_2','FAST');

PL/SQL procedure successfully completed.

--对mv_3进行两种方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_3','COMPELETE');

PL/SQL procedure successfully completed.

suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_3','FAST');

PL/SQL procedure successfully completed.

--对mv_4进行两种方法刷新
suk@ORA9I> 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_4"
ORA-00904: "COL3": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1


suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');

PL/SQL procedure successfully completed.

--从以上测试结果可以看出,源表添加字段时,只有MV_4在完全刷新时会出错。为什么会这样呢?
--先看看MV的DDL:
suk@ORA9I> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_1','SUK') FROM DUAL;

CREATE MATERIALIZED VIEW "SUK"."MV_1"
......
AS SELECT C1,C2 FROM T_MV

suk@ORA9I> C/1/2
1* SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_2','SUK') FROM DUAL
suk@ORA9I> /

CREATE MATERIALIZED VIEW "SUK"."MV_2"
......
AS SELECT "T_MV"."C1" "C1","T_MV"."C2" "C2" FROM "T_MV" "T_MV"

suk@ORA9I> C/2/3
1* SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_3','SUK') FROM DUAL
suk@ORA9I> /

CREATE MATERIALIZED VIEW "SUK"."MV_3"
......
AS SELECT "T"."C1" "C1","T"."C2" "C2" FROM "T_MV" "T"



suk@ORA9I> C/3/4
1* SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_4','SUK') FROM DUAL
suk@ORA9I> /

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、新添加的字段数据发生变化,快速刷新是否会刷新该记录

suk@ORA9I> SELECT * FROM T_MV;

C1         C2         COL3
---------- ---------- ----------
                  3

suk@ORA9I> UPDATE T_MV SET COL3=4;

1 row updated.

suk@ORA9I> COMMIT;

Commit complete.

suk@ORA9I> @begin_trace
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');
suk@ORA9I> @end_trace

--从trace文件中可以发现如下语句:
UPDATE "SUK"."MV_1" SET "C1" = :1,"C2" = :2
WHERE
"C1" = :1

--说明在源表中且在MV不存在的字段的数值发生变化,MV也会刷新这条数据。且MV的刷新方式是把整条记录的所有字段都更新

3、源表删除字段

suk@ORA9I> ALTER TABLE T_MV DROP COLUMN COL3;

Table altered.

suk@ORA9I> ALTER TABLE T_MV DROP COLUMN C2;

Table altered.

suk@ORA9I> 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 path
ORA-00904: "T_MV"."C2": invalid identifier
ORA-00904: "C2": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1


suk@ORA9I> 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 refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

.....MV_1到MV_3都包同样的错误.....

suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','COMPELETE');

PL/SQL procedure successfully completed.

suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');

PL/SQL procedure successfully completed.

--其实这个结果可以根据上一步推断出来了,现在用试验也证明了。
--根本原因就是建立MV的DDL不同,也就是是否指定*导致的


四、基表结构发生变化时
刚才讨论了源表的结构变化对MV刷新的影响,下面讨论基表的结构对MV刷新的影响。
这个问题相对简单一点,我们知道,修改基表不会对MV建立的DDL造成影响,也就是不会改变MV的刷新语句,所以,很容易得到以下结论:
1、如果基表添加字段,则不会影响快速刷新和完全刷新
2、如果基表删除字段,则不能快速刷新和完全刷新

suk@ORA9I> CREATE TABLE T_MV(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1));

Table created.

suk@ORA9I> CREATE MATERIALIZED VIEW LOG ON T_MV;

Materialized view log created.

suk@ORA9I> CREATE MATERIALIZED VIEW MV_1 REFRESH FAST AS SELECT C1,C2 FROM T_MV;

Materialized view created.

suk@ORA9I> CREATE MATERIALIZED VIEW MV_4 REFRESH FAST AS SELECT T.* FROM T_MV T;

Materialized view created.

--添加基表字段
suk@ORA9I> ALTER TABLE MV_1 ADD (C3 NUMBER);

Table altered.

suk@ORA9I> ALTER TABLE MV_4 ADD (C3 NUMBER);

Table altered.

----对mv_1进行两种方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','COMPELETE');

PL/SQL procedure successfully completed.

suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');

PL/SQL procedure successfully completed.

--对mv_4进行两种方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','COMPELETE');

PL/SQL procedure successfully completed.

suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');

PL/SQL procedure successfully completed.

--删除基表字段
--删除字段
suk@ORA9I> ALTER TABLE MV_1 DROP COLUMN C3;

Table altered.

suk@ORA9I> ALTER TABLE MV_1 DROP COLUMN C2;

Table altered.

suk@ORA9I> ALTER TABLE MV_4 DROP COLUMN C3;

Table altered.

suk@ORA9I> ALTER TABLE MV_4 DROP COLUMN C2;

Table altered.

--对mv_1进行两种方法刷新
suk@ORA9I> 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 path
ORA-00904: "C2": invalid identifier
ORA-00904: "C2": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1


suk@ORA9I> 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 refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

--对mv_4进行两种方法刷新
suk@ORA9I> 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 path
ORA-00904: "C2": invalid identifier
ORA-00904: "T"."C2": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1


suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');
BEGIN DBMS_MVIEW.REFRESH('MV_4','FAST'); END;

*
ERROR at line 1:
ORA-12057: materialized view "SUK"."MV_4" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

--以上的测试结果证明了前面的推论实在正确的
 
五、总结
源表结构变化
1、如果建立MV的DDL用到*(真正存储在数据库),则当源表增加字段时,基于该源表的MV可以正常快速刷新,但不能完全刷新;当源表删除字段时,基于该源表的MV可以正常快速刷新,也可以正常完全刷新。
2、如果建立MV的DDL指定了具体字段,则当源表增加字段时字段时,基于该源表的MV可以正常快速刷新,也可以正常完全刷新;当源表删除字段时,不能快速刷新,也不能完全刷新。
3、源表添加字段时,发生在新增字段的数据的变化对应的记录在快速刷新时会被刷新

基表结构变化
3、无论是指定字段还是用*,如果基表添加字段,则不会影响快速刷新和完全刷新
4、无论是指定字段还是用*,如果基表删除字段,则不能快速刷新和完全刷新
 
 




-The End-

posted on 2009-04-16 22:36 decode360-3 阅读(1409) 评论(0)  编辑  收藏 所属分类: Oracle

只有注册用户登录后才能发表评论。


网站导航: