物化视图增量方式

down vot favorite 2

I just created tables DEPT and EMP like follow : create table DEPT ( dept_no number , dept_name varchar(32) , dept_desc varchar(32), CONSTRAINT dept_pk Primary Key (dept_no) ); create table EMP ( emp_no number, dept_no number, CONSTRAINT emp_pk Primary Key (emp_no,dept_no)); insert into dept values (10,'it','desc1'); insert into dept values (20,'hr','desc2'); insert into emp values (1,10); insert into emp values (2,20); I created materialized view logs on these tables with rowid and materialized views as follows: create materialized view log on emp with rowid; create materialized view log on dept with rowid; create materialized view empdept_mv refresh fast on commit as select a.rowid dept_rowid, b.rowid emp_rowid, a.dept_no,b.emp_no from dept a, emp b where a.dept_no=b.dept_no ; select * from emp; EMP_NO DEPT_NO ---------- ---------- 1 10 2 20 3 30 select * from dept; DEPT_NO DEPT_NAME DEPT_DESC ---------- -------------------------------- -------------------------------- 10 it desc1 20 hr desc2 30 it desc3 select * from empdept_mv; DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 I inserted a new record and did COMMIT; ..but still when i check the materialized view, the new record is not shown in the materialized view. insert into dept values (30,'it','desc3'); commit; insert into emp values (3,30); commit; select * from empdept_mv; DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 Now, when I run the procedure for Fast and complete refresh as per, The Fast refresh does not update the Mview but the complete refresh does. ( Note: But the Mview is still REFRESH ON COMMIT) execute DBMS_MVIEW.REFRESH('empdept_mv', 'F', '', TRUE, FALSE, 0,0,0,FALSE, FALSE); PL/SQL procedure successfully completed. DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 execute DBMS_MVIEW.REFRESH('test_mview2', 'C', '', TRUE, FALSE, 0,0,0,FALSE, FALSE); PL/SQL procedure successfully completed. DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 AAAli5AABAAAPZ6AAC AAAli7AABAAAQs6AAC 30 3 The DBMS_MVIEW.EXPLAIN_MVIEW output is as shown : (capability_name --Possible-- msgtxt) PCT --N-- REFRESH_COMPLETE --Y-- REFRESH_FAST --Y-- REWRITE --N-- PCT_TABLE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details REFRESH_FAST_AFTER_INSERT --Y-- REFRESH_FAST_AFTER_ONETAB_DML --Y-- REFRESH_FAST_AFTER_ANY_DML --Y-- REFRESH_FAST_PCT --N-- PCT is not possible on any of the detail tables in the mater REWRITE_FULL_TEXT_MATCH --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details REWRITE_FULL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view REWRITE_PARTIAL_TEXT_MATCH --N-- materialized view cannot support any type of query rewrite REWRITE_PARTIAL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view REWRITE_GENERAL --N-- materialized view cannot support any type of query rewrite REWRITE_GENERAL --N-- query rewrite is disabled on the materialized view REWRITE_PCT --N-- general rewrite is not possible or PCT is not possible on an PCT_TABLE_REWRITE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details How can I achieve Fast Refresh On Commit ? The Oracle Version details are as follows: NLSRTL 10.2.0.4.0 Production Oracle Database 10g 10.2.0.4.0 64bit Production PL/SQL 10.2.0.4.0 Production TNS for Linux: 10.2.0.4.0 Production

7 down vote favorite 2

I just created tables DEPT and EMP like follow : create table DEPT ( dept_no number , dept_name varchar(32) , dept_desc varchar(32), CONSTRAINT dept_pk Primary Key (dept_no) ); create table EMP ( emp_no number, dept_no number, CONSTRAINT emp_pk Primary Key (emp_no,dept_no)); insert into dept values (10,'it','desc1'); insert into dept values (20,'hr','desc2'); insert into emp values (1,10); insert into emp values (2,20); I created materialized view logs on these tables with rowid and materialized views as follows: create materialized view log on emp with rowid; create materialized view log on dept with rowid; create materialized view empdept_mv refresh fast on commit as select a.rowid dept_rowid, b.rowid emp_rowid, a.dept_no,b.emp_no from dept a, emp b where a.dept_no=b.dept_no ; select * from emp; EMP_NO DEPT_NO ---------- ---------- 1 10 2 20 3 30 select * from dept; DEPT_NO DEPT_NAME DEPT_DESC ---------- -------------------------------- -------------------------------- 10 it desc1 20 hr desc2 30 it desc3 select * from empdept_mv; DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 I inserted a new record and did COMMIT; ..but still when i check the materialized view, the new record is not shown in the materialized view. insert into dept values (30,'it','desc3'); commit; insert into emp values (3,30); commit; select * from empdept_mv; DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 Now, when I run the procedure for Fast and complete refresh as per, The Fast refresh does not update the Mview but the complete refresh does. ( Note: But the Mview is still REFRESH ON COMMIT) execute DBMS_MVIEW.REFRESH('empdept_mv', 'F', '', TRUE, FALSE, 0,0,0,FALSE, FALSE); PL/SQL procedure successfully completed. DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 execute DBMS_MVIEW.REFRESH('test_mview2', 'C', '', TRUE, FALSE, 0,0,0,FALSE, FALSE); PL/SQL procedure successfully completed. DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO ------------------ ------------------ ---------- ---------- AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1 AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2 AAAli5AABAAAPZ6AAC AAAli7AABAAAQs6AAC 30 3 The DBMS_MVIEW.EXPLAIN_MVIEW output is as shown : (capability_name --Possible-- msgtxt) PCT --N-- REFRESH_COMPLETE --Y-- REFRESH_FAST --Y-- REWRITE --N-- PCT_TABLE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details REFRESH_FAST_AFTER_INSERT --Y-- REFRESH_FAST_AFTER_ONETAB_DML --Y-- REFRESH_FAST_AFTER_ANY_DML --Y-- REFRESH_FAST_PCT --N-- PCT is not possible on any of the detail tables in the mater REWRITE_FULL_TEXT_MATCH --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details REWRITE_FULL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view REWRITE_PARTIAL_TEXT_MATCH --N-- materialized view cannot support any type of query rewrite REWRITE_PARTIAL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view REWRITE_GENERAL --N-- materialized view cannot support any type of query rewrite REWRITE_GENERAL --N-- query rewrite is disabled on the materialized view REWRITE_PCT --N-- general rewrite is not possible or PCT is not possible on an PCT_TABLE_REWRITE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details How can I achieve Fast Refresh On Commit ? The Oracle Version details are as follows: NLSRTL 10.2.0.4.0 Production Oracle Database 10g 10.2.0.4.0 64bit Production PL/SQL 10.2.0.4.0 Production TNS for Linux: 10.2.0.4.0 Production