Materialized view

From Wikipedia, the free encyclopedia

Jump to: navigation, search

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.

There are three types of materialized views:

  1. Read only
    • Cannot be updated and complex materialized views are supported
  2. Updateable
    • Can be updated even when disconnected from the master site.
    • Are refreshed on demand.
    • Consumes fewer resources.
    • Requires Advanced Replication option to be installed.
  3. Writeable
    • Created with the for update clause.
    • Changes are lost when view is refreshed.
    • Requires Advanced Replication option to be installed.

[edit] External links

 This database-related article is a stub. You can help Wikipedia by expanding it.
Retrieved from "http://en.wikipedia.org/wiki/Materialized_view"
Personal tools