oracle10g materialized view物化檢視示例
SQL> create table t_test(a int,b int);
Table created.
--構建物化檢視基表必須有pk主鍵
SQL> create materialized view mv_t_test refresh fast as select a,b from t_test;
create materialized view mv_t_test refresh fast as select a,b from t_test
*
ERROR at line 1:
ORA-12014: table 'T_TEST' does not contain a primary key constraint
--新增主鍵
SQL> alter table t_test add primary key(a)
2 ;
Table altered.
--增量重新整理（refresh fast）必須構建物化檢視日誌
SQL> create materialized view mv_t_test refresh fast as select a,b from t_test;
create materialized view mv_t_test refresh fast as select a,b from t_test
*
ERROR at line 1:
ORA-23413: table "SCOTT"."T_TEST" does not have a materialized view log
SQL> create materialized view mv_t_test refresh force as select a,b from t_test;
Materialized view created.
---檢視物化檢視相關資訊
select owner,mview_name,updatable,update_log from user_mviews
OWNER MVIEW_NAME U UPDATE_LOG
------------------------------ ------------------------------ - ------------------------------
SCOTT MV_T_TEST N
SQL>
SQL> select mview_name,rewrite_enabled,rewrite_capability from user_mviews;
MVIEW_NAME R REWRITE_C
------------------------------ - ---------
MV_T_TEST N GENERAL
SQL> select mview_name,refresh_mode,refresh_method,build_mode from user_mviews;
MVIEW_NAME REFRES REFRESH_ BUILD_MOD
------------------------------ ------ -------- ---------
MV_T_TEST DEMAND FORCE IMMEDIATE
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DML COMPLETE 2013-07-18 10:55:11
SQL> exec dbms_mview.refresh('mv_t_test');
PL/SQL procedure successfully completed.
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DML COMPLETE 2013-07-18 11:05:15
--增量重新整理報錯，第二個引數指定refresh mode,值為：f表示fast；c表示complete;?表示force;p表示基於分割槽重新整理pct
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-23413: table "SCOTT"."T_TEST" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL> exec dbms_mview.refresh('mv_t_test','c');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('mv_t_test','?');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('mv_t_test','x');
BEGIN dbms_mview.refresh('mv_t_test','x'); END;
*
ERROR at line 1:
ORA-01760: illegal argument for function
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL> exec dbms_mview.refresh('mv_t_test','p');
BEGIN dbms_mview.refresh('mv_t_test','p'); END;
*
ERROR at line 1:
ORA-12047: PCT FAST REFRESH cannot be used for materialized view "SCOTT"."MV_T_TEST"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL>
--構建物化檢視日誌
SQL> create materialized view log on t_test;
Materialized view log created.
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;
no rows selected
--構建物化檢視日誌後先要全量重新整理，才可以進行增量重新整理
SQL> !oerr ora 12034
12034, 0000, "materialized view log on \"%s\".\"%s\" younger than last refresh"
// *Cause: The materialized view log was younger than the last refresh.
// *Action: A complete refresh is required before the next fast refresh.
//
SQL> exec dbms_mview.refresh('mv_t_test','c');
PL/SQL procedure successfully completed.
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;
no rows selected
--全量重新整理後增量重新整理即可
SQL> exec dbms_mview.refresh('mv_t_test','f');
PL/SQL procedure successfully completed.
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DML FAST 2013-07-18 11:19:49
SQL> exec dbms_mview.refresh('mv_t_test');
PL/SQL procedure successfully completed.
--構建物化檢視日誌後自動進行增量重新整理
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DML FAST 2013-07-18 11:22:26
--插入記錄不提交
SQL> insert into t_test select level,level+3 from dual connect by level<=20;
20 rows created.
--未提交前在物化檢視日誌插入記錄
select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test
A SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
1 4000-01-01 00:00:00 I N FE
2 4000-01-01 00:00:00 I N FE
3 4000-01-01 00:00:00 I N FE
4 4000-01-01 00:00:00 I N FE
5 4000-01-01 00:00:00 I N FE
6 4000-01-01 00:00:00 I N FE
7 4000-01-01 00:00:00 I N FE
8 4000-01-01 00:00:00 I N FE
9 4000-01-01 00:00:00 I N FE
10 4000-01-01 00:00:00 I N FE
11 4000-01-01 00:00:00 I N FE
A SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
12 4000-01-01 00:00:00 I N FE
13 4000-01-01 00:00:00 I N FE
14 4000-01-01 00:00:00 I N FE
15 4000-01-01 00:00:00 I N FE
16 4000-01-01 00:00:00 I N FE
17 4000-01-01 00:00:00 I N FE
18 4000-01-01 00:00:00 I N FE
19 4000-01-01 00:00:00 I N FE
20 4000-01-01 00:00:00 I N FE
20 rows selected.
SQL> commit;
Commit complete.
--提交後仍儲存物化檢視日誌
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;
A SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
1 4000-01-01 00:00:00 I N FE
2 4000-01-01 00:00:00 I N FE
3 4000-01-01 00:00:00 I N FE
4 4000-01-01 00:00:00 I N FE
5 4000-01-01 00:00:00 I N FE
6 4000-01-01 00:00:00 I N FE
7 4000-01-01 00:00:00 I N FE
8 4000-01-01 00:00:00 I N FE
9 4000-01-01 00:00:00 I N FE
10 4000-01-01 00:00:00 I N FE
11 4000-01-01 00:00:00 I N FE
A SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
12 4000-01-01 00:00:00 I N FE
13 4000-01-01 00:00:00 I N FE
14 4000-01-01 00:00:00 I N FE
15 4000-01-01 00:00:00 I N FE
16 4000-01-01 00:00:00 I N FE
17 4000-01-01 00:00:00 I N FE
18 4000-01-01 00:00:00 I N FE
19 4000-01-01 00:00:00 I N FE
20 4000-01-01 00:00:00 I N FE
20 rows selected.
--重新整理物化檢視
SQL> exec dbms_mview.refresh('mv_t_test');
PL/SQL procedure successfully completed.
--重新整理物化檢視則有
SQL> select * from mv_t_test;
A B
---------- ----------
1 4
6 9
11 14
13 16
2 5
14 17
20 23
4 7
5 8
8 11
17 20
A B
---------- ----------
3 6
7 10
18 21
9 12
10 13
12 15
15 18
16 19
19 22
20 rows selected.
--重新整理物化檢視後則刪除物化檢視日誌記錄
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;
no rows selected
--更新不提交
SQL> update t_test set b=44 where a=1;
1 row updated.
--透過物化檢視日誌列可區別源表的不同操作型別，
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;
A SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
1 4000-01-01 00:00:00 U U 04
SQL> rollback;
Rollback complete.
--回滾也清除物化檢視日誌內容
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;
no rows selected
SQL> delete from t_test where a=1;
1 row deleted.
---刪除不提交在物化檢視日誌也會記錄對應的內容
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;
A SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
1 4000-01-01 00:00:00 D O 00
---刪除物化檢視
SQL> drop materialized view mv_t_test;
Materialized view dropped.
--構建聚合物化檢視
SQL> create materialized view mv_t_test enable query rewrite as select a,count(b) from t_test group by a;
Materialized view created.
--可知聚合物化檢視首次是全量重新整理，且增量重新整理是有限制條件的
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DIRLOAD_LIMITEDDML COMPLETE 2013-07-18 11:42:44
SQL> select * from t_test;
A B
---------- ----------
2 5
3 6
4 7
5 8
6 9
7 10
8 11
9 12
10 13
11 14
12 15
A B
---------- ----------
13 16
14 17
15 18
16 19
17 20
18 21
19 22
20 23
19 rows selected.
--物化檢視已有值
SQL> select * from mv_t_test;
A COUNT(B)
---------- ----------
6 1
11 1
13 1
2 1
14 1
20 1
4 1
5 1
8 1
17 1
3 1
A COUNT(B)
---------- ----------
7 1
18 1
9 1
10 1
12 1
15 1
16 1
19 1
19 rows selected.
--插入一條記錄
SQL> insert into t_test values(21,28);
1 row created.
--重新整理物化檢視
SQL> exec dbms_mview.refresh('mv_t_test');
PL/SQL procedure successfully completed.
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DIRLOAD_LIMITEDDML COMPLETE 2013-07-18 11:48:04
--即使未提交重新整理物化檢視也會同步新資料
SQL> select * from mv_t_test;
A COUNT(B)
---------- ----------
6 1
11 1
13 1
2 1
14 1
20 1
21 1
4 1
5 1
8 1
17 1
A COUNT(B)
---------- ----------
3 1
7 1
18 1
9 1
10 1
12 1
15 1
16 1
19 1
20 rows selected.
--增量重新整理報錯
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T_TEST"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL> !oerr ora 12032
12032, 0000, "cannot use rowid column from materialized view log on \"%s\".\"%s\""
// *Cause: The materialized view log either does not have ROWID columns
// logged, or the timestamp associated with the ROWID columns is
// more recent than the last refresh time.
// *Action: A complete refresh is required before the next fast refresh.
// Add ROWID columns to the materialized view log, if required.
//
SQL> exec dbms_mview.refresh('mv_t_test','c');
PL/SQL procedure successfully completed.
--完全重新整理後增量重新整理依舊報錯
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T_TEST"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
--新增rowid到物化檢視日誌
SQL> alter materialized view log force on t_test add rowid;
Materialized view log altered.
---物化檢視日誌多了一個列m_row$$
SQL> desc mlog$_t_test;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
A NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
M_ROW$$
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL> exec dbms_mview.refresh('mv_t_test','c');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-32401: materialized view log on "SCOTT"."T_TEST" does not have new values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
--新增新值到物化檢視日誌
SQL> alter materialized view log force on t_test including new values;
Materialized view log altered.
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL> exec dbms_mview.refresh('mv_t_test','c');
PL/SQL procedure successfully completed.
--新增新值到物化檢視日誌後仍報錯
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
SQL> !oerr ora 12033
12033, 0000, "cannot use filter columns from materialized view log on \"%s\".\"%s\""
// *Cause: The materialized view log either did not have filter columns
// logged, or the timestamp associated with the filter columns was
// more recent than the last refresh time.
// *Action: A complete refresh is required before the next fast refresh.
// Add filter columns to the materialized view log, if required.
//
SQL> alter materialized view log force on t_test add rowid(a);
Materialized view log altered.
SQL> alter materialized view log force on t_test add rowid(a);
Materialized view log altered.
--新增過濾列仍報錯
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
--新增過濾列
SQL> alter materialized view log force on t_test add (b);
Materialized view log altered.
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;
*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
--新增過濾列然後全量重新整理之後增量即可成功
SQL> exec dbms_mview.refresh('mv_t_test','c');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('mv_t_test','f');
PL/SQL procedure successfully completed.
--可知上次是增量重新整理
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DIRLOAD_LIMITEDDML FAST 2013-07-18 12:08:10
SQL> exec dbms_mview.refresh('mv_t_test');
PL/SQL procedure successfully completed.
--不加第二個引數也是增量重新整理
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;
MVIEW_NAME FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST DIRLOAD_LIMITEDDML FAST 2013-07-18 12:11:15
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-766609/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle10g nested materialized view巢狀物化檢視示例OracleZedView巢狀
- materialized view (物化檢視)ZedView
- 建立物化檢視MV ( Materialized View )ZedView
- 轉：物化檢視(Materialized View)介紹ZedView
- oracle10g partition分割槽表與物化檢視materialized viewOracleZedView
- ogg 同步 物化檢視建立限制 Materialized ViewZedView
- zt_yangtinkung_ITPUB知識索引貼——物化檢視materialized view索引ZedView
- 物化檢視(Materialized View)的重新整理回滾約束ZedView
- 物化檢視(Materialized View)的重新整理回滾測試ZedView
- 物化檢視日誌(materialized view log)引起大量Dfs Lock Handle等待ZedView
- oracle10g新特性——物化檢視Oracle
- ZT oracle10g新特性——物化檢視Oracle
- MV (Materialed View) 物化檢視的重新整理組View
- Materialized ViewZedView
- [20121101]物化檢視與表(Materialized Views and Tables).txtZedView
- [20230225]12c Real-time materialized view 實時物化檢視的應用.txtZedView
- 物化檢視妙用__表同步使用物化檢視方法
- 【物化檢視】幾種物化檢視日誌分析
- oracle10g物化檢視之dbms_mview.explain_mviewOracleViewAI
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- oracle物化檢視Oracle
- setting up materialized view sites for oracle10g advanced replication mvZedViewOracle
- drop materialized view hung !!!ZedView
- 物化檢視詳解
- oracle 建立物化檢視Oracle
- Oracle 物化檢視建立Oracle
- 物化檢視 on commitMIT
- 物化檢視日誌表被DROP後建立物化檢視報錯
- 建立Materialed View (物化檢視)時候報錯ORA-01723View
- materialized view 的總結ZedView
- about materialized view and long(turn)ZedView
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle
- 物化檢視的快速重新整理測試與物化檢視日誌
- 普通檢視和物化檢視的區別
- calcite物化檢視詳解
- Oracle物化檢視詳解Oracle
- ORACLE物化檢視測試Oracle
- Oracle 物化檢視案例分享Oracle