0

I've created materialized view logs on a WORKORDER table in an Oracle 19c database.

create materialized view log on my_workorder_system.workorder with rowid;  

The plan is to integrate the WORKORDER records to a separate GIS system (Oracle 18c) via a materialized view.

The catch:

The WORKORDER table has both spatial and non-spatial workorder records in it (ISGIS = 0 or 1). I only need to sync the records to the GIS datdabase WHERE ISGIS = 1.

If I understand correctly, my materialized view in GIS will tell the MV logs in the workorder system to purge the log records after they're synced. However, the sync will only ever happen for workorders where ISGIS=1. The workorders where ISGIS=0 will never get synced — and therefore are not needed in the MV logs.

Is there a way to only generate MV logs for records that meet a condition?

For example:

create materialized view log on my_workorder_system.workorder with rowid where ISGISS=1.
3
  • 1
    MV logs are all or nothing; they have no visibility of criteria for any given MV. Any MV Log records will be purged up to the oldest common refresh date as soon as the source DB determines that all registered MVs have refreshed.
    – pmdba
    Nov 30 '20 at 19:36
  • @pmdba Since MV logs can't have conditions, is there an alternative Oracle mechanism that can be used for syncing to a remote table — that does allow for conditions?
    – User1973
    Dec 4 '20 at 2:58
  • 1
    Not with the same level of support or minimal complexity; you'd have to reinvent the wheel. My advice: don't over think it. MV Logs have very, very little overhead. As long as you keep the relative number of transactions in each refresh low, you shouldn't have any problems. In general it is better to have more frequent refreshes with fewer transactions each than to have less frequent refreshes with more transactions each. It keeps the workload on both databases low and predictable, vs. more intense and erratic (DBAs like low and predictable).
    – pmdba
    Dec 4 '20 at 3:47
0

MV logs are all or nothing; they have no visibility of criteria for any given MV. Any MV Log records will be purged up to the oldest common refresh date as soon as the source DB determines that all registered MVs have refreshed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.