ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 物化视图几个知识点

物化视图几个知识点

原创 Linux操作系统 作者:space6212 时间:2019-07-08 10:06:06 0 删除 编辑
源表:物化视图数据源对应的表
基表:物化视图对应的表

本文主要内容包括:
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
---------- --------- - - ------------
1 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
---------- ---------- ----------
1 3 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、无论是指定字段还是用*,如果基表删除字段,则不能快速刷新和完全刷新

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63793/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 获取表空间DDL
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    168540