oracle10g nested materialized view巢狀物化檢視示例
SQL> create table t_emp(empno int,ename varchar2(100),deptno int);
Table created.
SQL> alter table t_emp add primary key(empno);
Table altered.
SQL> create table t_dept(deptno int primary key,dname varchar2(100));
Table created.
SQL> create materialized view log on t_emp;
Materialized view log created.
SQL> create materialized view log on t_dept;
Materialized view log created.
--構建join多表的物化檢視必須指定選項refresh force or refresh complete,不能是refresh fast
SQL> create materialized view mv_emp_dept refresh fast as select t_emp.empno,t_emp.ename,t_dept.dname from t_emp,t_dept where t_emp.deptno=t_dept.deptno;
create materialized view mv_emp_dept refresh fast as select t_emp.empno,t_emp.ename,t_dept.dname from t_emp,t_dept where t_emp.deptno=t_dept.deptno
*
ERROR at line 1:
ORA-12052: cannot fast refresh materialized view SCOTT.MV_EMP_DEPT
SQL>create materialized view mv_emp_dept refresh force as select t_emp.empno,t_emp.ename,t_dept.dname from t_emp,t_dept where t_emp.deptno=t_dept.deptno
Materialized view created
SQL> exec dbms_mview.refresh('mv_emp_dept');
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_REFR
------------------------------ ------------------ -------- ---------
MV_EMP_DEPT DIRLOAD_DML COMPLETE 18-JUL-13
--join多表的物化檢視必須，在基表構建rowid的物化檢視日誌
SQL> exec dbms_mview.refresh('mv_emp_dept','f');
BEGIN dbms_mview.refresh('mv_emp_dept','f'); END;
*
ERROR at line 1:
ORA-12032: cannot use rowid column from materialized view log on
"SCOTT"."T_DEPT"
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 on t_emp add rowid;
Materialized view log altered.
SQL> alter materialized view log on t_dept add rowid;
Materialized view log altered.
SQL> exec dbms_mview.refresh('mv_emp_dept','f');
BEGIN dbms_mview.refresh('mv_emp_dept','f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_DEPT" 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_emp_dept','c');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('mv_emp_dept','f');
PL/SQL procedure successfully completed.
---檢視物化檢視日誌相關資訊
select log_owner,master,log_table,rowids,primary_key,object_id,filter_columns,sequence,include_new_values from user_mview_logs
LOG_OWNER MASTER LOG_TABLE ROW PRI OBJ FIL SEQ INC
------------------------------ ------------------------------ --------------- --- --- --- --- --- ---
SCOTT T_DEPT MLOG$_T_DEPT YES YES NO NO NO NO
SCOTT T_EMP MLOG$_T_EMP YES YES NO NO NO NO
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> exec dbms_mview.refresh('mv_emp_dept','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_EMP_DEPT DIRLOAD_DML FAST 2013-07-18 16:50:47
--基於join多表的物化檢視再次構建物化檢視日誌
SQL> create materialized view log on mv_emp_dept with rowid;
Materialized view log created.
--構建巢狀物化檢視
SQL> create materialized view nest_emp_dept refresh force as select dname,count(empno) as cnt from mv_emp_dept group by dname;
Materialized view created.
SQL> select * from mv_emp_dept;
EMPNO ENAME DNAME
---------- ---------- ----------
1 zxy tech
2 zxb sale
SQL> select * from nest_emp_dept;
DNAME CNT
---------- ----------
sale 1
tech 1
SQL> select * from t_emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1 zxy 1
2 zxb 2
SQL> select * from t_dept;
DEPTNO DNAME
---------- ----------
1 tech
2 sale
SQL> select * from mv_emp_dept;
EMPNO ENAME DNAME
---------- ---------- ----------
1 zxy tech
2 zxb sale
SQL> select * from nest_emp_dept;
DNAME CNT
---------- ----------
sale 1
tech 1
SQL> insert into t_emp values(3,'zxd',3);
1 row created.
SQL> insert into t_dept values(3,'design');
1 row created.
SQL> commit;
Commit complete.
SQL> var x number
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true);
BEGIN dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true); END;
*
ERROR at line 1:
ORA-32401: materialized view log on "SCOTT"."MV_EMP_DEPT" does not have new values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2918
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2889
ORA-06512: at line 1
SQL> alter materialized view log on mv_emp_dept add including new values;
Materialized view log altered.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true);
BEGIN dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."MV_EMP_DEPT" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2918
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2889
ORA-06512: at line 1
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'c',nested=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true);
BEGIN dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true); END;
*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."MV_EMP_DEPT"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2918
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2889
ORA-06512: at line 1
SQL> alter materialized view log on mv_emp_dept add (empno,ename,dname);
Materialized view log altered.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true);
BEGIN dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true); END;
*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."MV_EMP_DEPT"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2918
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2889
ORA-06512: at line 1
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'c',nested=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'f',nested=>true);
PL/SQL procedure successfully completed.
--巢狀物化檢視沒有及時重新整理,指定方式不對
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'mv_emp_dept',method=>'cf',nested=>true);
PL/SQL procedure successfully completed.
SQL> select * from mv_emp_dept;
EMPNO ENAME DNAME
---------- ---------- ----------
1 zxy tech
2 zxb sale
3 zxd design
SQL> select * from nest_emp_dept;
DNAME CNT
---------- ----------
sale 1
tech 1
design 1
--針對巢狀物化檢視必須直接重新整理底層基表，而非其上的物化檢視
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_emp',method=>'c',nested=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_dept',method=>'c',nested=>true);
PL/SQL procedure successfully completed.
SQL> select * from mv_emp_dept;
EMPNO ENAME DNAME
---------- ---------- ----------
1 zxynewly tech
2 zxb sale
3 zxd design
SQL> select * from nest_emp_dept;
DNAME CNT
---------- ----------
sale 1
tech 1
design 1
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_dept',method=>'f',nested=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_emp',method=>'f',nested=>true);
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_EMP_DEPT DIRLOAD_DML COMPLETE 2013-07-18 17:28:21
NEST_EMP_DEPT DIRLOAD_LIMITEDDML COMPLETE 2013-07-18 17:28:21
SQL>
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_dept',method=>'f',nested=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_emp',method=>'f',nested=>true);
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_EMP_DEPT DIRLOAD_DML FAST 2013-07-18 17:28:48
NEST_EMP_DEPT DIRLOAD_LIMITEDDML FAST 2013-07-18 17:28:48
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_emp',method=>'c',nested=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh_dependent(number_of_failures=>:x,list=>'t_dept',method=>'f',nested=>true);
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_EMP_DEPT DIRLOAD_DML FAST 2013-07-18 17:29:29
NEST_EMP_DEPT DIRLOAD_LIMITEDDML FAST 2013-07-18 17:29:29
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/500314/viewspace-1164628/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle10g 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
- 各種檢視的巢狀巢狀
- Java | 靜態巢狀類（Static Nested Class）Java巢狀
- MV (Materialed View) 物化檢視的重新整理組View
- Materialized ViewZedView
- [20121101]物化檢視與表(Materialized Views and Tables).txtZedView
- [20230225]12c Real-time materialized view 實時物化檢視的應用.txtZedView
- 物化檢視妙用__表同步使用物化檢視方法
- 【物化檢視】幾種物化檢視日誌分析
- oracle10g物化檢視之dbms_mview.explain_mviewOracleViewAI
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- [轉]Trees in SQL: Nested Sets and Materialized PathSQLZed
- oracle物化檢視Oracle
- setting up materialized view sites for oracle10g advanced replication mvZedViewOracle
- drop materialized view hung !!!ZedView
- 物化檢視詳解
- oracle 建立物化檢視Oracle
- Oracle 物化檢視建立Oracle
- 物化檢視 on commitMIT
- 物化檢視日誌表被DROP後建立物化檢視報錯
- [譯] D3.js 巢狀選擇集 (Nested Selection)JS巢狀
- 關於迴圈巢狀nested loops的一點分析巢狀OOP
- 建立Materialed View (物化檢視)時候報錯ORA-01723View
- materialized view 的總結ZedView
- about materialized view and long(turn)ZedView
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle