Materialized view

A materialized view is a database object that stores the results of a query (possibly from a remote database). Materialized views are sometimes referred to as snapshots.

Example

If the materialized view will access remote database objects, we need to start by creating a database link to the remote DB:

CREATE DATABASE LINK remotedb 
CONNECT TO scott IDENTIFIED BY tiger
USING 'orcl';

Now we can create the materialized view to pull in data (in this example, across the database link):

CREATE MATERIALIZED VIEW items_summary_mv
 ON PREBUILT TABLE 
 REFRESH FORCE  AS
 SELECT  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID, 
        sum(a.GMS)       GMS, 
        sum(a.NET_REV)   NET_REV, 
        sum(a.BOLD_FEE)  BOLD_FEE,
        sum(a.BIN_PRICE) BIN_PRICE, 
        sum(a.GLRY_FEE)  GLRY_FEE, 
        sum(a.QTY_SOLD)  QTY_SOLD, 
        count(a.ITEM_ID) UNITS 
FROM  items@remotedb a
GROUP BY  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

Materialized view logs

Materialized view logs are used to track changes (insert, update and delete) to a table. Remote materialized views can use the log to speed-up data replication by only transferring changed records.

Example:

CREATE MATERIALIZED VIEW LOG ON items;

SELECT * FROM USER_MVIEW_LOGS;

Refresh groups

A refresh group is a collection of one or more materialized views that Oracle refreshes in an atomic transaction, guaranteeing that relationships among the master tables are preserved.

To create a refresh group:

exec DBMS_REFRESH.MAKE(name=>'my_grp', -
                      list=>'my_mv1,my_mv2', -
                      next_date => sysdate, -
                      interval => 'null');

Force a refresh:

exec DBMS_REFRESH.REFRESH('my_grp');

Monitoring

Here are some views that can be queried to obtain more information about materialized views:

SELECT * FROM all_refresh;
SELECT * FROM all_refresh_children;
SELECT * FROM v$mvrefresh;
SELECT * FROM all_registered_mviews;

External Links

• SQL Snippets: SQL Features Tutorials - Materialized Views