unique index and/or unique constraint on mat view issue
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