Question about unique constraint (DEFERRABLE INITIALLY DEFERRED) in MV refresh
There are 3 ways to set unique constraint and given mv refresh don't guarantee the DML replay sequence. One way to work around it is to set the unique constraint "DEFERRABLE INITIALLY DEFERRED" (case 2). The question is there any benefit to set the defer = immediate? (case1) thx
create table pojen(eiddi number, eiddd number, eidni number, eidnd number) | |
---|---|
create materialized view log on pojen | |
create materialized view mv_pojen as select * from pojen | |
alter table mv_pojen add constraint deferrable_immediate unique (eiddi) DEFERRABLE | case 1 |
alter table mv_pojen add constraint deferrable_deferred unique (eiddd) DEFERRABLE INITIALLY DEFERRED | case 2 |
alter table mv_pojen add constraint notdeferrable_immediate unique (eidni) NOT DEFERRABLE |