I just created a 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:http://docs.oracle.com/cd/E16655_01/server.121/e17749/refresh.htm#DWHSG8366%20
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)
1. PCT --N--
2. REFRESH_COMPLETE --Y--
3. REFRESH_FAST --Y--
4. REWRITE --N--
5. PCT_TABLE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
6. REFRESH_FAST_AFTER_INSERT --Y--
7. REFRESH_FAST_AFTER_ONETAB_DML --Y--
8. REFRESH_FAST_AFTER_ANY_DML --Y--
9. REFRESH_FAST_PCT --N-- PCT is not possible on any of the detail tables in the mater
10. REWRITE_FULL_TEXT_MATCH --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
11. REWRITE_FULL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view
12. REWRITE_PARTIAL_TEXT_MATCH --N-- materialized view cannot support any type of query rewrite
13. REWRITE_PARTIAL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view
14. REWRITE_GENERAL --N-- materialized view cannot support any type of query rewrite
15. REWRITE_GENERAL --N-- query rewrite is disabled on the materialized view
16. REWRITE_PCT --N-- general rewrite is not possible or PCT is not possible on an
17. 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