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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-754611/
扫一扫
2543
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
