03 June, 2008

MVs with Refresh ON COMMIT cannot be used for Synchronous Replication

The question of why Materialized Views with Refresh ON COMMIT cannot be created across databases (in effect providing Synchronous Replication) has come up a few times on the web.
It came up again in this forums posting.

This is my response :
MultiMasterAdvancedReplication works on a PUSH method.
You insert into Table "T_A" in database "D_A" and oracle puts the transaction in the queue tables in "D_A" to be propagated to "D_B". They are then "pushed" from "D_A" to "D_B" via a DBLink. If you option for Synchronous, the "push" is immediate with the transaction on "T_A" and becomes a "2-Phase Commit".

MaterializedViews across DBLinks use a PULL method.
An MV "MV_A" in database "D_B" "pulls" data from "T_A" and "T_A"'sSnapshotLog via a DBLink from "D_B" to read data from "D_A". There is no DBLink from "D_A" to "D_B".
Therefore, a transaction that inserts into "T_A" has no way of pushing the data to "D_B" because there is no DBLink. Therefore it is not possible to have Synchronous MVs across Databases.
If the MV "MV_A" built to REFRESH ON COMMIT is in the same database "D_A" then the transaction inserting into "T_A" merely has to update "MV_A" in the same database -- it just becomes a transaction on *two* *LOCAL* tables inside of "D_A".

No comments: