0

I have to create Materialized View (MV) on few source tables. Since records count is huge, we want to refresh it in incremental way i.e. refresh only those records which are updated. But, source tables do not have MV log created on them and it won't be created as owner refused to do so.

What are other options for me to refresh MV without MV log ?

I heard there is some option in oracle 19c where we can create some hash value while creating MV. And then based on hash value, we can refresh only updated records. I could not try it as I'm not much aware of it.

Pravin
  • 1
  • 2
  • If no access to fast refresh, you can drop the MV keeping the associated table and do the refresh with triggers... the complexity will depend on the query on which the MV is based (JOINs, aggregates, analytics, ...). – p3consulting Mar 13 '23 at 08:22

1 Answers1

0

If the table owner refuses to allow an mv log, they are also going to refuse a trigger (for populating your own change log).

Failing these methods, is there a metadata column like "last updated time" that always gets moved forward when the data is updated? And, are there any deletes that happen? If yes to the first question and no to the second, you can simply use a "last retrieved value" mechanism for incremental pulls:

  1. Retrieve all rows
  2. Record the maximum "last updated time" column value
  3. On next retrieval, pull only rows => that value. Use merge to insert and/or update.

If however deletes occur on the source, this won't work. And if there isn't a metadata column that is religiously updated, or that gets updated out of order, then it won't work either. In this case, you have no choice but to pull 100% of the table every time.

Of course, you don't have to replace the rows in your target every time in any event. If you pull 100% of the source rows to a temp table and then make two MERGE passes on it and the target, you can look for new/changed rows on the first pass, and deleted rows on the second. At least you aren't doing unnecessary block changes. If the source table is local, you can bypass the temp table altogether and your process is a SP that does two merge statements. It's often advantageous to do your own "MV" logic rather than use Oracle's, precisely for these kinds of reasons.

Paul W
  • 5,507
  • 2
  • 2
  • 13