Is ON-COMMIT Complete Refresh Possible?
Last updated on JULY 07, 2017
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Information in this document applies to any platform.
Question: Is On-Commit Complete refresh is possible?
As per Oracle documentation: Oracle Database SQL Language Reference
"ON COMMIT Clause
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.
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.
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