Performance Tuning   «Prev  Next»

Lesson 2 What is a materialized view?
Objective Describe a materialized view and its uses.

What is a Materialized View?

A materialized view is a very useful object. The basic function of a materialized view is to store pre-computed aggregate values or joins. The materialized view can be selected by the cost-based query optimizer to provide rapid retrieval of data without having to perform resource-intensive aggregate functions or joins.
The summary information in a materialized view is loaded into the view from one or more base tables of view. The view can then be queried, either explicitly or automatically. The flow of data through the view is illustrated in the following ToolTip:

  1. The base table holds the basic information, which is transformed and loaded into the materialized view
  2. A materialized view is formed from data that already exists in other tables.
  3. A user query against the materialized view, with the pre-computed results, is much faster than the same query against the base tables.
The base table holds the basic information, which is transformed and loaded into the materialized view

Using materialized view

Uses of a materialized view

There are several different areas where a materialized view is beneficial, as depicted in the following table:

Area Benefit
Data warehousing Improves performance by providing pre-computed aggregate values that are typically required in the dimensions of a data warehouse.
Distributed databases Used for snapshot replication to distributed databases.
Mobile computing Used for replication and aggregation transfer to mobile or remote clients.

Materialized views also provide automatic refresh capabilities, which are useful for data warehouse maintenance and replication in distributed or remote implementations.


Materialized views are copies (also known as replicas) of data, based upon queries. In its simplest form, a materialized view can be thought of as a table created by a command such as the following:
create table LOCAL_BOOKSHELF

In this example, a table named LOCAL_BOOKSHELF is created in the local database and is populated with data from a remote database (defined by the database link named REMOTE_CONNECT). Once the LOCAL_BOOKSHELF table is created, though, its data may immediately become out of sync with the master table (BOOKSHELF@REMOTE_CONNECT). Also, LOCAL_BOOKSHELF may be updated by local users, further complicating its synchronization with the master table. Despite these synchronization problems, there are benefits to replicating data in this way. Creating local copies of remote data may improve the performance of distributed queries, particularly if the master table’s data does not change frequently. You may also use the local table creation process to restrict the rows returned, restrict the columns returned, or generate new columns (such as by applying functions to selected values). This is a common strategy for decision-support environments, in which complex queries are used to periodically “roll up” data into summary tables for use during analyses.
Materialized views automate the data replication and refresh processes. When materialized views are created, a refresh interval is established to schedule refreshes of replicated data. Local updates can be prevented, and transaction-based refreshes can be used. Transaction-based refreshes, available for many types of materialized views, send from the master database only those rows that have changed for the materialized view. This capability, may significantly improve the performance of your refreshes.
In the next lesson, you will learn the syntax for creating materialized views.