Materialized view with Unique Index

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.

Advertisements

2 thoughts on “Materialized view with Unique Index

  1. Hi Mehraj,

    We are getting a ORA-12008 error accompanied by ORA-01422 error when the materialized view first tries to refresh. Here’s the query and the error:

    CREATE MATERIALIZED VIEW zzz
    REFRESH START WITH ROUND(SYSDATE) + 13/24
    NEXT NEXT_DAY(TRUNC(SYSDATE), ‘THURSDAY’) + 10/24
    from xxx;

    Error:
    =====
    ORA-12012: error on auto execute of job 258
    ORA-12008: error in materialized view refresh path
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 803
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 860
    ORA-06512: at “SYS.DBMS_IREFRESH”, line 683
    ORA-06512: at “SYS.DBMS_REFRESH”, line 195
    ORA-06512: at line 1

    Any idea as to why this error occurs?

    Thanks much.

    Like

    • ORA-12008—SNAP$_ reads rows from the view MVIEW$_, which is a view on the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time. For fast refreshes, the table .MLOG$_ is also referenced.

      The problem is in your Query ,Plz Post the Complete script

      Like

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