Materialized view

From Wikipedia, the free encyclopedia

A view is a virtual table representing the result of a database query. Whenever an ordinary view's table is queried or updated, the database management system converts these into queries or updates against the underlying base tables. A materialized view takes a different approach in which the query result is stored as a concrete table that is infrequently updated from the original base tables. This enables much more efficient access, at the cost of some data being potentially out-of-date. In this way, use of a materialized view can be thought of as a formalization of denormalization. It is most useful in data warehousing scenarios, where frequent queries of the base tables themselves is 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 and primarily by the Oracle database.