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

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

 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.)

Changes

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. 

Cause

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