Oracle Materialized Views FAST refresh ON COMMIT

• Home
• Solutions
• Library
• About Us
• Community

Materialized views can be used as a performance enhancement, because Oracle treats materialized views as normal table. The typical concerns with materialize views are related to the operation overhead with keeping the data up to date.

The most efficient mechanism for keeping a materialized view up to date is by using a FAST refresh with the ON COMMIT option. A FAST refresh allows the materialized views to be updated incremental (using a FULL refresh). Oracle will only updates values of materialized view that value been changed in the underlying tables for a FAST refresh.

The materialized view can also be created using the ON COMMIT Clause. This indicates that a fast refresh is needed whenever the database commits a transaction that operates on a master table of the materialized view. The database performs the refresh operation as part of the commit process. Note this clause may increase the time needed to commit data changes.

CREATE MATERIALIZED VIEW sales_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT t.calendar_year, p.prod_id, 
      SUM(s.amount_sold) AS sum_sales
      FROM times t, products p, sales s
      WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
      GROUP BY t.calendar_year, p.prod_id;