Materialized view

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables, are also known as snapshots. A snapshot can be redefined as a materialized view.

Introduction

In a database management system following the relational model, a view is a virtual table representing the result of a database query. Whenever an ordinary view's table is queried or updated, the DBMS converts these into queries or updates against the underlying base tables. A materialized view takes a different approach in which the query result is cached as a concrete table that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of some data being potentially out-of-date. It is most useful in data warehousing scenarios, where frequent queries of the actual base tables can be extremely expensive.

In addition, because the view is manifested as a real table, anything that can be done to a real table can be done to it, most importantly building indexes on any column, enabling drastic speedups in query time. In a normal view, it's typically only possible to exploit indexes on columns that come directly from (or have a mapping to) indexed columns in the base tables; often this functionality is not offered at all.

Materialized views were implemented first by the Oracle Database: the Query rewrite feature was added from version 8i^[1]. They are also supported in Sybase SQL Anywhere^[2]. In IBM DB2, they are called "materialized query tables"; Microsoft SQL Server has a similar feature called "indexed views"^[3]. MySQL doesn't support materialized views natively but workarounds can be implemented using triggers or stored procedures ^[4] or using the open source application Flexviews^[5]. It is also possible to implement materialized views in PostgreSQL^[6]

Syntax (Oracle):

 CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS SELECT * FROM <table_name>;

References

1. ^ Oracle8i Tuning Release 8.1.5
2. ^ Materialized Views - Sybase SQL Anywhere
3. ^ Improving Performance with SQL Server 2005 Indexed Views
4. ^ Implementing materialized views in MySQL
5. ^ Flexviews for MySQL - incrementally refreshable materialized views w/ MySQL
6. ^ PostgreSQL: Materialized Views

External links

• Materialized View Concepts and Architecture - Oracle
• SQL Snippets: SQL Features Tutorials - Materialized Views - Oracle
• Oracle9i Replication Management API Reference Release 2 (9.2)