The Geek Diary

Basics of Materialized Views in Oracle

by

Materialized views are query results that have been stored or “materialized” in advance as schema objects. The FROM clause of the query can name tables, views, and materialized views. Collectively these objects are called primary tables (a replication term) or detail tables (a data warehousing term).

Materialized views are used to summarize, compute, replicate, and distribute data. In a replication environment, a materialized view shares data with a table in a different database called a primary database. The table associated with the materialized view at the primary site is the primary table.

Characteristics of Materialized Views

Materialized views share some characteristics of non-materialized views and indexes. Materialized views are similar to indexes in the following ways:

  • They contain actual data and consume storage space.
  • They can be refreshed when the data in their primary tables changes.
  • They can improve performance of SQL execution when used for query rewrite operations.
  • Their existence is transparent to SQL applications and users.

Components of the materialized view configuration

A typical create MVIEW statement has the following form:

create materialized view snap_test
refresh fast
start with sysdate
next sysdate+1 as
select * from master_table@master_db;

This statement should be executed in snap_db. It will create:

  • A MVIEW base table called SNAP_TEST which has the same structure as MASTER_TABLE.
  • A new object namely SNAP_TEST of type materialized view
  • A UNIQUE index on the PK columns or ROWID depending on the type of MVIEW log at master site

Since this is a fast refresh MVIEW the master table should have a log to record the changes on it that can be created by running:

create materialized view log on master_table;

In master_db. This will create the following objects:

  • A table called MLOG$_master_table
  • An internal trigger on MASTER_TABLE that populates the log table

Refreshing Materialized Views

Initially, a materialized view contains the same data as in the master table. After the materialized view is created, changes can be made to the master table, and possibly also to the materialized view. To keep a materialized view’s data relatively current with the data in the master table, the materialized view must be periodically refreshed. Refresh can be accomplished by one of the following procedure:

dbms_mview.refresh( '[mview list]', '[Refresh Type]' );
dbms_refresh.refresh( '[refresh group]' );

You can choose between Complete, Fast, and Force refresh types. Complete refresh is performed by deleting (or truncating) the rows from the snapshot and inserting the rows satisfying the mview query. Depending on the size of the materialized view this can be a costly operation. This cost is addressed with fast refresh where only rows updated since the last refresh are pulled from the master site. This requires a log table called a materialized view log to be created on the master table. Force refresh first tries to run a fast refresh if possible. If fast refresh is not possible it accomplishes a complete refresh. See Note.236233.1 for a complete discussion of the refresh mechanism.

Refresh Groups

Sometimes it is required to refresh multiple snapshots in a transactionally consistent manner. Refresh groups server this purpose. When a refresh group is refreshed all mviews in that group are populated with data from a consistent point in time.

A refresh groups are managed using the procedures in the package DBMS_REFRESH. MAKE and ADD API in that package are used to create a refresh group and add new snapshots to an existing refgroup respectively.

Materialized View Types

In this section a classification of materialized views is provided. Note that any of the refresh options described in the previous section can be used by each snapshot in any of these categories.

Read-Only Materialized Views

As the name implies it is not possible to perform DML on snapshots in this category. Applications can query data from read-only materialized views to avoid network access to the master site, regardless of network availability. However, master table should be accessed for DMLs. These changes are transfered to the snapshot with the next refresh.

Updatable Materialized Views

Updatable materialized views eliminate the restriction of DMLs on snapshots. Users are allowed to insert, update and delete rows of the updatable materialized view. These changes are propagated to the master through the PUSH mechanism of advanced replication. Changes committed on the master table are pulled using the REFRESH mechanism. Permitting simultaneous DMLs on both master and materialized view brings the possibility of conflicts.

Updatable Materialized View Restrictions:

  • They are always based on a single table, although multiple tables can be referenced in a subquery.
  • They should allow fast refreshing.
  • Master table should be registered to a replication group
  • Must belong to a materialized view group that has the same name as the replication group at its master site
  • Must reside in a different database than the master replication group
  • The name of the materialized view should be the same as its master table

For a snapshot to be updatable its associated create statement should include “FOR UPDATE” clause. For Example:

create materialized view snap_test
refresh fast
start with sysdate
next sysdate+1
FOR UPDATE as
select * from master_table@master_db;

For the details of setup and configuration of an updatable mview environment refer to the Advanced Replication manual.

Subquery Materialized Views

Materialized views that are created with subqueries in the WHERE clause of the mview query are referred to as subquery materialized views. Fast refresh restrictions on these kind of mviews depend on the version, where the number of restrictions decreases in newer versions.

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);

Rowid vs. Primary Key Materialized Views

Fast refresh requires association between rows at snapshot and master sites. In Oracle7 ROWIDs were employed to do this mapping. Given the maintenance problems (e.g. ROWIDs change when a table is rebuild with EXP/IMP) fast refresh has been enhanced in Oracle8 to use primary keys. Snapshots that use ROWIDs to refresh are called ROWID snapshots while those that use primary keys are called primary key snapshots.

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;

