SQL Language (MOSC)

MOSC Banner

Question about unique constraint (DEFERRABLE INITIALLY DEFERRED) in MV refresh

in SQL Language (MOSC) 1 commentAnswered ✓

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center