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博客 ” ，链接：https://blog.itpub.net/500314/viewspace-1164628/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
改进数据库性能-SQL查询优化
下一篇：
显示v$sqltext中完整的sql信息
请登录后发表评论
登录
全部评论
