this is a question related to Oracle 12c.
I'm currently trying to create MVs as data sources for Golden Gate replication. We need fast refresh because We have to replicate 60,000 rows + at one time and deleting and reinserting all rows seem to be inefficient.
The base tables have unique columns which act as composite primary keys but there's no primary key constraint defined in them. They're peoplesoft tables so We prefer not to add pk constraints if possible, as They're used in so many applications.
I have built the MVs using only WITH ROWID clause for the materialized view logs and They seem to be working fine. But I don't know if it's a good practice to do so, should I add PK constraints on all the master tables instead? or maybe there's another way?
Feedbacks are appreciated. Thanks.
Best Answer
ROWIDs are good to use in a transient sense, but they're a poor choice for long-term storage.
"You should not use
ROWIDas the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later." Source: https://docs.oracle.com/database/121/SQLRF/pseudocolumns008.htm#SQLRF00254You may need to import and export this database when you upgrade the solution or when you refresh a nonproduction environment. If you do decide to use the ROWID anyway, then a complete refresh of the materialized view would be prudent whenever you copy or move data.