Using Materialized Views

The use of materialized views, or snapshots as they were previously known, is the simplist way to achive replication of data between sites. The materialized view is a table whose contents are periodically refreshed using a query against a remote table

Query Rewriting
The QUERY_REWRITE_ENABLED initialization parameter determines whether Oracle will rewrite a queryor not. The default value for this parameter is FALSE,

Refresh Mode
You can choose between the ON COMMIT and ON DEMAND modes of data refresh.

• ON COMMIT: In this mode, whenever a data change in one of the master tables is committed,the materialized view is refreshed automatically to reflect the change.
• ON DEMAND: In this mode, you must execute a procedure like DBMS_MVIEW.REFRESH to updatethe materialized view.The default refresh mode is ON DEMAND.

Refresh Type

You can choose from the following four refresh types:

• COMPLETE: This refresh option will completely recalculate the query underlying the materializedview. Thus, if the materialized view originally took you 12 hours to build, it’ll take aboutthe same time to rebuild it. Obviously, you wouldn’t want to use this option each time a fewrows are modified, dropped, or inserted into your master tables.
• FAST: Under the fast refresh mechanism, Oracle will use a materialized view log to log allchanges to the master tables. It’ll then use the materialized view log to update the mastertables, thus avoiding a complete refresh of the view. You can use other techniques toperforma fast refresh, but the materialized view log is the most frequently used devicefor this purpose.

Creating Materialized Views

SQL> GRANT CREATE DATABASE LINK TO scott;
SQL> GRANT CREATE MATERIALIZED VIEW TO scott;
SQL> GRANT QUERY REWRITE TO scott;

Creating the Materialized View Log

Let’s use the FAST refresh mechanism for our materialized view. This will require the creation of twomaterialized logs, of course, to capture the changes to the two master tables that are going to be thebasis for our materialized view. Here’s how you create the materialized view logs:Here’s how you create the materialized view log:

SQL> CREATE MATERIALIZED VIEW LOG ON products;
SQL> CREATE MATERIALIZED VIEW LOG ON sales;

SQL> CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE REFRESH FORCE
ON DEMAND AS SELECT * FROM emp@tsh1.world;