Skip to Main Content
  • Questions
  • Materialized view logs and unique indexes

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Steven.

Asked: July 06, 2016 - 7:55 pm UTC

Last updated: July 07, 2016 - 5:57 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked


I have a table with moderate DML activity, a materialized view log on that table, and one materialized view on a remote database. After some activity in the source app, I can see multiple rows in the MV log with the same m_row$$, with a DMLTYPE$ value of 'I' and a snaptime$$ of '01-JAN-4000'. That would seem to suggest to me that the same physical row of the base table was inserted multiple times. When I perform a fast refresh, there are no duplicated rows in the MV... could this occur because the physical row is inserted, deleted, and new data is inserted into the same physical location?

M_ROW$$ SNAPTIME$$ D O CHANGE_VECTOR$$ XID$$
---------------------- -------------------- - - ------------------------------------------------------------------------------------------ ----------
AAAVVrAAlAAHmQYAAC 01-JAN-4000 00:00:00 I N FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 3.9407E+15
AAAVVrAAlAAHmQYAAC 01-JAN-4000 00:00:00 D O 0000000000000000000000000000000000000000000000 4.2222E+15
AAAVVrAAlAAHmQYAAC 01-JAN-4000 00:00:00 I N FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 4.5036E+15
AAAVVrAAlAAHmQYAAH 01-JAN-4000 00:00:00 D O 0000000000000000000000000000000000000000000000 2.8149E+15
AAAVVrAAlAAHmQYAAH 01-JAN-4000 00:00:00 I N FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 5.9110E+15
AAAVVrAAlAAHmQYAAH 01-JAN-4000 00:00:00 I N FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 5.9111E+15


Now I add a UNIQUE index on one column of the MV that is identical to an index on the same unique column of the base table. I perform a fast refresh on the MV, and receive a unique constraint violation. Yet if I make the index nonunique, and perform a fast refresh, the resultant columnar data after refresh is unique. Is this expected behavior? If so, is there a way to ignore the transitionary violation of uniqueness during the MV fast refresh?

--steve

and Connor said...

1) Yes - you can see from the "dmltype" column, it was I/D/I/D etc, ie insert, delete, insert, delete. And as such, a rowid could easily be "reused"

2) For a materialized view, as refreshes occur, the data might be in violation of constraints on a *transient* basis. So you can create a non-unique index, and then enforce uniqueness with a deferrable unique constraint.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great response

Steven Tevault, July 07, 2016 - 12:48 pm UTC

Excellent! Thanks Connor!