Fast Refresh on commit of materialized view

Question

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)

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

Answer 1

I don't know if the problem still persists, but as I took a look at the artice you provided, I noticed something (which might just be the solution here):

ON COMMIT Refresh

A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view. The only disadvantage is the time required to complete the commit will be slightly longer because of the extra processing involved. However, in a data warehouse, this should not be an issue because there is unlikely to be concurrent processes trying to update the same table.

• Notice the bold line.

Then we have:

Table 7-1 ON DEMAND Refresh Methods

Refresh Option Parameter Description COMPLETE C Refreshes by recalculating the defining query of the materialized view.

FAST F Refreshes by incrementally applying changes to the materialized view. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.

FAST_PCT P Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.

FORCE ? Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

• Notice the bold lines.
• I personally prefer the FORCE Option.

Could you please tell, if this occurs again after some time (depending of the parameters of the DB and the machine it runs on, so I can't even hint you how much)?

When Fast Refresh is Possible

Not all materialized views may be fast refreshable. Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view.

If you are not sure how to make a materialized view fast refreshable, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure, which provides a script containing the statements required to create a fast refreshable materialized view.

Cheers

Answer 2

I see that you created the materialized view logs with ROWID, which is not really required as both tables have a primary key so you could try without the ROWID.

create materialized view log on emp; create materialized view log on dept;

Additionally, if you create the materialized view log with ROWID you should create the materialized view with rowid.

create materialized view empdept_mv refresh fast on commit WITH ROWID 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 ;

You could try those changes and see if the materialized views fast refresh on commit.