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 ?

First of all, you don't need to drop and create MV every day (at least because it will drop all the grants on this MV and all additional structures like index, constraint etc), you may use `complete` refresh. Secondly, you may use `fast` refresh method, but it is a subject of certain restrictions. See [`CREATE MATERIALIZED VIEW`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-MATERIALIZED-VIEW.html) and [`DBMS_MVIEW.EXPLAIN_MVIEW`](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_MVIEW.html) for more details. — astentx, Mar 28 '23 at 07:30
And possibly [DBMS_ADVISOR.TUNE_MVIEW](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_ADVISOR.html#GUID-5FF012AD-6EC7-4EA5-BD4C-6E2E3F908941), as long as you didn't provide an actual query. — astentx, Mar 28 '23 at 07:32

score 2 · Answer 1 · answered Mar 28 '23 at 07:10

2

You have to define the view as a fast refresh view at view definition time. For views with joins, the ROWID from each table must be in the view. In addition your materialized view logs must be created with with rowid.

See: https://docs.oracle.com/cd/B19306_01/server.102/b14223/basicmv.htm#sthref537

answered Mar 28 '23 at 07:10

Justin Swanhart

1,826
13
15

There are some materialized views for which fast refresh is not possible. Mat views having joins and group by cannot possibly be refreshed fast, but need complete refresh. – Bogdan Dincescu Mar 28 '23 at 07:38
@user13975334 Note that even if it turns out that you must do a complete refresh, that is still far preferable to dropping and recreating the MV from scratch every day, for all the reasons that astentx noted in his comment above. – pmdba Mar 28 '23 at 10:22

score 0 · Accepted Answer · answered Mar 28 '23 at 11:36

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.

Refresh materialized view incrementally in oracle

2 Answers2