Materialized View Concepts Explored

Materialized View Concepts

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to nonmaster sites in a replication environment and to cache expensive queries in a data warehouse environment. This chapter, and this Oracle Database Advanced Replication manual in general, discusses materialized views for use in a replication environment.

What is a Materialized View?

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site, as illustrated in Figure 3-1. The arrows in Figure 3-1 represent database links.

Description of "Figure 3-1 Materialized View Connected to a Single Master Site"

When a materialized view is fast refreshed, Oracle must examine all of the changes to the master table or master materialized view since the last refresh to see if any apply to the materialized view. Therefore, if any changes where made to the master since the last refresh, then a materialized view refresh takes some time to apply the changes to the materialized view. If, however, no changes at all were made to the master since the last refresh of a materialized view, then the materialized view refresh should be very quick.

Why Use Materialized Views?

You can use materialized views to achieve one or more of the following goals:

• Ease Network Loads
• Create a Mass Deployment Environment
• Enable Data Subsetting
• Enable Disconnected Computing

Ease Network Loads

===============

If one of your goals is to reduce network loads, then you can use materialized views to distribute your corporate database to regional sites. Instead of the entire company accessing a single database server, user load is distributed across multiple database servers. Through the use of multitier materialized views, you can create materialized views based on other materialized views, which enables you to distribute user load to an even greater extent because clients can access materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view.

While multimaster replication also distributes a corporate database among multiple sites, the networking requirements for multimaster replication are greater than those for replicating with materialized views because of the transaction by transaction nature of multimaster replication. Further, the ability of multimaster replication to provide real-time or near real-time replication can result in greater network traffic, and might require a dedicated network link.

Materialized views are updated through an efficient batch process from a single master site or master materialized view site. They have lower network requirements and dependencies than multimaster replication because of the point in time nature of materialized view replication. Whereas multimaster replication requires constant communication over the network, materialized view replication requires only periodic refreshes.

In addition to not requiring a dedicated network connection, replicating data with materialized views increases data availability by providing local access to the target data. These benefits, combined with mass deployment and data subsetting (both of which also reduce network loads), greatly enhance the performance and reliability of your replicated database.

Create a Mass Deployment Environment

=============================

Deployment templates allow you to precreate a materialized view environment locally. You can then use deployment templates to quickly and easily deploy materialized view environments to support sales force automation and other mass deployment environments.

Enable Data Subsetting

=================

Materialized views allow you to replicate data based on column- and row-level subsetting, while multimaster replication requires replication of the entire table. Data subsetting enables you to replicate information that pertains only to a particular site. For example, if you have a regional sales office, then you might replicate only the data that is needed in that region, thereby cutting down on unnecessary network traffic.

Enable Disconnected Computing

======================

Materialized views do not require a dedicated network connection. Though you have the option of automating the refresh process by scheduling a job, you can manually refresh your materialized view on-demand, which is an ideal solution for sales applications running on a laptop

For example, a developer can integrate the replication management API for refresh on-demand into the sales application. When the salesperson has completed the day's orders, the salesperson simply dials up the network and uses the integrated mechanism to refresh the database, thus transferring the orders to the main office.

Read-Only, Updatable, and Writeable Materialized Views

A materialized view can be either read-only, updatable, or writeable. Users cannot perform data manipulation language (DML) statements on read-only materialized views, but they can perform DML on updatable and writeable materialized views.

Note:

• For read-only, updatable, and writeable materialized views, the defining query of the materialized view must reference all of the primary key columns in the master.
• Materialized views cannot be based on tables that contain any columns encrypted using transparent data encryption.

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 Replication Management tool. 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.world;

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 Replication Management tool. 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:

The following statement creates a materialized view group:

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
      gname => 'hr_repg',
      master => 'orc1.world',
      propagation_mode => 'ASYNCHRONOUS');
END;
/

The following statement adds the hr.departments materialized view to the materialized view group, making the materialized view updatable:

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'departments',
      type => 'SNAPSHOT',
      min_communication => TRUE);
END;

/

You can also use the Replication Management tool to create a materialized view group and add a materialized view to it.

In a single master site environment that has updatable materialized views, quiesce is not required when you perform administration operations on the master site if you:

• Propagate all of the deferred transactions at the databases containing updatable materialized views before you perform the administration operations to the master group.
• Do not allow any database manipulation language (DML) changes on the updatable materialized views until you have finished the administration operation on the master site and regenerated replication support for the materialized view.

If you do not perform these actions, then quiesce the master group before you perform the administration operations on it.

Note:

• Do not use column aliases when you are creating an updatable materialized view. Column aliases cause an error when you attempt to add the materialized view to a materialized view group using the CREATE_MVIEW_REPOBJECT procedure.
• An updatable materialized view based on a master table or master materialized view that has defined column default values does not automatically use the master's default values.
• A DELETE CASCADE constraint used with an updatable materialized view must be deferrable.

  Writable Materialized Views

