Fast Refresh of UPDATE DML in 11gR2 Does Delete/Insert Instead of Only UPDATE in 10gR2
Last updated on OCTOBER 16, 2015
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms