Fast Refresh of UPDATE DML in 11gR2 Does Delete/Insert Instead of Only UPDATE in 10gR2
(Doc ID 2017611.1)
Last updated on OCTOBER 16, 2015
Oracle Database - Enterprise Edition - Version 18.104.22.168 and later Information in this document applies to any platform.
In 11gR2, you see that a fast refresh of UPDATE DML for a replication materialized view (MV) does a two-step DELETE/INSERT, whereas it used to do only a single UPDATE in 10gR2. (A replication MV is one with no joins or aggregates.)
This is expected in 11.2, as the replication-MV fast refresh is enhanced to do "batch processing" by default. For UPDATE changes, two refresh DML statements will handle delete and insert phases, respectively. In 10.2, it was done in a "row-by-row" fashion (even though you saw only a single UPDATE statement).
It is true that the index maintenance load increases for UPDATE only cases. However, the "batch processing" refresh DMLs should greatly outperform the old-style "row-by-row processing" in MV.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!