Search code examples
oracleviewmaterialized

Way to improve performance of materialized view refresh

I have a materialized view that brings back 2 columns and roughly 300m rows.

The materialized view is based on joins between multiple views.

Currently it takes roughly 10 minutes for this to refresh and I am trying to improve the refresh time since there are usually very few changing records in this data set.

I currently run a "Complete" refresh - is there a way to run a fast refresh since this materialized view is based on other views and not tables?

Thank you

Solution

  • See https://docs.oracle.com/database/121/DWHSG/basicmv.htm#i1007007 for fast refresh requirements and limitations.

    Use DBMS_MVIEW.EXPLAIN_MVIEW for analyzing your materialized view query to see why the fast refresh is not possible to use.