Materialized Views - An Overview

Introduction

I have been asked to provide a brief overview of materialized views, particularly how they could be used in relation to Online Reporting. This document will cover the following topics:

• description of what a materialized view is
• how materialized views work
• the frequency of update
• performance overheads
• other issues, relating to online reporting

Some relevant URLs will also be provided.

What is a materialized view?

A materialized view is a new type of database object introduced in Oracle 8i. Like a standard view, a materialized view represents data stored in other database tables. However, a materialized view contains actual data. So, a materialized view is like an index, which also contains data derived from other tables and views. However, the data in a materialized view must be explicitly refreshed (indexes are refreshed automatically). A materialized view is also similar to a snapshot since administrators can specify when the data is to be refreshed. A materialized view differs from a snapshot because the materialized view should either include summary data or data from several joined tables.

Materialized views can be accessed directly using a SELECT statement. Depending on the type of refresh required, materialized views can also be accessed directly in INSERT, UPDATE or DELETE statements.

The main applications of materialized views are in:

• data warehousing
• replication

Why use a materialized view?

In broad terms, the benefits of using materialized views are:

• to increase speed of queries on large databases
• to replicate data. In fact, CREATE MATERIALIZED VIEW is a synonym for CREATE SNAPSHOT

However, there are costs associated with materialized views:

• performance costs of maintaining the additional database object
• storage costs of maintaining the database object

Creating a materialized view

To create a materialized view, a specific Data Definition Language (DDL) command is used. The basic syntax is as follows:

    CREATE MATERIALIZED VIEW <name>
      <storage_options>
      BUILD <build_clause_body>
      REFRESH <refresh_clause_body>
      [ENABLE QUERY REWRITE]
      AS
        SELECT <select_clause_body>
    

The body of the BUILD clause allows the administrator to specify when to build the actual data in the table. There are three options: IMMEDIATE, DEFERRED (when first scheduled within REFRESH clause) or PREBUILT TABLE.

The body of the REFRESH clause specifies when and how the data in the view is to be refreshed to reflect changes in the underlying database.

"When" options:

• ON COMMIT
  refresh occurs automatically on the next COMMIT to the master table(s)
• ON DEMAND
  refresh occurs when a user manually executes one of the available refresh procedures in the DBMS_MVIEW package
• at specified times
  refreshes can be initiated using JOB_QUEUE_PROCESSES or JOB_QUEUE_INTERVAL parameters

"How" options:

• COMPLETE
  completely recreates the materialized view, by recalculating the query which defines the materialized view
• FAST
  performs an incremental refresh
• FORCE
  determines if a FAST refresh is possible, otherwise performs a COMPLETE refresh
• NEVER
  suppresses refreshing of the materialized view

There are limitations on when some of these option can be used. For example, the FAST option cannot be used if the select clause contains a subquery or a set function.

Consult the Oracle 8i documentation for a more complete treatment of the syntax of the CREATE MATERIALIZED VIEW statement, consult the documentation at Oracle Technet.

How materialized views work

As indicated above, materialized views are variations of views which contain actual data. They are stored summaries of queries containing pre-computed results.

Materialized views improve query performance by pre-calculating expensive join and aggregation operations on the database in advance and storing these results in the database.

There are other structures in Oracle 8 which are very similar to materialized views, namely summary tables and pre-joined views. Materialized views differ from these in that they interact directly with the Oracle 8i cost-based optimizer. The cost-based optimizer can automatically substitute a materialized view for a standard table or group of tables, without any need to rewrite the applications which access the data. Even if the materialized view can satisfy part of the query, the optimizer can still use the materialized view for as much of the data as possible.

The query rewrite facility is activated by including ENABLE QUERY REWRITE clause when creating the materialized view. However, query rewrite is only possible where the materialized view is stored in the same database as its fact or details tables.

Other considerations

In the case of online reporting, which currently is a separate database, query rewrite will not be possible. In other words, the online reporting queries will need to be on the materialized view directly. This should not be a show-stopper.

Materialized views can be partitioned if necessary.

Indexes can be created on materialized views.

Materialized views enhance the benefits of snapshots by being able to perform fast refreshes. This reduces the network traffic between the master and the remote sites by avoiding a full refresh and improves overall refresh performance.

Based on the current online reporting system, it is highly likely that the FAST refresh option will be available. This will reduce the time required to update the materialized view.

A materialized view maintenance policy will need to be thought out.

Since the existing online reporting system makes use of snapshots, the storage overhead of a materialized view will not be much different than the current system. Furthermore, since snapshots are synonyms for materialized views, online reporting is already making use of materialized views. Examination of the various options will possibly improve the efficiency of the system.

Reference URLs - Materialized Views

The following resources were used in the preparation of this document.

• Oracle Technet: Oracle 8i Tuning - Materialized Views
• Oracle Technet: Oracle 8i - SQL Statements - Materialized Views
• oracle.oreilly.com News -- Oracle 8i: The Materialized View
• Oracle Magazine: Using Materialized Views to Speed Up Queries
• Oracle Doc: Oracle 8i Data Warehousing - Materialized Views
• Labio, W.; Quass, D.; Adelberg, B.: Physical Database Design for Data Warehousing