dbms_mview系列(一)

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
 
每天工作明細/文件/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