How to Refresh Materialized Views with DBMS_MVIEW

A materialized view stores a query's result at the time of its last refresh. When the base tables change, it becomes stale until it is refreshed. For views refreshed on demand, the DBMS_MVIEW.REFRESH procedure does it: one view or several, completely or fast, and in a consistent set.

Code for This Guide

The main examples are in the examples/views folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_mview.refresh(list => 'mview1[,mview2 ...]',
                   method => 'C' | 'F' | '?' | 'A' [...])

For a list of views, the method string has one letter per view.

Detect and Refresh a Stale View

The example refreshes ROUTE_REVENUE_MV, created first:

Example:

create materialized view route_revenue_mv
  build immediate
  refresh complete on demand
  enable query rewrite
as
select f.route_id, t.cabin, count(*) as tickets, sum(t.fare) as revenue
from   tickets t join flights f on f.flight_id = t.flight_id
group  by f.route_id, t.cabin;

select mview_name, refresh_mode, refresh_method, staleness, rewrite_enabled
from   user_mviews;

Output:

Materialized view ROUTE_REVENUE_MV created.

MVIEW_NAME          REFRESH_MODE    REFRESH_METHOD    STALENESS    REWRITE_ENABLED
___________________ _______________ _________________ ____________ __________________
ROUTE_REVENUE_MV    DEMAND          COMPLETE          FRESH        Y

A fare changes, the view becomes stale, and DBMS_MVIEW.REFRESH brings it up to date.

Example:

update tickets set fare = fare + 1 where ticket_id = 1;
commit;

select mview_name, staleness from user_mviews where mview_name = 'ROUTE_REVENUE_MV';

exec dbms_mview.refresh('ROUTE_REVENUE_MV', method => 'C')

select mview_name, staleness, to_char(last_refresh_date, 'HH24:MI') is not null as refreshed
from   user_mviews where mview_name = 'ROUTE_REVENUE_MV';

Output:

1 row updated.

Commit complete.

MVIEW_NAME          STALENESS
___________________ ________________
ROUTE_REVENUE_MV    NEEDS_COMPILE

PL/SQL procedure successfully completed.

MVIEW_NAME          STALENESS    REFRESHED
___________________ ____________ ____________
ROUTE_REVENUE_MV    FRESH        true

USER_MVIEWS.STALENESS shows NEEDS_COMPILE after the change, and FRESH after the refresh. The example restores the fare afterward.

Refresh Several Views Together

Listing several views in one call refreshes them as a set, here two work views with complete refreshes ('CC').

Example:

-- refresh several materialized views in one call, as one consistent set
exec dbms_mview.refresh('CABIN_COUNTS_MV,ROUTE_COUNTS_MV', method => 'CC')

select mview_name, last_refresh_type, staleness
from   user_mviews
where  mview_name in ('CABIN_COUNTS_MV', 'ROUTE_COUNTS_MV')
order  by mview_name;

Output:

PL/SQL procedure successfully completed.

MVIEW_NAME         LAST_REFRESH_TYPE    STALENESS
__________________ ____________________ ____________
CABIN_COUNTS_MV    COMPLETE             FRESH
ROUTE_COUNTS_MV    COMPLETE             FRESH

LAST_REFRESH_TYPE confirms the complete refreshes. The views were created and dropped by the example.

Things to Know

• Schedule refreshes with DBMS_SCHEDULER, for example nightly for reporting views.
• DBMS_MVIEW.REFRESH_ALL_MVIEWS and REFRESH_DEPENDENT refresh all views, or all views that depend on given tables.
• A complete refresh rebuilds the whole result; for large views with few changes, set up materialized view logs and fast refresh.

Related Guides

• How to Create Materialized Views in Oracle

Conclusion

DBMS_MVIEW.REFRESH brings on-demand materialized views up to date, completely, fast, or whichever is possible, one at a time or as a set. Check USER_MVIEWS.STALENESS to see which views need it.