Refresh materialized view incrementally in oracle

Question

There is script which basically drops the materialized view created on the previous day and recreates the materialized view again. Materialized view is created by joining some fact with dimension.

I was thinking instead of dropping materialized view we can go for incrementally refresh this view.

To refresh this view manually, we can use

DBMS_MVIEW.Refresh('TEST_MV'); 

How to refresh the materialized view incrementally ?

Answer 1

Fast refresh is of course the most efficient option, but not always possible due to limitations and also often the owner of the source table doesn't want a trigger on their table (which can cause limitations to how that table is maintained). It also has the downside of just plain adding more complexity (triggers, log tables, etc.).

I find that in most cases a MERGE statement works best because it can surgically add and/or modify only rows that need changing.

-- new and modified rows:

MERGE INTO target tgt
USING (SELECT n.*,
              o.rowid row_id
         FROM (SELECT [mv SQL]) n,
              target o
        WHERE n.pkcol = o.pkcol(+)
          AND (o.pkcol IS NULL OR -- new rows
               n.attr1 != o.attr1 OR  -- changed rows
               n.attr2 != o.attr2)) src
ON (src.rowid = tgt.ROWID)
WHEN MATCHED THEN UPDATE SET tgt.attr1 = src.attr1,
                             tgt.attr2 = src.attr2
WHEN NOT MATCHED THEN INSERT (pkcol,
                              attr1,
                              attr2)
                      VALUES (src.pkcol,
                              src.attr1,
                              src.attr2);

-- deleted rows:

DELETE FROM target 
 WHERE pkcol NOT IN (SELECT pkcol FROM (SELECT [mv SQL]));

This can of course be parallelized with PDML.