Oracle Materialized View Types

Materialized View Types in Oracle

Oracle offers several types of materialized views to meet the needs of many different replication (and non-replication) situations.

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

Primary Key Materialized Views

Primary key materialized views are the default type of materialized views in Oracle. 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.

The following statement creates the primary key materialized view on the table emp located on a remote database.

SQL> CREATE MATERIALIZED VIEW mv_emp_pk

BUILD DEFFERED

REFRESH FAST

START WITH SYSDATE NEXT SYSDATE + 1/48

WITH PRIMARY KEY

AS SELECT * FROM emp@remote_db;

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:

SQL> CREATE MATERIALIZED VIEW offshore.customers

FOR UPDATE

AS SELECT * FROM onsite.customers@orcl;

Primary key M-views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Subquery Materialized Views

Materialized views may contain a subquery so that we can create a subset of rows at the remote materialized view site. A subquery is a query imbedded within the primary query, so that we 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 statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW mv_empdept

DISABLE QUERY REWRITE

AS SELECT * FROM emp@remote_db e

WHERE EXISTS

(SELECT * FROM dept@remote_db d WHERE e.dept_no = d.dept_no);

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 from Oracle8 or higher.

The following statement creates the rowid materialized view on table emp located on a remote database:

SQL> CREATE MATERIALIZED VIEW mv_emp_rowid

REFRESH WITH ROWID

ENABLE QUERY REWRITE

AS SELECT * FROM emp@remote_db;

ROWID materialized views should have a single master table and cannot contain any of the following:

• Distinct or aggregate functions
• GROUP BY subqueries, joins & set operations

Object Materialized Views

Object materialized view is based on an object table and is created using the OF type clause. 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.

SQL> CREATE MATERIALIZED VIEW customer OF cust_objtype

AS SELECT CustNo FROM scott.Customer@orcl;

SQL> CREATE MATERIALIZED VIEW LOG ON categories_tab WITH OBJECT ID;

An object materialized view inherits the object identifier (OID) specifications of its master. If the master has a primary key-based OID, then the OIDs of row objects in the materialized view are primary key-based. If the master has a system generated OID, then the OIDs of row objects in the materialized view are system generated. Also, the OID of each row in the object materialized view matches the OID of the same row in the master, and the OIDs are preserved during refresh of the materialized view. Consequently, REFs to the rows in the object table remain valid at the materialized view site.

Complex Materialized Views

To be fast refreshed, the defining query for a materialized view must observe certain restrictions. If we 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.

A materialized view is considered complex when the defining query of the materialized view contains:

i) A CONNECT BY clause
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.emp_hierarchy
AS SELECT LPAD(' ', 4*(LEVEL-1))||email USERNAME
FROM hr.employees@orcl START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

ii) 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:
SQL> CREATE MATERIALIZED VIEW hr.mview_employees AS
SELECT employees.employee_id, employees.email
FROM hr.employees@orcl
UNION ALL
SELECT new_employees.employee_id, new_employees.email
FROM hr.new_employees@orcl;

iii) 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:
SQL> CREATE MATERIALIZED VIEW hr.employee_depts
AS SELECT DISTINCT department_id FROM hr.employees@orcl
ORDER BY department_id;

iv) An aggregate function

For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.average_sal
AS SELECT AVG(salary) "Average" FROM hr.employees@orcl;

v) Joins other than those in a subquery

For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.emp_join_dep AS
SELECT last_name
FROM hr.employees@orc1.world e, hr.departments@orcl d
WHERE e.department_id = d.department_id;

vi) 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.

SQL> CREATE MATERIALIZED VIEW oe.orders AS

SELECT order_total FROM oe.orders@orcl o

WHERE EXISTS (SELECT cust_first_name, cust_last_name

FROM oe.customers@orcl c

WHERE o.customer_id = c.customer_id AND c.credit_limit > 50)

UNION

SELECT customer_id FROM oe.orders@orcl o

WHERE EXISTS (SELECT cust_first_name, cust_last_name

FROM oe.customers@orcl c

WHERE o.customer_id = c.customer_id AND c.account_mgr_id = 30);

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

Note: If possible, we should avoid using complex materialized views because they cannot be fast refreshed, which may degrade network performance.

Related Oracle Articles: Materialized Views (MViews)    Materialized View Logs  Materialized Views Refresh Groups