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