How to Set Up Fast Refresh on Commit for Materialized Views

A complete refresh recomputes the whole result, which is slow for large views and leaves them stale between refreshes. Fast refresh applies only the changes since the last refresh, read from a materialized view log on each base table. Combined with ON COMMIT, the view is updated by every transaction that changes its base tables, so it is never stale.

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

create materialized view log on table
  [with {rowid | primary key | sequence} [, ...] (columns)]
  [including new values]

create materialized view mview refresh fast on commit as subquery

The log records the changes to a base table. For aggregate views, the log should include ROWID, SEQUENCE, the columns used, and INCLUDING NEW VALUES, and the view should contain COUNT(*) and a COUNT of each column that it sums, as the example does.

A Self-Maintaining Summary

Example:

create materialized view log on tickets
  with rowid, sequence (cabin, fare) including new values;

create materialized view cabin_totals_mv
  refresh fast on commit
as
select cabin, count(*) as tickets, count(fare) as fares, sum(fare) as revenue
from   tickets
group  by cabin;

select cabin, revenue from cabin_totals_mv order by cabin;

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

select cabin, revenue from cabin_totals_mv order by cabin;

Output:

Materialized view log TICKETS created.

Materialized view CABIN_TOTALS_MV created.

CABIN            REVENUE
___________ ____________
BUSINESS       618345.79
ECONOMY        512432.86

1 row updated.

Commit complete.

CABIN            REVENUE
___________ ____________
BUSINESS       618345.79
ECONOMY        512532.86

The view's economy revenue rises by 100 as soon as the update is committed, without any refresh call. The example restores the fare and drops the view and the log afterward.

Rules for Fast Refresh

• Each base table needs a materialized view log with the columns the view uses.
• Include COUNT(*), and COUNT(column) for each SUM(column) or AVG(column), so Oracle can maintain the totals incrementally for every kind of change, deletes included.
• DBMS_MVIEW.EXPLAIN_MVIEW reports which refresh methods a query supports and why others are not possible.

Things to Know

• ON COMMIT makes every commit on the base tables do extra work; use it for small, frequently read summaries.
• For larger views, use REFRESH FAST ON DEMAND and schedule refreshes.
• Drop the log with DROP MATERIALIZED VIEW LOG ON table when no view needs it.

Related Guides

• How to Refresh Materialized Views with DBMS_MVIEW
• How to Create Materialized Views in Oracle

Conclusion

Fast refresh applies only recorded changes to a materialized view, using materialized view logs on its base tables, and ON COMMIT applies them with every transaction. Include COUNT columns in aggregate views, and reserve ON COMMIT for small summaries that must always be current.