Materialized view

From Wikipedia, the free encyclopedia
Jump to: navigation, search

In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.

The process of creating a materialized view is sometimes called materialization.[1] This is a form of caching the results of a query, similar to memoization of the value of a function in functional languages, and it is sometimes described as a form of precomputation.[2][3] As with other forms of precomputation, materialized views are typically created for performance reasons, i.e. as a form of optimization.[4]

Materialized views, which store data based on remote tables, are also known as snapshots. According to C. J. Date, the term "materialized view" is deprecated in favor of "snapshot".[1]

In any database management system following the relational model, a view is a virtual table representing the result of a database query. Whenever a query or an update addresses an ordinary view's virtual table, 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 expensive.

In a materialized view, indexes can be built on any column. In contrast, 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.[5]

Example syntax to create a materialized view in Oracle:

     AS SELECT * FROM <table_name>;


In PostgreSQL, version 9.3 and newer natively support materialized views.[6] In version 9.3, a materialized view is not auto-refreshed, and is populated only at time of creation (unless WITH NO DATA is used). It may be refreshed later manually using REFRESH MATERIALIZED VIEW.[7] In version 9.4, the refresh may be concurrent with selects on the materialized view if CONCURRENTLY is used.[8]


Materialized views are also supported in Sybase SQL Anywhere.[9] In IBM DB2, they are called "materialized query tables"; Microsoft SQL Server has a similar feature called "indexed views".[10] MySQL doesn't support materialized views natively, but workarounds can be implemented by using triggers or stored procedures [11] or by using the open-source application Flexviews.[12]


  1. ^ a b C.J. Date (28 August 2006). The Relational Database Dictionary: A Comprehensive Glossary of Relational Terms and Concepts, with Illustrative Examples. "O'Reilly Media, Inc.". pp. 59–. ISBN 978-1-4493-9115-7. 
  2. ^ Karen Morton; Kerry Osborne; Robyn Sands; Riyaj Shamsudeen; Jared Still (28 October 2013). Pro Oracle SQL. Apress. p. 48. ISBN 978-1-4302-6220-6. 
  3. ^ Marie-Aude Aufaure; Esteban Zimányi (16 January 2012). Business Intelligence: First European Summer School, EBISS 2011, Paris, France, July 3-8, 2011, Tutorial Lectures. Springer Science & Business Media. p. 43. ISBN 978-3-642-27357-5. 
  4. ^ Michael L. Gonzales (25 February 2003). IBM Data Warehousing: with IBM Business Intelligence Tools. John Wiley & Sons. p. 214. ISBN 978-0-471-45736-7. 
  5. ^ Oracle8i Tuning Release 8.1.5. Retrieved on 2012-02-09.
  6. ^ PostgreSQL: Materialized Views. (2010-05-07). Retrieved on 2013-09-25.
  7. ^ PostgreSQL: Documentation: 9.3: CREATE MATERIALIZED VIEW. Retrieved on 2014-01-25.
  8. ^ PostgreSQL: Documentation: 9.4: REFRESHED MATERIALIZED VIEW. Retrieved on 2015-01-23.
  9. ^ Materialized Views – Sybase SQL Anywhere. Retrieved on 2012-02-09.
  10. ^ Improving Performance with SQL Server 2005 Indexed Views. Retrieved on 2012-02-09.
  11. ^ Implementing materialized views in MySQL. (2006-11-06). Retrieved on 2012-02-09.
  12. ^ Flexviews for MySQL – incrementally refreshable materialized views w/ MySQL. Retrieved on 2012-02-09.

External links[edit]