Is ON-COMMIT Complete Refresh Possible? (Doc ID 1604527.1)

Last updated on JULY 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

Question: Is On-Commit Complete refresh is possible?

As per Oracle documentation: Oracle Database SQL Language Reference

"ON COMMIT Clause

Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process."

So, per the the documentation, 'On-Commit Refresh' is only possible for Fast Refresh. Whereas the test below shows that, it is possible for Complete Refresh as well.

Changes

See the testcase below:

 This shows that “on-commit complete refresh” does exists.

You do not get any table with the name " MLOG$_T2". That means, on-commit is happening when no mlog table is there, this proves that it's not fast refresh.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms