Is ON-COMMIT Complete Refresh Possible?
(Doc ID 1604527.1)
Last updated on NOVEMBER 07, 2023
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database 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.
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.
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