8 Replies Latest reply: Aug 24, 2011 5:41 AM by Dom Brooks RSS

    refresh materialized view but skipping delete

    842339
      I have a master table named DATA with mview log created on it.

      DATA_MVIEW is created as select * from DATA.

      For a particular reason, I want to refresh DATA_MVIEW hourly but for only UPDATE/INSERT, skip DELETE action.

      One way of achieving that is to write additional code:
      1. detect DELETE records by looking into MLOG$_DATA (mview log) whose DMLTYPE$$ value is D.
      2. Pull these DELETE records to a separate temp table.
      3. refresh DATA_MVIEW
      4. insert DELETE records from temp table into DATA_MVIEW.

      My question is "is there a better, more straightforward way to skip DELETE while refreshing MVIEW"?

      Thanks so much & reward points awaiting useful advice!