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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Steven.

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

Answered by: Connor McDonald - Last updated: July 07, 2016 - 5:57 am UTC

Category: Database - Version: 11.2.0.4

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Table Functions, Part 3a: table functions as parameterized views in the PL/SQL Challenge website

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 we 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.

and you rated our response

  (1 rating)

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

Reviews

Great response

July 07, 2016 - 12:48 pm UTC

Reviewer: Steven Tevault

Excellent! Thanks Connor!