My Oracle Support Banner

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 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!

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.