Explain Materialized View and its Architecture

Materialized View Concepts

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. This chapter, and this Oracle9i 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.

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 may 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. Parameters allow you to create custom data sets for individual users without changing the deployment template. This technology enables you to roll out a database infrastructure to hundreds or thousands of users.
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.

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:

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

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.

Writeable 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.

Available Materialized Views

Oracle offers several types of materialized views to meet the needs of many different replication (and non-replication) 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

Whenever you create a materialized view, regardless of its type, always specify the schema name of the table owner in the query for the materialized view. For example, consider the following CREATE MATERIALIZED VIEW statement:

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

Here, the schema hr is specified in the query.
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 may 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 may 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.

Object Materialized Views

If a materialized view is based on an object table and is created using the OF type clause, then the materialized view is called an object materialized view. An object materialized view is structured in the same way as an object table. That is, an object materialized view is composed of row objects, and each row object is identified by an object identifier (OID) column.

ROWID Materialized Views

For backward compatibility, 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 should be used only for materialized views based on master tables from an Oracle7 database, and should not be used when creating new materialized views based on masters from Oracle8 or higher databases.

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;

* In some cases, the DISTINCT or UNIQUE keyword, although it is possible to have the DISTINCT or UNIQUE keyword 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.employee_depts AS
SELECT DISTINCT department_id FROM hr.employees@orc1.world
ORDER BY department_id;

* An aggregate function

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;

* Joins other than those in a subquery

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;

* In some cases, a UNION operation. Specifically, a materialized view with a UNION operation is complex if any one of these conditions is true:
o Any query within the UNION is complex. The previous bullet items specify when a query makes a materialized view complex.
o The outermost SELECT list columns do not match for the queries in the UNION. In the following example, the first query only has order_total in the outermost SELECT list while the second query has customer_id in the outermost SELECT list. Therefore, the materialized view is complex.

CREATE MATERIALIZED VIEW oe.orders AS
SELECT order_total
FROM oe.orders@orc1.world o
WHERE EXISTS
(SELECT cust_first_name, cust_last_name
FROM oe.customers@orc1.world c
WHERE o.customer_id = c.customer_id
AND c.credit_limit > 50)
UNION
SELECT customer_id
FROM oe.orders@orc1.world o
WHERE EXISTS
(SELECT cust_first_name, cust_last_name
FROM oe.customers@orc1.world c
WHERE o.customer_id = c.customer_id
AND c.account_mgr_id = 30);

The innermost SELECT list has no bearing on whether a materialized view is complex. In the previous example, the innermost SELECT list is cust_first_name and cust_last_name for both queries in the UNION.

Required Privileges for Materialized View Operations

Three distinct types of users perform operations on materialized views:

* Creator: the user who creates the materialized view
* Refresher: the user who refreshes the materialized view
* Owner: the user who owns the materialized view. The materialized view resides in this user's schema.

One user may perform all of these operations on a particular materialized view. However, in some replication environments, different users perform these operations on a particular materialized view. The privileges required to perform these operations depend on whether the same user performs them or different users perform them. The following sections explain the privileges requirements in detail.

Creator Is Owner

If the creator of a materialized view also owns the materialized view, this user must have the following privileges to create a materialized view, granted either explicitly or through a role:

* CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW
* CREATE TABLE or CREATE ANY TABLE
* CREATE VIEW or CREATE ANY VIEW if the compatibility level of the database is lower than 8.1.0
* SELECT object privilege on the master and the master's materialized view log or SELECT ANY TABLE system privilege. If the master site or master materialized view site is remote, then the SELECT object privilege must be granted to the user at the master site or master materialized view site to which the user at the materialized view site connects through a database link.

If the owner of materialized view at the materialized view site has a private database link to the master site or master materialized view site, then the database link connects to the owner of the master at the master site or master materialized view site. Otherwise, the normal rules for connections through database links apply.

Creator Is Not Owner

If the creator of a materialized view is not the owner, certain privileges must be granted to the creator and to the owner to create a materialized view. The creator's privileges can be granted explicitly or through a role, but the owner's privileges must be granted explicitly. That is, the privileges granted to the owner cannot be granted through a role.

Data Subsetting with Materialized Views

In certain situations, you may want your materialized view to reflect a subset of the data in the master table or master materialized view. Row subsetting enables you to include only the rows that are needed from the master in the materialized views by using a WHERE clause. Column subsetting enables you to include only the columns that are needed from the master in the materialized views. You do this by specifying certain select columns in the SELECT statement during materialized view creation. If you use deployment templates to build your materialized views, then you can define column subsets on updatable materialized views.

Some reasons to use data subsetting are to:

* Reduce Network Traffic: In a column-subsetted materialized view, only changes that satisfy the WHERE clause of the materialized view's defining query are propagated to the materialized view site, thereby reducing the amount of data transferred and reducing network traffic.
* Secure Sensitive Data: Users can only view data that satisfies the defining query for the materialized view.
* Reduce Resource Requirements: If the materialized view is located on a laptop, then hard disks are generally significantly smaller than the hard disks on a corporate server. Subsetted materialized views may require significantly less storage space.
* Improve Refresh Times: Because less data is propagated to the materialized view site, the refresh process is faster, which is essential for those who need to refresh materialized views using a dial up network connection from a laptop.

For example, the following statement creates a materialized view based on the oe.orders@orc1.world master table and includes only the rows for the sales representative with a sales_rep_id number of 173:

CREATE MATERIALIZED VIEW oe.orders REFRESH FAST AS
SELECT * FROM oe.orders@orc1.world
WHERE sales_rep_id = 173;

Rows of the orders table with a sales_rep_id number other than 173 are excluded from this materialized view.