A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp
. So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time.
Do you need a materialized view? Could you maintain your own staging table and create a custom job that runs every few minutes, deletes the data that is now more than 24 hours old, and inserts the new data (either by directly querying the table or creating a trigger that writes the new data to a different table)? That's likely a non-starter if you need to use the query rewrite functionality of a materialized view but if you just want the benefit of having a smaller table to query, custom code may be more efficient.
Or could you partition the table by day so that queries for the past 24 hours always just have to hit the two most recent partitions?
Materialized views over a DB link most certainly can be set up as fast refreshes (i.e. only changes since the last refresh) as we use this approach ourselves. However, what cannot be done are refresh on commit mviews over a DB link, so the mview will have to be on a refresh schedule (unless you simply want to refresh on demand, of course).
This works on Oracle Database Versions >= 10g (personally tested). I can't vouch for < 10g versions since I started using Oracle Database in 10g.
We see very little database impact on both of our source DB's and destination DB's using this approach with roughly 30 mviews (most are medium size tables, a few million rows each) that refresh every 15 minutes.
A few notes/gotchas: The mview log has to be created on the source DB, not the destination DB (which is usually a point of confusion with this setup).
Create the DB link on the destination DB that connects to the source DB. If the usercode that the DB link is using to connect to the source DB is different than the schema where the table/mview log table resides, then the DB link usercode on the source DB will have to be granted select privileges on both the source table and the source mview log table.
In addition, the mview log deletions don't move the HWM, so for large transactional tables, you may want to consider having a nightly process that locks the mview log table, then checks if the mview log table is empty, then truncate the mview log table in order to reset the HWM to keep fast refreshes as fast as possible.
Best Answer
Set atomic_refresh to false. It will truncate your MV table instead of deleting records.