Can I have a materialized view refresh on commit only for two of three tables in its select statement?

Question

I would like to use a materialized view that refreshes ON COMMIT. My backing view joins two tables in the local database and one table in a remote database via DB Link. How can I have the view refresh only when changes are made to one of the two local tables?

Are there any other ways to solve this problem? Can I have the materialized view just join the two local tables and put NULLS for the columns from the remote database, and then have a trigger on insert/update to the materialized view that would fill in those fields? Or do updates to a materialized view propagate back to the source tables?

I'm doing something like this:

SELECT LOC1.ID, LOC1.NAME, LOC2.PRICING_TYPE, REM1.PURCHASING_ID
FROM LOCAL_TABLE_A LOC1, LOCAL_TABLE_B LOC2, [email protected] REM1
WHERE LOC1.ID = LOC2.MASTER_ID
AND LOC1.REM_ID = REM1.ID
AND LOC2.YEAR = REM1.YEAR

The REMOTE_TABLE is only a lookup table for information related to the two local tables. It should not drive anything here, and I only want the materialized view to update if LOCAL_TABLE_A OR LOCAL_TABLE_B CHANGE.

Answer 1

Hi,

You could use an intermediate Materialized View for the remote table. This MV would be created in your local DB with REFRESH ON DEMAND so that you can refresh it manually. Your MV would use the local table in place of the remote table.