dbms_mview系列(一)
1,是dbms_snapshot同义词
2,更进一步理解物化视图及潜在的物化视图工作机制
3,可以刷新同一个刷新组的物化视图

操作指南:
如查询少于256字度,直接使用execute dbms_mview.explain_rewrite,否则用plsql begin end代码块.

操作限制和说明
 explain_rewrite过程不能接受超过32627个字符长度的查询.此限制同样应用于dbms_mview定义查询

dbms_mview包各子过程功能说明
Summary of DBMS_MVIEW Subprograms
 
file:///C:/Users/123/Desktop/每天工作明细/文档/oracle文档/oracle11g官方文档/appdev.112/e25788/d_mview.htm#CEGHCECB

BEGIN_TABLE_REORGANIZATION Procedure  刷新物化视图之前的准备工作

end_table_reorganization procedure    保证master table的物化视图结果有效,且master table处于合理状态

estimate_mview_size procedure         估算创建物化视图的大小,单位为byte或row

explain_mview procedure               解决物化视图或潜在物化视图是否可能适用一些情形,比如是否可增量刷新

explain_rewrite procedure             解释为何不能查询重写,优化器为何不重写一个查询

i_am_refresh procedure                返回i_am_refresh包状态的值

pmarker function                      返回源于rowid的一部分标识,用于partition change tracking,即pct

purge_direct_load_log procedure       自direct loader log清除记录,物化视图不再使用此log

purge_log procedure                   自物化视图日志清除记录

purge_mview_from_log                  同上

refresh procedure                     刷新不是同一刷新组的一或多个物化视图

refresh_all_mviews procedure          刷新所有物化视图,但不会影响对于master table or master物化视图变更.

refresh_dependent procedure           刷新基于特定的master table或master materiazlied view的物化视图,或者
                                      一系列master table或一系列master materiazlied views
                                     
register_mview procedure              启用每个物化视图的管理

unregister_mview procedure            与上相反

---重组master table前运行此过程
SQL> exec dbms_mview.begin_table_reorganization(tabowner => 'SCOTT',tabname => 'T_MV');
 
PL/SQL procedure successfully completed
--未进行dml master table前的数据
SQL> select * from mv_t;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                     20
---物化视图日志有记录,因为未提交,一提交,即清除记录
SQL> select * from mlog$_t_mv;
 
         A SNAPTIME$$  DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$                                                                       XID$$
---------- ----------- --------- --------- -------------------------------------------------------------------------------- ----------
     22222 4000/1/1    I         N         FE                                                                               2.53333062
 
--插入数据
SQL> insert into t_mv values(99,10);
 
1 row inserted
 
SQL> commit;
 
Commit complete
---未同步到物化视图,说明begin_table_reorganization用于维护物化视图,在其维护期间,产生的日志不记录到物化视图日志中
--当然不会应用到物化视图
SQL> select * from mv_t;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                     20
                                  22222
--插入数据在master table存在
SQL> select * from t_mv;
 
                                      A                                       B
--------------------------------------- ---------------------------------------
                                      1
                                      2
                                      3                                       3
                                     20                                    2222
                                  22222                                    2222
                                     99                                      10
 
6 rows selected
--结束物化视图维护
SQL> exec dbms_mview.end_table_reorganization(tabowner => 'SCOTT',tabname => 'T_MV');
 
PL/SQL procedure successfully completed
 
SQL> select * from mv_t;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                     20
                                  22222
--再次插入数据
SQL> insert into t_mv values(8888,8888);
 
1 row inserted
 
SQL> commit;
 
Commit complete
--马上同步到了物化视图
SQL> select * from mv_t;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                     20
                                   8888
                                  22222
 
6 rows selected
 
SQL> select * from t_mv;
 
                                      A                                       B
--------------------------------------- ---------------------------------------
                                      1
                                      2
                                      3                                       3
                                     20                                    2222
                                  22222                                    2222
                                     99                                      10
                                   8888                                    8888
 
7 rows selected