ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g nested materialized view嵌套物化视图示例

oracle10g nested materialized view嵌套物化视图示例

原创 Linux操作系统 作者:wisdomone1 时间:2013-07-18 18:31:16 0 删除 编辑
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/9240380/viewspace-766637/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb 工作经历: 中国普天 北京科蓝 北京云和恩墨 北京神州新桥

注册时间:2008-04-04

  • 博文量
    2189
  • 访问量
    11966278