6 Replies Latest reply: Apr 9, 2010 10:44 AM by 611900 RSS

    unique index and/or unique constraint on mat view issue

    Mark Reichman
      10.2.0.3

      I have an OLTP table and a fast refresh mat view of the table in the warehouse. I have unique indexes on the mat view just like I have on the OLTP table. Evidently thats a bad idea because the Oracle refresh mechanism on mat views does not apply the dml in the same order that it occured on the OLTP side? Should I get rid of all the unique indexes on the mat views in my warehouse and make them regular indexes since their uniqueness will just happen becuase the OLTP side has a unique index? How will that affect query performance? Here is the alert log...

      Alert log from owp2
      =======================
      ORA-12012: error on auto execute of job 1595
      ORA-12008: error in materialized view refresh path
      ORA-00001: unique constraint (SMS_AR.IU02_ROUTE_REF_MRKR) violated
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 510
      ORA-06512: at line 1
      ORA-00001: unique constraint (SMS_AR.IU02_ROUTE_REF_MRKR) violated
      ORA-00610: Internal error code
      ORA-12012: error on auto execute of job 260282
      ORA-30439: refresh of 'ORA-30439: refresh of 'SMS_AR.MV_ROUTE_REF_MRKR' failed because of ORA-12008: error in materialized view refresh path
      ORA-00001: unique constraint (SMS_AR.IU02_ROUTE_REF_MRKR) violated
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
      ORA-06512: at "SMS_AR.PA_PIES_WAREHOUSE", line 44
      ORA-06512: at line 2
      ORA-20000: index "SMS_AR"."I01_MV_PIES_INV_REFMKR" or partition of such index is in unusable state
        • 1. Re: unique index and/or unique constraint on mat view issue
          user503699
          Are you sure that the errors you are getting are because of the way changes are applied to the materialized view ?
          Not that I am doubting your findings, but just want you to make sure that you are solving the correct problem.
          Also, is there any specific reason for defining indexes on materialized views as unique ?
          As the MVs are in datawarehouse, I am sure you are never planning to update the MVs.
          It would be interesting to know if there is any performance difference between index unique scan and shared scan
          on MVs. I have not tested this but would be interested to know your findings.
          • 2. Re: unique index and/or unique constraint on mat view issue
            Mark Reichman
            This is all I could find so far on this.. This guy had a unique constraint which caused his problem.

            http://oracletoday.blogspot.com/2007/08/unique-indexes-on-materialized-views.html

            I tried to look up this metalink note 284101.1 referred to in the link above but it does not exist.

            I guess Im just looking for confirmation from more experienced warehouse developers that a regular index is normal practice in place of unique index on a fast refresh mat view?

            Edited by: Mark Reichman on Nov 5, 2008 1:07 PM
            • 3. Re: unique index and/or unique constraint on mat view issue
              Tubby
              Any chance you can modify the constraint to be deferrable?
              ME_XE?create table test_deferred
                2  (
                3     column1 number(38)
                4  );

              Table created.

              Elapsed: 00:00:00.01
              ME_XE?
              ME_XE?alter table test_deferred add constraint t0010 unique (column1) deferrable initially deferred ;

              Table altered.

              Elapsed: 00:00:00.07
              ME_XE?insert into test_deferred values (1);

              1 row created.

              Elapsed: 00:00:00.01
              ME_XE?insert into test_deferred values (1);

              1 row created.

              Elapsed: 00:00:00.00
              ME_XE?commit;
              commit
              *
              ERROR at line 1:
              ORA-02091: transaction rolled back
              ORA-00001: unique constraint (TUBBY.T0010) violated


              Elapsed: 00:00:00.03
              • 4. Re: unique index and/or unique constraint on mat view issue
                Mark Reichman
                I think this issue is solved.. Unless somebody has something else to add. I have not tried this yet.. But it appears to be valid. I did a test and a deferrable unique constraint actually creates a non-unique index. So, i need to drop my unique indexes on my mat views and create deferrable unique constraints.

                Or...

                forget about the unqiue part and just change the indexes to non-unique since the master table has a unique index and enforces uniqueness for me and the mat view will just copy whats in the master table.


                http://www.jlcomp.demon.co.uk/MV_non_bug.html
                From the above link....

                This is the guilty line:

                create unique index t1_mv_u1 on t1_mv(n1,v1,n2) ;

                Change this to:

                alter table t1_mv add constraint t1_mv_u1 unique (n1,v1,n2) deferrable;

                Note particularly the deferrable – when we do our fast refresh there is a moment where we have to have a duplicate row in the table – either we change the 1 to a 2 first, or we change the 2 to a 1 first – the refresh updates one row at a time, it doesn’t do an array update. By making the constraint deferrable we get a non-unique index protecting the constraint, and Oracle only re-checks the validity of the constraint on the commit, not for each update statement. I believe there is something in the data warehouse manuals about making unique (and primary key) constraints on materialized views deferrable for exactly this reason.
                • 5. Re: unique index and/or unique constraint on mat view issue
                  Randolf Geist
                  Mark Reichman wrote:
                  I think this issue is solved.. Unless somebody has something else to add. I have not tried this yet.. But it appears to be valid. I did a test and a deferrable unique constraint actually creates a non-unique index. So, i need to drop my unique indexes on my mat views and create deferrable unique constraints.

                  Or...

                  forget about the unqiue part and just change the indexes to non-unique since the master table has a unique index and enforces uniqueness for me and the mat view will just copy whats in the master table.
                  Mark,

                  the solution sounds reasonable. Just one side note: If you use a "deferrable" unique constraint Oracle silently ignores any attempts to perform a direct-path insert and always resorts to conventional insert generating undo and much more redo.

                  As long as you're performing only a "fast" refresh this shouldn't matter, but in case you're dealing with full refreshs of large MVs, this might make a significant difference when performing non-atomic refreshs (which can take advantage of direct-path inserts / parallel DML etc.).

                  So you might want to consider to use just the non-unique index instead of the deferrable constraint if performance might matter and given the fact that you should never see any duplicates in the MV due to the constraint on the base table.

                  By the way, you can use a non-unique index to enforce a non-deferrable unique/primary key constraint as well, it's supported. You just need to create the index yourself either before defining the constraint or by using the explicit "CREATE INDEX" syntax of the constraint clause.

                  For more information, I've written a short note about this "deferrable" issue some time ago:

                  http://oracle-randolf.blogspot.com/2008/07/deferrable-constraints-and-direct-path.html

                  Regards,
                  Randolf

                  Oracle related stuff blog:
                  http://oracle-randolf.blogspot.com/

                  SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                  http://www.sqltools-plusplus.org:7676/
                  http://sourceforge.net/projects/sqlt-pp/
                  • 6. Re: unique index and/or unique constraint on mat view issue
                    611900
                    I have an issue which is somewhat similar to this one. Hope you will be able to shed some light on this -

                    I have some MVs. At the time of doing REFRESH of the Materialized View, we are getting error like -

                    SQL> EXECUTE DBMS_MVIEW.REFRESH('MV_TEST.MY_VIEW,'CF');
                    BEGIN DBMS_MVIEW.REFRESH('MV_TEST.MY_VIEW,'CF'); END;

                    *
                    ERROR at line 1:
                    ORA-12008: error in materialized view refresh path
                    ORA-00001: unique constraint (MV_TEST.I_SNAP$_FIN_RESULT_EXPENSE1) violated
                    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
                    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
                    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
                    ORA-06512: at line 1

                    I tried to drop and recreate the index I_SNAP$_FIN_RESULT_EXPENSE1 but it didn’t helped.

                    Later I dropped the index I_SNAP$_FIN_RESULT_EXPENSE1 and refreshed the materialized view and it was successful (obvious). But when I tried to recreate the index, I got the following error –

                    SYS_OP_MAP_NONNULL("FIN_MULT"), SYS_OP_MAP_NONNULL("FIN_CD"))
                    *
                    ERROR at line 8:
                    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

                    When I look at the value of SYS_OP_MAP_NONNULL('FIN_CD'), it seems to be unique.

                    SQL> select SYS_OP_MAP_NONNULL('FIN_CD') from dual;

                    SYS_OP_MAP_NONNULL('FIN_CD’)
                    ----------------------------------
                    46494E5F5650545F4C564C5F335F434400

                    My questions are:

                    1.     Why are the indexes (I_SNAP$_*) automatically created for?
                    2.     What is the purpose of these (I_SNAP$_*) indexes?
                    3.     What harm can be there if we drop these (I_SNAP$_*) indexes?
                    4.     Why it is complaining about the Duplicate records / Uniqueness?
                    5.     How to know where exactly the problem is in this case?
                    6.     What is the solution for this problem? How to recreate these indexes successfully?


                    Thanks!