title: Fast Refresh on commit of materialized view
body: <p>I just created tables DEPT and EMP like follow :</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>I created materialized view logs on these tables with rowid and materialized views as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Now, when I run the procedure for Fast and complete refresh as <a href="http://docs.oracle.com/cd/E16655_01/server.121/e17749/refresh.htm#DWHSG8366%20" rel="noreferrer">per</a>, The Fast refresh does not update the Mview but the complete refresh does. ( Note: <strong>But the Mview is still REFRESH ON COMMIT</strong>)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The DBMS_MVIEW.EXPLAIN_MVIEW output is as shown : (capability_name --Possible-- msgtxt)</p>
  
  <ol>
  <li>PCT --N--</li>
  <li>REFRESH_COMPLETE --Y--</li>
  <li>REFRESH_FAST --Y--</li>
  <li>REWRITE --N--</li>
  <li>PCT_TABLE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for
  details</li>
  <li>REFRESH_FAST_AFTER_INSERT --Y--</li>
  <li>REFRESH_FAST_AFTER_ONETAB_DML --Y--</li>
  <li>REFRESH_FAST_AFTER_ANY_DML --Y--</li>
  <li>REFRESH_FAST_PCT --N-- PCT is not possible on any of the detail
  tables in the mater</li>
  <li>REWRITE_FULL_TEXT_MATCH --N-- Oracle error: see RELATED_NUM and
   RELATED_TEXT for details</li>
  <li>REWRITE_FULL_TEXT_MATCH --N-- query rewrite is disabled on the
   materialized view</li>
  <li>REWRITE_PARTIAL_TEXT_MATCH --N-- materialized view cannot support
   any type of query rewrite</li>
  <li>REWRITE_PARTIAL_TEXT_MATCH --N-- query rewrite is disabled on the
   materialized view</li>
  <li>REWRITE_GENERAL --N-- materialized view cannot support any type of
   query rewrite</li>
  <li>REWRITE_GENERAL --N-- query rewrite is disabled on the materialized
   view</li>
  <li>REWRITE_PCT --N-- general rewrite is not possible or PCT is not
   possible on an</li>
  <li>PCT_TABLE_REWRITE --N-- Oracle error: see RELATED_NUM and
   RELATED_TEXT for details</li>
  </ol>
  
  <p><strong>How can I achieve Fast Refresh On Commit ?</strong><br>
  The Oracle Version details are as follows:<br>
  NLSRTL                  10.2.0.4.0  Production<br>
  Oracle Database 10g     10.2.0.4.0  64bit Production<br>
  PL/SQL              10.2.0.4.0  Production<br>
  TNS for Linux:          10.2.0.4.0  Production  </p>