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.
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.
CREATE MATERIALIZED VIEW LOG ON items;
SELECT * FROM USER_MVIEW_LOGS;
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:
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;