My Oracle Support Banner

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

Last updated on MARCH 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.