Orace Materialized Views

Materialized views facilitate you to execute a SQl query and save its results either locally or in a remote database.

After the Materialized view is initially populated it can be refreshed later on to store fresh results into the underlying table.

Materialized Views are mainly used for two reasons:

1) Replication of data to separate remote databases.

2) For improving the performance of queries by computing and storing the results of complex aggregations of data.

In situations where complex sql queries are performed mainly in reporting or datawarehouse environments Materialized Views are really helpful in improving performance.

Whenever a SQL query is executed oracle database has to lot of work in order to retrieve the data. For example it may have to do sorting (Memory or Disk Based), it has to decide the execution plan for the sql statement (Do a full tables scan or a indexed based scan) and lots of other stuff before retrieving the requested data.

These type of queries if performed repeatedly will affect the performance of the server in a negative way.

But with Materialized Views the performance can be improved significantly, because when a materialized view is created it stores all the data along with the execution plans. So even if the query is executed repeatedly it will not eat up all the resources as it did earlier.

The Materialized view can be created on the base of tables, views or other materialized views.

When a Materialized View is created, oracle also create a table with the same name as that of the materialized view and also creates a materialized view object.

Types of Materialized Views

There are following types of Materialized views.

• Read-Only Materialized Views.
• Updatable Materialized Views.

Read-Only Materialized Views

You can make a materialized view read-only during creation by omitting the FOR UPDATE clause or disabling the equivalent option in the Advanced Replication interface in Oracle Enterprise Manager. Read-only materialized views use many of the same mechanisms as updatable materialized views, except that they do not need to belong to a materialized view group.

In addition, using read-only materialized views eliminates the possibility of a materialized view introducing data conflicts at the master site or master materialized view site, although this convenience means that updates cannot be made at the remote materialized view site. The following is an example of a read-only materialized view:

CREATE MATERIALIZED VIEW hr.employees AS
  SELECT * FROM hr.employees@orc1.example.com;

Updatable Materialized Views

You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the Advanced Replication interface in Oracle Enterprise Manager. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.

Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site. The following is an example of an updatable materialized view:

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
  SELECT * FROM hr.departments@orc1.example.com;

Materialized Views Refresh Types

Refresh is the operation that synchronizes the content of the materialized view with the data in base tables. Following are the types of refresh:

1. Complete - Refreshable Materialzed Views.
2. Fast-refresh Materialized Views.

COMPLETE REFERSH MATERIALIZED VIEWS

Complete: Involves truncating existing data & re-inserting all the data based on the detail tables by re-executing the query definition from the create command at periodic intervals.

CREATE MATERIALIZED VIEW hr.employees
  REFRESH COMPLETE
  NEXT SYSDATE + 1/1440 
  AS SELECT * FROM hr.employees@orc1.example.com;

So above we have created a materialized view based on the sales table, which will completely refresh itself after every one minute.

FAST REFRESH MATERIALIZED VIEW

Fast refreshable materialized views work a little bit differently. When a fast refresh materialized view is created it initially populates the materialized view table with data from the base or master table.

After the initial data is populated only modified data is applied to the materialized view table after each refresh, Instead of a complete refresh like that in Complete refresh materialized views.

Fast refresh using materialized view logs.

Fast refresh using ROWID range.

Materialized View Logs

Materialized view logs are required if you want to use fast refresh. Materialized view logs are defined using a CREATE MATERIALIZED VIEW LOG statement on the base table that is to be changed.

Now lets create a materialized view log on the master table.

CREATE MATERIALIZED VIEW LOG ON hr.employees@orc1.example.com WITH 
PRIMARY KEY;

If your base table does not have a primary key then a following error will occur.

ORA-12014: table does not contain primary key constraint

In that case make materialized view log based on ROWID.

CREATE MATERIALIZED VIEW LOG ON hr.employees@orc1.example.com WITH 
ROWID;

We are creating a materialized view based on primary key refresh.

 CREATE MATERIALIZED VIEW hr.employees
  REFRESH WITH PRIMARY KEY
  NEXT SYSDATE + 3/1440 
  AS SELECT * FROM hr.employees@orc1.example.com;

Materialized Views Refresh Modes

Manual Refresh: Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)

Automatic Refresh: Can be performed in two ways:

ON COMMIT – Materialized view gets updated whenever changes to one of these tables are committed.

ON DEMMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.

Dropping Materialized Views

DROP MATERIALIZED VIEW hr.employees;