dbms_mview系列(五)
REFRESH_ALL_MVIEWS Procedure
此過程會重新整理符合如下特徵的所有物化檢視
This procedure refreshes all materialized views that have the following properties:
物化檢視不會重新整理因為它依賴於master table or master materiazlied view的最新變化
The materialized view has not been refreshed since the most recent change to a master table or master materialized view on which it depends.
物化檢視及其依賴的master table or master materialized view必須全是本地
The materialized view and all of the master tables or master materialized views on which it depends are local.
物化檢視必須位於dba_mviews
The materialized view is in the view DBA_MVIEWS.
此過程用於資料倉儲
This procedure is intended for use with data warehouses.
Syntax
DBMS_MVIEW.REFRESH_ALL_MVIEWS (
number_of_failures OUT BINARY_INTEGER,
method IN VARCHAR2 := NULL,--諸元素語義同上
rollback_seg IN VARCHAR2 := NULL,
refresh_after_errors IN BOOLEAN := false,
atomic_refresh IN BOOLEAN := true);
--物化檢視可以truncate
SQL> truncate table mv_t;
Table truncated
SQL> select * from mv_t;
SQL> set serverout on
SQL> declare
2 out_err number;
3 begin
4 dbms_mview.refresh_all_mviews(number_of_failures => out_err,method => 'f',refresh_after_errors => true,atomic_refresh => false);
5 dbms_output.put_line(out_err);
6 end;
7 /
5
PL/SQL procedure successfully completed --重新整理所有物化檢視有5個錯誤
SQL> select * from mv_t;
A
---------------------------------------
--系統共有5個物化檢視
SQL> select * from dba_mviews;
OWNER MVIEW_NAME CONTAINER_NAME QUERY QUERY_LEN UPDATABLE UPDATE_LOG MASTER_ROLLBACK_SEG MASTER_LINK REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE LAST_REFRESH_TYPE LAST_REFRESH_DATE STALENESS AFTER_FAST_REFRESH UNKNOWN_PREBUILT UNKNOWN_PLSQL_FUNC UNKNOWN_EXTERNAL_TABLE UNKNOWN_CONSIDER_FRESH UNKNOWN_IMPORT UNKNOWN_TRUSTED_FD COMPILE_STATE USE_NO_INDEX STALE_SINCE NUM_PCT_TABLES NUM_FRESH_PCT_REGIONS NUM_STALE_PCT_REGIONS
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------------------------------- --------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------------ ------------ -------------- ---------- ------------------ ----------------- ----------------- ------------------- ------------------- ---------------- ------------------ ---------------------- ---------------------- -------------- ------------------ ------------------- ------------ ----------- -------------- --------------------- ---------------------
SCOTT MV_9 MV_9 select a from t_mv 19 N N GENERAL COMMIT FORCE IMMEDIATE DML COMPLETE 2013/2/20 10:04:0 UNUSABLE NA N N N N N N VALID N 0
SCOTT MV_ROWID MV_ROWID select a from t_rowid connect by level<=3 42 N N TEXTMATCH DEMAND FORCE IMMEDIATE NO COMPLETE 2013/2/20 20:25:3 FRESH NA N N N N N N VALID N 0
SCOTT MV_1 MV_1 select a from t_mv 19 N N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 2013/2/21 20:24:5 UNUSABLE NA N N N N N N VALID N 0
SCOTT MV_TOP MV_TOP select mv_1.a from mv_1,t_mv where mv_1.a=t_mv.a 50 N N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 2013/2/21 20:25:3 UNUSABLE NA N N N N N N VALID N 0
SCOTT MV_T MV_T select a from t_mv 19 N N GENERAL COMMIT FAST IMMEDIATE DML FAST 2013/2/22 23:59:2 UNUSABLE NA N N N N N N VALID N 0
A
---------------------------------------
SQL> drop materialized view mv_t;
Materialized view dropped
--刪除完所有物化檢視再執行過程結果為0
SQL>
SQL> declare
2 out_err number;
3 begin
4 dbms_mview.refresh_all_mviews(number_of_failures => out_err,method => 'f',refresh_after_errors => true,atomic_refresh => false);
5 dbms_output.put_line(out_err);
6 end;
7 /
0
PL/SQL procedure successfully completed
--mv filter column
SQL> desc dba_mview_log_filter_cols;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------------------------------------
OWNER VARCHAR2(30) Y Owner of the master table being logged
NAME VARCHAR2(30) Y Name of the master table being logged
COLUMN_NAME VARCHAR2(30) Y Filter column being logged
SQL> desc dba_mview_aggregates;
Name Type Nullable Default Comments
------------------ ------------ -------- ------- ----------------------------------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the materialized view
MVIEW_NAME VARCHAR2(30) Name of the materialized view
POSITION_IN_SELECT NUMBER Position of this aggregated measure with the SELECT list
CONTAINER_COLUMN VARCHAR2(30) Name of this column in the container table
AGG_FUNCTION VARCHAR2(8) Y Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV
DISTINCTFLAG VARCHAR2(1) Y Set to Y is this is a DISTINCT aggregation
MEASURE LONG Y The SQL text of the measure, excluding the aggregation function
SQL>
SQL> desc dba_mview_detail_partition;
Name Type Nullable Default Comments
------------------------- ------------ -------- ------- ----------------------------------------
OWNER VARCHAR2(30) Owner of the materialized view
MVIEW_NAME VARCHAR2(30) Name of the materialized view
DETAILOBJ_OWNER VARCHAR2(30)
DETAILOBJ_NAME VARCHAR2(30) Name of the detail object
DETAIL_PARTITION_NAME VARCHAR2(30) Y Name of the detail object partition
DETAIL_PARTITION_POSITION NUMBER Y Position of the detail object partition
FRESHNESS CHAR(5) Y Freshness of the detail object partition
SQL> desc dba_mview_joins;
Name Type Nullable Default Comments
------------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the materialized view
MVIEW_NAME VARCHAR2(30) Name of the materialized view
DETAILOBJ1_OWNER VARCHAR2(30) Owner of the 1st detail object
DETAILOBJ1_RELATION VARCHAR2(30) Name of the 1st detail object
DETAILOBJ1_COLUMN VARCHAR2(30) Name of the 1st detail object column
OPERATOR CHAR(1) Y Name of the join operator. Currently nly = is defined
OPERATOR_TYPE VARCHAR2(1) Y Indicates inner or outer join. I = inner join, L = DETAILOBJ1 table
is the left side of an outer join, R = DETAILOBJ2 table is the right
side of an outer join
DETAILOBJ2_OWNER VARCHAR2(30) Owner of the 2nd detail object
DETAILOBJ2_RELATION VARCHAR2(30) Name of the 2nd detail object
DETAILOBJ2_COLUMN VARCHAR2(30) Name of the 2nd detail object column
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-755172/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dbms_mview系列(五)_補View
- dbms_mview系列(六)View
- dbms_mview系列(四)View
- dbms_mview系列(三)View
- dbms_mview系列(二)View
- dbms_mview系列(一)View
- iptables系列五
- dbms_mview 並行重新整理 refresh parallelView並行Parallel
- JDK併發AQS系列(五)JDKAQS
- Spring AOP系列（五）—反射Spring反射
- RxJava 操作符系列五RxJava
- dbms_scheduler package系列(五)Package
- Elasticsearch 系列（五）- 資料聚合Elasticsearch
- webpack系列之五module生成1Web
- webpack系列之五module生成2Web
- docker系列（五）：網路通訊Docker
- HBase 系列（五）——HBase常用 Shell 命令
- PHP系列（五）PHP字串處理PHP字串
- JavaScript夯實基礎系列（五）：類JavaScript
- Flutter系列五：State的生命週期Flutter
- JVM系列(五)：gc實現概要01JVMGC
- Web Components 系列（五）—— 關於 TemplatesWeb
- Web Components 系列（五）—— 詳解 SlotsWeb
- WebAssembly 系列（五）為什麼 WebAssembly 更快？Web
- Oracle 12c系列(五)｜PDB RefreshOracle
- create table進階學習系列(五)
- 【JVM】JVM系列之執行引擎（五）JVM
- [譯] Flutter 系列入門教程五：網格Flutter
- Java NIO學習系列五：I/O模型Java模型
- Akka系列（五）：Java和Scala中的FutureJava
- 機器學習Sklearn系列：（五）聚類演算法機器學習聚類演算法
- 深入JavaScript系列（五）：JS與記憶體JavaScriptJS記憶體
- JVM系列(五) - JVM垃圾回收演算法JVM演算法
- RabbitMQ系列（五）使用Docker部署RabbitMQ叢集MQDocker
- Akka 系列（五）：Java 和 Scala 中的 FutureJava
- keystone系列五：keystone原始碼分析原始碼
- ORACLE SQL效能最佳化系列 (五) (轉)OracleSQL
- RocketMQ系列（五）廣播與延遲訊息MQ