Monday, February 11, 2013

Materialized view or Indexed view

A materialized view or indexed view is a db object that stores the results of a query physically. 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 dbms 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.
As the materialized view is like a real table, anything that can be implemented on a db table can be implemented on it.  Creating indexes on any column, resulting improvements in query performance time.

No comments:

Post a Comment