Hi Connor,

I asked this question on OTN:
We have some mviews with joins, and we found out sometimes an update on the base table will result in insert+delete on the mview, not delete+insert. Because insert comes first, we have to make the primary key or unique constraint deferrable. This is fine, but in case the mview is replicated by GG, on the target site it will delete by primary key, and after the refresh it deletes not only the old data but also the new one. If delete comes before insert then this will not be an issue. What is the reason behind this implementation?

Below is a test case to reproduce it:

jsu@JSU12P> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

-- Create two tables for the test case

CREATE TABLE test_parent (
pid number not null constraint test_parent_pk primary key
,c1 number
,c2 number
)
/

CREATE MATERIALIZED VIEW LOG ON test_parent WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

CREATE TABLE test_child (
cid number not null constraint test_CHILD_pk primary key
,c3 number
,c4 number
,pid number constraint test_child_fk references test_parent(pid)
)
/

CREATE MATERIALIZED VIEW LOG ON test_child WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

INSERT INTO test_parent VALUES (1,1,1);
INSERT INTO test_child VALUES (1,1,1,1);
COMMIT;

------ Now create the mview

CREATE MATERIALIZED VIEW MV_TEST
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT p.ROWID AS P_ROWID
,c.rowid AS C_ROWID
,c.cid
,c.c3
,c.c4
,c.pid
,p.c1 + 123 as c1
from test_child c, test_parent p
WHERE c.pid = p.pid(+)
/

ALTER TABLE MV_TEST ADD CONSTRAINT MV_TEST_PK PRIMARY KEY(cid) DEFERRABLE INITIALLY DEFERRED;

select * from MV_TEST;


--------------- set up a trigger and audit table for observation of the refresh behavior

create sequence mv_audit_seq order;

create table mv_audit (seq number,dml varchar2(10),cid number);

create or replace trigger trg_mv_test
after insert or delete or update on MV_TEST for each row
declare
v_dml varchar2(10);
begin
v_dml := case when inserting then 'I'
when deleting then 'D'
when updating then 'U'
end;

insert into mv_audit values (mv_audit_seq.nextval,v_dml,:new.cid);

end;
/

------- run the below DML to reproduce this issue. An update on the parent table will trigger the unusual behavior in mview refresh

jsu@JSU12P> UPDATE test_parent SET C1=NVL(C1,0)+1;

1 row updated.

jsu@JSU12P> COMMIT;

Commit complete.

jsu@JSU12P> EXEC DBMS_MVIEW.REFRESH('MV_TEST','F');

PL/SQL procedure successfully completed.

jsu@JSU12P> select * from mv_audit order by seq;



SEQ DML CID
---------- ---------- ----------
1 U 1
2 I 1
3 D

------ as you can see, it first did an update, then insert, then delete
------ delete happened after insert
------ at this time, if you have GG replication, the source mv_test has one row, target mv_test has zero row because when it deleted the target table by primary key cid=1, the newly inserted row was also deleted