A writeable materialized view is one that is created using the FOR UPDATE clause but is not part of a materialized view group. Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself. Writeable materialized views are typically allowed wherever fast-refreshable read-only materialized views are allowed.

Note:

Most of the documentation about materialized views only refers to read-only and updatable materialized views because writeable materialized views are rarely used.

	Read-only	Updatable	Writeable
created with “FOR UPDATE” clause?	N	Y	Y
perform DML operation?	N	Y	Y
placed in a materialized view group?	N	Y	N
changes be pushed back to master?	N	Y	N

 Available Materialized Views

Oracle offers several types of materialized views to meet the needs of many different replication (and nonreplication) situations. The following sections describe each type of materialized view and also describe some environments for which they are best suited.

The following sections contain examples of creating different types of materialized views:

• Primary Key Materialized Views
• Object Materialized Views
• ROWID Materialized Views
• Complex Materialized Views

Primary Key Materialized Views

Primary key materialized views are the default type of materialized view. They are updatable if the materialized view was created as part of a materialized view group and FOR UPDATE was specified when defining the materialized view. An updatable materialized view must belong to a materialized view group that has the same name as the replication group at its master site or master materialized view site. In addition, an updatable materialized view must reside in a different database than the master replication group.

Changes are propagated according to the row-level changes that have occurred, as identified by the primary key value of the row (not the ROWID). The following is an example of a SQL statement for creating an updatable, primary key materialized view:

CREATE MATERIALIZED VIEW oe.customers FOR UPDATE AS
  SELECT * FROM oe.customers@orc1.world;

Primary key materialized views can contain a subquery so that you can create a subset of rows at the remote materialized view site. A subquery is a query imbedded within the primary query, so that you have more than one SELECT statement in the CREATE MATERIALIZED VIEW statement. This subquery can be as simple as a basic WHERE clause or as complex as a multilevel WHERE EXISTS clause. Primary key materialized views that contain a selected class of subqueries can still be incrementally (or fast) refreshed, if each master referenced has a materialized view log. A fast refresh uses materialized view logs to update only the rows that have changed since the last refresh.

The following materialized view is created with a WHERE clause containing a subquery:

CREATE MATERIALIZED VIEW oe.orders REFRESH FAST AS
 SELECT * FROM oe.orders@orc1.world o
 WHERE EXISTS
   (SELECT * FROM oe.customers@orc1.world c
    WHERE o.customer_id = c.customer_id AND c.credit_limit > 10000);

This type of materialized view is called a subquery materialized view.

ROWID Materialized Views

Oracle supports ROWID materialized views in addition to the default primary key materialized views. A ROWID materialized view is based on the physical row identifiers (rowids) of the rows in a master. ROWID materialized views can be used for materialized views based on master tables that do not have a primary key, or for materialized views that do not include all primary key columns of the master tables.

The following is an example of a CREATE MATERIALIZED VIEW statement that creates a ROWID materialized view:

CREATE MATERIALIZED VIEW oe.orders REFRESH WITH ROWID AS
 SELECT * FROM oe.orders@orc1.world

Complex Materialized Views

To be fast refreshed, the defining query for a materialized view must observe certain restrictions. If you require a materialized view whose defining query is more general and cannot observe the restrictions, then the materialized view is complex and cannot be fast refreshed.

Specifically, a materialized view is considered complex when the defining query of the materialized view contains:

• A CONNECT BY clause.
  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.emp_hierarchy AS
    SELECT LPAD(' ', 4*(LEVEL-1))||email USERNAME 
      FROM hr.employees@orc1.world START WITH manager_id IS NULL 
      CONNECT BY PRIOR employee_id = manager_id;
  

• An INTERSECT, MINUS, or UNION ALL set operation.
  For example, the following statement creates a complex materialized view because it has a UNION ALL set operation:

  CREATE MATERIALIZED VIEW hr.mview_employees AS
    SELECT employees.employee_id, employees.email 
    FROM hr.employees@orc1.world
  UNION ALL
    SELECT new_employees.employee_id, new_employees.email 
    FROM hr.new_employees@orc1.world;
  

• The DISTINCT or UNIQUE keyword.
  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.employee_depts AS
    SELECT DISTINCT department_id FROM hr.employees@orc1.world 
    ORDER BY department_id; 
  

• In some cases, an aggregate function, although it is possible to have an aggregate function in the defining query and still have a simple materialized view.
  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.average_sal AS
    SELECT AVG(salary) "Average" FROM hr.employees@orc1.world;
  

• In some cases, joins other than those in a subquery, although it is possible to have joins in the defining query and still have a simple materialized view.
  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.emp_join_dep AS
    SELECT last_name 
    FROM hr.employees@orc1.world e, hr.departments@orc1.world d
    WHERE e.department_id = d.department_id;