Unique indexes on materialized views

One of the uses of materialized views is replication. Mviews can be used to replicate a table to another database to prevent users from accessing several databases through database links. This can improve the performance of queries which frequently access that table by removing the latency of the database link.

Today the refresh job of one of the mviews we use for this purpose started getting "ORA-00001 unique constraint violated" error. This was an mview with a unique index on it. After a simple search in Metalink I have found the reason of this behavior.

I will provide a simple test case to reproduce the problem.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> create table master as select rownum r,'ROW'||rownum line from all_objects where rownum<=5; 
Table created.  

SQL> alter table master add primary key(r);

Table altered.

SQL> create unique index master_ind on master(line);

Index created.

SQL> create materialized view log on master with primary key;

Materialized view log created.

SQL> create materialized view snap refresh fast with primary key as select * from master;

Materialized view created.

SQL> create unique index snap_ind on snap(line);

Index created.

SQL> select * from master;

         R LINE
---------- -------------------------------------------
         1 ROW1
         2 ROW2
         3 ROW3
         4 ROW4
         5 ROW5

After these steps we have now one master table, an mview log on it and a fast refreshable mview of the master table. Now we make some updates to the master table to switch the LINE column's data of two rows.

SQL> update master set line='DUMMY' where r=1;

1 row updated.

SQL> update master set line='ROW1' where r=2;

1 row updated.

SQL> update master set line='ROW2' where r=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from master;

         R LINE
---------- -------------------------------------------
         1 ROW2
         2 ROW1
         3 ROW3
         4 ROW4
         5 ROW5

Now when we try to refresh the mview we get the error.

SQL> exec dbms_mview.refresh('SNAP');
BEGIN dbms_mview.refresh('SNAP'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (YAS.SNAP_IND) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

If we drop the unique index and recreate it as nonunique we can refresh the mview without errors.

SQL> drop index snap_ind;

Index dropped.

SQL> create index snap_ind on snap(line);

Index created.

SQL> exec dbms_mview.refresh('SNAP');

PL/SQL procedure successfully completed.

SQL> select * from snap;

         R LINE
---------- -------------------------------------------
         1 ROW2
         2 ROW1
         3 ROW3
         4 ROW4
         5 ROW5

The reason of this error is explained in Metalink note 284101.1. According to that note the refresh mechanism may temporarily leave the data in an inconsistent state during the refresh. The modifications are not made with the same order as the modifications to the master table. Because of this creating unique indexes, foreign keys or primary keys on mviews can cause problems in the refresh process. This totally makes sense as it is better to keep the integrity checks on the master table rather than the mview. An mview is just a copy of the master table, so if we define the integrity constraints on the master table the mview will take care of itself.

This behaviour reproduces in 10G also.