Materialized view and unique constraints/indexes

There is well-known issue with the ability of MV-based replication to keep referencial integrity constraints on the replication side. Oracle provides such mechanisms as refresh groups, atomic transaction refersh and deferred constraints to support referenctial integrity.

But apart from this issue there is another one what is related to how replication mechanisms treat changes in the master table.

If we switch on tracing event 10046 and perform DBMS_MVIEW.REFRESH for a single on prebuilt table fast refreshable MV based on primary key, we can see something like below (generic code):

SELECT DISTINCT LOG$.{PK COLUMN} 
FROM
(SELECT MLOG$.{PK COLUMN} FROM {MVIEW_LOG} MLOG$ WHERE "SNAPTIME$$" > :1 
  AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$.{PK COLUMN}) NOT IN (SELECT MAS_TAB$.{PK COLUMN} 
  FROM  "MAS_TAB$" WHERE LOG$.{PK COLUMN} = MAS_TAB$.{PK COLUMN})

DELETE FROM {PREBUILD TABLE} SNAP$ 
WHERE "X" = :1

SELECT CURRENT$.{COLUMN LIST} 
FROM
 (SELECT {COLUMN LIST} FROM {MASTER TABLE}) CURRENT$, 
  (SELECT DISTINCT MLOG$.{PK COLUMN} FROM {MV LOG} MLOG$ WHERE 
  "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$.{PK COLUMN} = 
  LOG$.{PK COLUMN}

UPDATE {PREBUILD TABLE} SET {COLUMNS...} = {VALUES...}
WHERE {PK COLUMN} = :1  

INSERT INTO   (COLUMN LIST) VALUES (VALUES LIST)

As we can see this pseudo-code selects promary key values of rows what are related to DELETE operation (DMLTYPE$$ != ‘I’), checks what these rows don’t exist and deletes them (in batch or row-by-row but using single-row operator DELETE).

The next part of the code selects values what are related to “inserted” rows (we should take into account UPDATE operation is treated as DELETE+INSERT). To differ UPDATE and INSERT the code seems is trying to UPDATE row with specific primary key. If affected row does not exist the code insertes new row.

It’s essential to notice what refresh mechanism breaks down changes to separate SQL operators event the original statement changing master table was a single multi-row operator. This behaviour leads to the curious effect when master table and it’s replica have same unique constraints or unique indexes apart from primary keys.

Let’s create mater table, materialized view log, similar prebuilt table and materialized view:

SQL> create table t_m (x int primary key, y int);

Table created.

SQL> create materialized view log on t_m;

Materialized view log created.

SQL> create table t_v (x int primary key, y int);

Table created.

SQL> edit
Wrote file afiedt.buf

  1  create materialized view tikka_v
  2  on prebuilt table
  3  refresh fast on demand
  4  start with sysdate+0 next sysdate+1
  5  with primary key
  6* as select * from tikka
SQL> /

Materialized view created.

Now we should create two unique indexes on master and replication side.

SQL> create unique index t_m_u001 on t_m(y);

Index created.

SQL> create unique index t_v_u001 on t_v(y);

Index created.

Now we may create the initial set of data and check refresh process (I put sysdate+1 into MV specification in purpose to excude Oracle regular replication job influence).

SQL> insert into t_m values(1,1);

1 row created.

SQL> insert into t_m values(2,2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_refresh.refresh('t_v')

PL/SQL procedure successfully completed.

SQL> select * from t_v;

         X          Y
---------- ----------
         1          1
         2          2

And now let’s execute UPDATE statement what exchanges values in Y column. As Oracle states conceptually it guarantees the data consistency at the end of DLM operator execution if we speak of non-deferred constraints). But while DML is being executed the data integrity may be violated.

SQL> update t_m set y = case when x = 1 then 2 when x = 2 then 1 end;

2 rows updated.

SQL> select * from t_m;

         X          Y
---------- ----------
         1          2
         2          1

SQL> commit;

Commit complete.

And now let’s try to replicate:

SQL> exec dbms_refresh.refresh('t_v')
BEGIN dbms_refresh.refresh('t_v'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (SCOTT.T_V_U001) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Wow ! But why ? Just because the scenario of replication built-in functionality. Oracle selected new values in updated rows and issued separate UPDATE statements to update the row with X=1 and X=2. No wonder when it tried to issue UPDATE t_v SET x=1, y= 2 WHERE x= 1; it stuck on the exception – because row with Y=2 exists in t_v already.

How we can fix this ? Easy – let’s create deferrable UNIQUE constraint instead of just unique index:

 

SQL> drop index T_V_U001;

Index dropped.

SQL> create index T_V_U001 on tikka_v(y);

Index created.

SQL> alter table t_v add constraint T_V_U001 unique(y)
  2  deferrable initially deferred;

Table altered.

SQL> exec dbms_refresh.refresh('t_v')

PL/SQL procedure successfully completed.

SQL> select * from t_v;

         X          Y
---------- ----------
         1          2
         2          1

The same situation may be in place when we don’t change unique key columns values but exchange primary key values between two rows:

SQL> alter table t_v drop constraint T_V_U001;

Table altered.

SQL> drop index T_V_U001;

Index dropped.

SQL> create unique index T_V_U001 ON T_V(Y)
  2  /

Index created.

SQL> update t_m set x = case when x = 1 then 2 when x = 2 then 1 end;

2 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_refresh.refresh('t_v')
BEGIN dbms_refresh.refresh('t_v'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (SCOTT.T_V_U001) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1


SQL> drop index T_V_U001;

Index dropped.

SQL> select * from t_v;

         X          Y
---------- ----------
         1          2
         2          1

SQL> create index T_V_U001 on t_v(y);

Index created.

SQL> alter table t_v add constraint T_V_U001 unique(y)
  2  deferrable;

Table altered.

SQL> exec dbms_refresh.refresh('t_v')

PL/SQL procedure successfully completed.


SQL> select * from t_v;

         X          Y
---------- ----------
         1          1
         2          2

As you may see in this case I even did not specify INITALLY DEFERRED – replication functionality executes SET CONSTRAINTS ALL DEFERRED automatically.
And, finally, one more scenario:

SQL> delete from t_m where x = 2;

1 row deleted.

SQL> insert into t_m values(3,2);

1 row created.

SQL> insert into t_m values(2,3);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_refresh.refresh('t_v');
BEGIN dbms_refresh.refresh('t_v'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (SCOTT.T_V_U001) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

SQL> drop index T_V_U001;

Index dropped.

SQL> create index T_V_U001 on t_v(y);

Index created.

SQL> alter table t_v add constraint T_V_U001 unique(y)
  2  deferrable;

Table altered.

SQL> exec dbms_refresh.refresh('t_v');

PL/SQL procedure successfully completed.

SQL> select * from t_v;

         X          Y
---------- ----------
         1          1
         2          3
         3          2

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Materialized view and unique constraints/indexes

  1. Skinners says:

    But “Tom” http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5083800171146
    says that the deferrable option will be implemented as a non-unique index, so what is the point?

    • dnikiforov says:

      Really only non-unique indexes are used to support deferrable constraints, but constraints (not indexes) are main in this context. The point is that if we perform some single DML operation on master side and we touch many rows in this operation, this DML can violate constraints during execution (then operation ends the data should be consistend though). But replication functionality breaks the single operation (like UPDATE) down on many single-row operations what can lead to constraint violation on the replication side if we don’t use deferrable constraints (and Oracle directly recommends this approach).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s