Partial refresh on Materialized View

Question

We have a table TB_1 which has monthly and weekly data partitioned by monthly and weekly columns. We also have materialized view MV_1 inherited from the table TB_1.

We would like to refresh the materialized view by certain weekly or monthly basis.

Not sure how we can filter out weekly or monthly changes from all the changes captured in the materialized log for partial refresh.

Right now we are thinking to have a flag column in TB_1. By clearing the materialized log and updating the flag, We think we can achieve this.

Is there anyway efficient way than the process for partial refresh on specific criteria?

Answer 1

We have a similar case here: the solution we found was to partition the materialized view month by month (using PCT ). If you have this as an option in your licensing, this could be a solution. Then you must partition the "details tables", TB_1, and probably the "details tables" of MV_1.

execute dbms_mview.refresh( 
   list => 'your_partitioned_mview'
 , method => 'P'                -- this is where PCT is specified
 , atomic_refresh => false 
);

There are also other solution on Is it possible to partially refresh a materialized view in Oracle? .

edit:

I'd say the solution of a fast refresh using a "to be refreshed" flag is worth being tried. Not sure you need to clear the Mview log beforehand. Just changing the value of the flag for the record to be updated should work. Here is a nice howto I found.

If you have Oracle 12.2, they introduced real-time Mviews, which might be what you are looking for...

Oracle 12.2 introduced the concept of real-time materialized views, which allow a statement-level wind-forward of a stale materialised view, making the data appear fresh to the statement. This wind-forward is based on changes computed using materialized view logs, similar to a conventional fast refresh, but the operation only affect the current statement. The changes are not persisted in the materialized view, so a conventional refresh is still required at some point.

@use416, please keep us posted for what actually worked for your case.