物化视图的非原子性刷新

物化视图的原子性刷新是指在刷新一组物化视图的时候，整个刷新作为一个事务，如果其中一个物化视图刷新失败，那么这一组物化视图都不会刷新。非原子性刷新则指的是每个物化视图会作为一个单独的事务，如果一组物化视图进行刷新，其中某个物化视图刷新失败，那么前面已经刷新过的物化视图都是提交过的。

 

 

对于完全刷新来说，非原子性刷新是最佳选择。由于原子性刷新会采用DELETE＋INSERT的方法，完全刷新将会删除物化视图表中所有的数据，然后在将源表中所有输入插入到物化视图表中，且这些DML都会使用UNDO空间，不但效率很低，而且占用大量的资源。

而非原子性刷新则采用了TRUNCATE + INSERT /*+ APPEND */的方法。以前只是注意到了TRUNCATE，没有注意这个APPEND。最近由于在一个小的测试环境下完全刷新一张很大的表，由于表本身的大小已经接近了这个数据库中UNDO表空间的大小，所以采用平常的刷新方式无法成功。当时就在想办法，看看能不能将物化视图的INSERT变成APPEND方式，结果TRACE了一下非原子性的刷新方式，发现Oracle自动采用了APPEND的方式。这样非原子性刷新不但效率最高，而且将占有的UNDO空间和产生的REDO数量减少到最低的情况。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID);

表已更改。

SQL> CREATE MATERIALIZED VIEW LOG ON T;

实体化视图日志已创建。

SQL> DROP MATERIALIZED VIEW LOG ON T;

实体化视图日志已删除。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH COMPLETE AS SELECT * FROM T;

实体化视图已创建。

SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB;

已创建23行。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T', ATOMIC_REFRESH => FALSE)

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

查看得到的TRACE文件信息：

=====================
PARSING IN CURSOR #3 len=34 dep=0 uid=72 ct=42 lid=72 tim=229649519499 hv=3913151867 ad='2567fa48'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #3:c=0,e=288,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=229649519493
*** 2008-11-12 08:40:28.078
=====================
PARSING IN CURSOR #15 len=198 dep=1 uid=0 ct=3 lid=0 tim=229660972725 hv=4125641360 ad='336db980'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #15:c=0,e=380,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=229660972719
EXEC #15:c=0,e=31901,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=229661005512
FETCH #15:c=0,e=46,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=229661005771
STAT #15 id=1 cnt=0 pid=0 pos=1 bj=18 p='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=54 us)'
STAT #15 id=2 cnt=0 pid=1 pos=1 bj=37 p='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=37 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #19 len=28 dep=1 uid=72 ct=7 lid=72 tim=229662296277 hv=4184353949 ad='25464a68'
delete from "YANGTK"."MV_T"
END OF STMT
PARSE #19:c=0,e=2951,p=0,cr=14,cu=0,mis=1,r=0,dep=1,og=1,tim=229662296271
EXEC #19:c=0,e=174,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=1,tim=229662296519
=====================
.
.
.
=====================
PARSING IN CURSOR #19 len=110 dep=1 uid=72 ct=2 lid=72 tim=229662306090 hv=3172137641 ad='25463ee8'
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "YANGTK"."MV_T"("ID","NAME") SELECT "T"."ID","T"."NAME" FROM "T" "T"
END OF STMT
PARSE #19:c=0,e=1623,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,tim=229662306084
EXEC #19:c=0,e=359,p=0,cr=8,cu=21,mis=0,r=23,dep=1,og=1,tim=229662306510
STAT #19 id=1 cnt=23 pid=0 pos=1 bj=61746 p='TABLE ACCESS FULL T (cr=7 pr=0 pw=0 time=140 us)'
STAT #28 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE SNAP$ (cr=1 pr=0 pw=0 time=80 us)'
STAT #28 id=2 cnt=1 pid=1 pos=1 bj=217 p='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=25 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #30 len=50 dep=1 uid=72 ct=85 lid=72 tim=229677074850 hv=3299608286 ad='25460cfc'
truncate table "YANGTK"."MV_T" purge snapshot log
END OF STMT
PARSE #30:c=0,e=58160,p=0,cr=0,cu=1,mis=1,r=0,dep=1,og=1,tim=229677074843
=====================
.
.
.
=====================
PARSING IN CURSOR #30 len=139 dep=1 uid=72 ct=2 lid=72 tim=229678096349 hv=1058017291 ad='25799364'
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "YANGTK"."MV_T"("ID","NAME") SELECT "T"."ID","T"."NAME" FROM "T" "T"
END OF STMT
PARSE #30:c=0,e=2970,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=1,tim=229678096342
EXEC #30:c=0,e=71106,p=1,cr=8,cu=10,mis=0,r=23,dep=1,og=1,tim=229678167508
XCTEND rlbk=0, rd_only=0
STAT #30 id=1 cnt=1 pid=0 pos=1 bj=0 p='LOAD AS SELECT (cr=8 pr=1 pw=1 time=71006 us)'
STAT #30 id=2 cnt=23 pid=1 pos=1 bj=61746 p='TABLE ACCESS FULL T (cr=7 pr=0 pw=0 time=194 us)'
STAT #23 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE SNAP$ (cr=1 pr=0 pw=0 time=63 us)'
STAT #23 id=2 cnt=1 pid=1 pos=1 bj=217 p='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=18 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #41 len=35 dep=0 uid=72 ct=42 lid=72 tim=229682775142 hv=4067503723 ad='2579c394'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #41:c=0,e=930,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=229682775135
EXEC #41:c=0,e=263,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=229682775481

从TRACE信息中可以清晰的看到原子性刷新与非原子性刷新在执行方式上的区别。