Starting with Oracle8 primary key snapshots are the default. You should use rowid mviews:

  • If there are Oracle7 databases involved or
  • If the master table has no primary key and there is a need for fast refresh

Multitier Materialized Views

A multitier materialized view is a materialized view whose master table is itself a materialized view. This feature enables fast refresh of materialized views that have materialized views as their masters. This also makes it possible to define hierarchies of updatable mviews.

Many companies are structured on at least three levels: international, national, and local. Many nodes at both the national and local levels are required. The best possible solution in such cases is to use multitier materialized views.

In this example:

  • the site at the top of the hierarchy is assumed to be oracle.world
  • regions are considered to be at the next level, emea.oracle.world
  • countries are located at the third level, uk.emea.oracle.world

Create a materialized view of employees at EMEA region:

create materialized view employees:
refresh fast as
select * from employees@oracle.world where region='EMEA';

Create a materialized view log on the materialized view so that lower level mviews refresh fast:

create materialized view log on employees;

Create a materialized view of employees at UK:

create materialized view employees
refresh fast as
select * from employees@emea.oracle.world where country='UK';

Simple vs. Complex Materialized Views

Snapshot’s being simple or complex determines whether it can be fast refreshed. Specifically, a snapshot is fast refreshable if it is simple (not complex). A snapshot is considered complex if its defining query does not meet certain criteria, e.g. it should not contain a CONNECT BY, INTERSECT, MINUS or UNION ALL clause. Restrictions that apply vary with version. See Note:179466.1 for a detailed description of complex materialized views.

Refreshing Materialized Views

When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. In the case of ON COMMIT, the materialized view is changed every time a transaction commits, thus ensuring that the materialized view always contains the latest data. Alternatively, you can control the time when refresh of the materialized views occurs by specifying ON DEMAND. In this case, the materialized view can only be refreshed by calling one of the procedures in the DBMS_MVIEW package.

DBMS_MVIEW provides three different types of refresh operations:

  • DBMS_MVIEW.REFRESH – Refresh one or more materialized views.
  • DBMS_MVIEW.REFRESH_ALL_MVIEWS – Refresh all materialized views.
  • DBMS_MVIEW.REFRESH_DEPENDENT – Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.

Some sites might prefer not to refresh all of their materialized views at the same time. As soon as some underlying detail data has been updated in the master table, all materialized views using this data will become stale. Therefore, if you defer refreshing your materialized views, you can either rely on your chosen rewrite integrity level to determine whether or not a stale materialized view can be used for query rewrite or you can temporarily disable query rewrite with an ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE statement. After refreshing the materialized views, you can re-enable query rewrite as the default for all sessions in the current database instance by specifying ALTER SYSTEM SET QUERY_REWRITE_ENABLED as TRUE.

Refreshing a materialized view automatically updates all of its indexes. In the case of full refresh, this requires temporary sort space to rebuild all indexes during refresh. This is because the full refresh truncates or deletes the table before inserting the new full data volume. If insufficient temporary space is available to rebuild the indexes, then you must explicitely drop each index or mark it UNUSABLE prior to performing the refresh operation.

If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh.

Complete Refresh

A complete refresh occurs when the materialized view is initially defined as BUILD IMMEDIATE, unless the materialized view references a prebuilt table. For materialized views using BUILD DEFERRED, a complete refresh must be requested before it can be used for the first time. A complete refresh may be requested at any time during the life of any materialized view. The refresh involves reading the detail tables to compute the results for the materialized view. This can be a very time-consuming process, especially if there are huge amounts of data to be read and processed. Therefore, you should always consider the time required to process a complete refresh before requesting it. There are, however, cases when the only refresh method available for an already built materialized view is complete refresh because the materialized view does not satisfy the conditions for a fast refresh.

Fast Refresh

Fast refresh of your materialized views is usually efficient, because instead of having to recompute the entire materialized view, the changes are applied to the existing data. Thus, processing only the changes can result in a very fast refresh time.

Partition Change Tracking (PCT) Refresh

When there have been some partition maintenance operations on the detail tables, this is the only method of fast refresh that can be used. In the absence of partition maintenance operations on detail tables, when you request a FAST method (method => ‘F’) of refresh through procedures in DBMS_MVIEW package, Oracle will use a heuristic rule to try log-based fast refresh before choosing PCT refresh. Similarly, when you request a FORCE method (method => ‘?’), Oracle will choose the refresh method based on the following attempt order: log-based fast refresh, PCT refresh, and complete refresh. Alternatively, you can request the PCT method (method => ‘P’), and Oracle will use the PCT method provided all PCT requirements are satisfied. Oracle can use TRUNCATE PARTITION on a materialized view if it satisfies the conditions and hence, make the PCT refresh process more efficient.

ON COMMIT Refresh

A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes will be automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view. The only disadvantage is the time required to complete the commit will be slightly longer because of the extra processing involved. However, in a data warehouse, this should not be an issue because there is unlikely to be concurrent processes trying to update the same table.

Manual Refresh Using the DBMS_MVIEW Package

When a materialized view is refreshed ON DEMAND, one of four refresh methods can be specified as shown in the following table. You can define a default option during the creation of the materialized view.

You May Also Like