dbms_mview系列(三)
dbms_mview系列(三)
EXPLAIN_REWRITE Procedure
和explain_mview同理,也有2個過載過程形式.一個儲存到表,另一個儲存到array
語法
DBMS_MVIEW.EXPLAIN_REWRITE (
query VARCHAR2,
mv VARCHAR2(30),
statement_id VARCHAR2(30));
DBMS_MVIEW.EXPLAIN_REWRITE (
query [VARCHAR2 | CLOB],
mv VARCHAR2(30),
output_array SYS.RewriteArrayType);
utlxrw.sql script建立REWRITE_TABLE
xrwutl.sql格式化rewrite_table表的顯示結果
--執行過程,query引數即sql查詢,mv即物化檢視,此可以是多個mv,以逗號分隔
SQL> exec dbms_mview.Explain_Rewrite(QUERY => 'select a from t_mv',MV => 'mv_t');
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from rewrite_table;
STATEMENT_ID MV_OWNER MV_NAME SEQUENCE QUERY QUERY_BLOCK_NO REWRITTEN_TXT MESSAGE PASS MV_IN_MSG MEASURE_IN_MSG JOIN_BACK_TBL JOIN_BACK_COL ORIGINAL_COST REWRITTEN_COST FLAGS RESERVED1 RESERVED2
------------------------------ ------------------------------ ------------------------------ --------------------------------------- -------------------------------------------------------------------------------- --------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------
SCOTT MV_T 1 select a from t_mv 0 select a from t_mv QSM-01150: query did not rewrite NO 0 0
SCOTT MV_T 2 select a from t_mv 0 select a from t_mv QSM-01026: query rewrite is disabled for, MV_T YES MV_T 0 0
i_am_refresh函式
I_AM_A_REFRESH Function
This function returns the value of the I_AM_REFRESH package state.
語法
Syntax
DBMS_MVIEW.I_AM_A_REFRESH
RETURN BOOLEAN;--返回boolean
Return Values
如返回true,表明物化檢視的所有本地,事務上在當前會話已經禁用,因為每個複製trigger首先檢查此狀態值.
而false表示啟用這些trigger
A return value of true indicates that all local replication triggers for materialized views are
effectively disabled in this session because each replication trigger first checks this state.
A return value of false indicates that these triggers are enabled.
--不能更新物化檢視
SQL> update mv_t set a=88;
update mv_t set a=88
ORA-01732: data manipulation operation not legal on this view
SQL> declare
2 begin
3 if dbms_mview.i_am_a_refresh then --此函式我初步理解為檢查與物化檢視相關的觸發器是否啟用,未完全理解
4 dbms_output.put_line('true');
5 else
6 dbms_output.put_line('false');
7 end if;
8 end;
9 /
PL/SQL procedure successfully completed
false
pmarker函式
PMARKER Function
This function returns a partition marker from a rowid. It is used for Partition Change Tracking (PCT).
Syntax
DBMS_MVIEW.PMARKER(
rid IN ROWID)
RETURN NUMBER;
SQL> select dbms_mview.pmarker(rowid) from t_mv;
DBMS_MVIEW.PMARKER(ROWID)
-------------------------
68087
SQL> insert into t_mv values(3);
1 row inserted
SQL> select * from mlog$_t_mv;
A SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
---------- ----------- --------- --------- -------------------------------------------------------------------------------- ----------
3 4000/1/1 I N FE 2.53331774
SQL> insert into t_mv values(4);
1 row inserted
--可知物化檢視日誌由dmltyp$$列可知dml操作型別
SQL> select * from mlog$_t_mv;
A SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
---------- ----------- --------- --------- -------------------------------------------------------------------------------- ----------
4 4000/1/1 I N FE 2.53331774
3 4000/1/1 I N FE 2.53331774
SQL> update t_mv set a=44 where a=4;
1 row updated
SQL> select * from mlog$_t_mv;
A SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
---------- ----------- --------- --------- -------------------------------------------------------------------------------- ----------
4 4000/1/1 I N FE 2.53331774
4 4000/1/1 D O 00 2.53331774
3 4000/1/1 I N FE 2.53331774
44 4000/1/1 I N FF 2.53331774
--執行此過程後,物化檢視日誌仍存在
SQL> exec dbms_mview.purge_direct_load_log();
PL/SQL procedure successfully completed
SQL> select * from mlog$_t_mv;
A SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
---------- ----------- --------- --------- -------------------------------------------------------------------------------- ----------
4 4000/1/1 I N FE 2.53331774
4 4000/1/1 D O 00 2.53331774
3 4000/1/1 I N FE 2.53331774
44 4000/1/1 I N FF 2.53331774
purge_log過程
PURGE_LOG Procedure
清除物化檢視日誌的資料
This procedure purges rows from the materialized view log.
Syntax
DBMS_MVIEW.PURGE_LOG (
master IN VARCHAR2,
num IN BINARY_INTEGER := 1,
flag IN VARCHAR2 := 'NOP');--flag可覆蓋num
SQL> exec dbms_mview.purge_log(master => 'mv_t',num => 2);
PL/SQL procedure successfully completed
SQL> select * from mlog$_t_mv;
A SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
---------- ----------- --------- --------- -------------------------------------------------------------------------------- ----------
purge_mview_from_log過程
PURGE_MVIEW_FROM_LOG Procedure
此過程不會取消物化檢視的註冊.如指定最老的物化檢視,則物化檢視日誌也被清除.此過程在master site執行,刪除由mview_id,
mviewowner等幾個引數組合決定指定物化檢視的相關資料字典資料.(譯得有些亂)
This procedure is called on the master site or master materialized view site to delete the rows in materialized view
refresh related data dictionary tables maintained at the master for the specified materialized view identified by its
mview_id or the combination of the mviewowner, mviewname, and the mviewsite. If the materialized view specified is
the oldest materialized view to have refreshed from any of the master tables or master materialized views, then
the materialized view log is also purged. This procedure does not unregister the materialized view.
Syntax
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mview_id IN BINARY_INTEGER |
mviewowner IN VARCHAR2,
mviewname IN VARCHAR2,
mviewsite IN VARCHAR2);--取自dba_registered_mviews列mview_site
注:
Note:
mview_id與後三個引數互斥.
This procedure is overloaded. The mview_id parameter is mutually exclusive with the three remaining parameters:
mviewowner, mviewname, and mviewsite.
--其引數mview_id源於如下字典
SQL> desc dba_base_table_mviews;
Name Type Nullable Default Comments
----------------------- ------------ -------- ------- -----------------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the master table which changes are logged
MASTER VARCHAR2(30) Name of the master table which changes are logged
MVIEW_LAST_REFRESH_TIME DATE One date per materialized view -- the date the materialized view was last refreshed
MVIEW_ID INTEGER Y Unique identifier of the materialized view
--其它引數源於下述字典
SQL> desc dba_registered_mviews;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- -----------------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the materialized view
NAME VARCHAR2(30) The name of the materialized view
MVIEW_SITE VARCHAR2(128) Global name of the materialized view site
CAN_USE_LOG VARCHAR2(3) Y If NO, this materialized view is complex and cannot fast refresh
UPDATABLE VARCHAR2(3) Y If NO, the materialized view is read only
REFRESH_METHOD VARCHAR2(11) Y Whether the materialized view uses rowid, primary key or object id for fast refresh
MVIEW_ID INTEGER Y Identifier for the materialized view used by the master for fast refresh
VERSION VARCHAR2(26) Y Version of materialized view
QUERY_TXT LONG Y Query defining the materialized view
注:如中期過程出錯,已成功不會rollback,再次執行此過程即可.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-754611/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dbms_mview系列(六)View
- dbms_mview系列(五)View
- dbms_mview系列(四)View
- dbms_mview系列(二)View
- dbms_mview系列(一)View
- dbms_mview系列(五)_補View
- dbms_mview 並行重新整理 refresh parallelView並行Parallel
- MongoTemplate 教程系列（三）Go
- oracle undo系列(三)Oracle
- webpack系列之三resolveWeb
- vue系列自定義指令（三）Vue
- DelayQueue系列（三）：持久化方案持久化
- JDK併發AQS系列(三)JDKAQS
- [Docker 系列]docker 學習 三Docker
- 【Docker 系列】docker 學習 三Docker
- RxJava操作符系列三RxJava
- go微服務系列之三Go微服務
- margin系列之bug巡演（三）
- oracle v$lock系列之三Oracle
- dbms_scheduler package系列三Package
- 前端系列-三次握手前端
- Flutter系列（三）——環境搭建（Windows）FlutterWindows
- Vue原始碼分析系列三：renderVue原始碼
- JavaScript夯實基礎系列（三）：thisJavaScript
- Flutter系列（三） 整體架構Flutter架構
- YOLO系列梳理（三）YOLOv5YOLO
- Docker框架的使用系列教程（三）Docker框架
- Pytorch系列:（三）模型構建PyTorch模型
- 機器學習Sklearn系列：（三）決策樹機器學習
- JVM系列(三) - JVM物件探祕JVM物件
- CSS系列 (03)：CSS三大特性CSS
- 【Unity】Obi外掛系列（三）—— CollisionsUnity
- Play! Framework 系列（三）：依賴注入Framework依賴注入
- flashback系列文章三（flashback database）Database
- iOS動畫系列之三：Core AnimationiOS動畫
- oracle 11g bootstrap$系列三Oracleboot
- 分散式資料庫系列(三)分散式資料庫
- rac維護工具集系列（三）--叢集層crsctl系列