MATERIALIZED VIEWS

A materialized view provides access to table data by storing results of a query in a separate schema object.

Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental, (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log keeps a record of changes to the master tables.

Materialized views can be refreshed either on demand or at regular time intervals. Normal replication occurs at the row level.

Setup involves

• Configuring certain DB parameters
• Configuring the replicated objects

Parameters that need to be something other than the default:

• Job_queue_processes – at least 1
• Open_links – one additional link for each DB that will be replicated to.
• Shared_pool_size – add 20 Mg for basic replication.

SQL> CREATE MATERIALIZED VIEW my_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

REFRESH:

[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key | rowed}]]

The refresh option specifies:

• The refresh method used by Oracle to refresh data in a materialized view.
• Whether the view is primary key based or row id based.
• The time and interval at which the view is to be refreshed.

Refresh Method:

• FAST
• You should create a materialized view log for the master tables if you specify REFRESH FAST.
• Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
• COMPLETE – recreates the entire materialized view.
• FORCE – Performs a fast refresh if one is possible, or a complete refresh otherwise. FORCE is the default.

PRIMARY KEY / ROWID Clause

• WITH PRIMARY KEY – Materialized view is based on the primary key of the master table. This is the default.
• Primary Key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
• ROWID materialized views should have a single master table and cannot contain:
• Distinct or aggregate functions
• Group by subqueries
• Joins & Set operations

OTHER:

SQL> execute DBMS_REFRESH.DESTROY(name=> ‘my_group1’);

Removes all materialized views from the refresh group and deletes the refresh group.

SQL> select job, what, failures from user_jobs;

To see if any refreshes are failing.

References:

Materialized Views in Oracle By Ajay Gursahani (most of the material in this blog item is taken directly from this article)

Oracle Replication Expert Methods for Robust Data Sharing. By Karam, Garmany, Burleson. Rampant Press