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 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.
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 extremely expensive.
In addition, because the materialized 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. They are also supported in Sybase SQL Anywhere. In IBM DB2, they are called "materialized query tables"; Microsoft SQL Server has a similar feature called "indexed views". MySQL doesn't support materialized views natively, but workarounds can be implemented by using triggers or stored procedures  or by using the open-source application Flexviews. It is also possible to implement materialized views in PostgreSQL; version 9.3 natively supports materialized views. In the current version 9.3 the materialized views are not auto-refreshed, and are populated only at time of creation (unless WITH NO DATA is used) and may be refreshed later manually using REFRESH MATERIALIZED VIEW command.
Example syntax to create a materialized view in Oracle:
CREATE MATERIALIZED VIEW MV_MY_VIEW REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT * FROM <table_name>;
- Oracle8i Tuning Release 8.1.5. Ecst.csuchico.edu. Retrieved on 2012-02-09.
- Materialized Views – Sybase SQL Anywhere. Ianywhere.com. Retrieved on 2012-02-09.
- Improving Performance with SQL Server 2005 Indexed Views. Microsoft.com. Retrieved on 2012-02-09.
- Implementing materialized views in MySQL. Shinguz.ch (2006-11-06). Retrieved on 2012-02-09.
- Flexviews for MySQL – incrementally refreshable materialized views w/ MySQL. Flexviews.sourceforge.net. Retrieved on 2012-02-09.
- PostgreSQL: Materialized Views. Wiki.postgresql.org (2010-05-07). Retrieved on 2013-09-25.
- PostgreSQL: Documentation: 9.3: CREATE MATERIALIZED VIEW. PostgreSQL.com. Retrieved on 2014-01-25.
- Materialized View Concepts and Architecture – Oracle
- SQL Snippets: SQL Features Tutorials – Materialized Views – Oracle
- Oracle9i Replication Management API Reference Release 2 (9.2)