Materialized view

Materialized view

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables 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.

Introduction

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: the Query rewrite feature was added from version 8i[1]. They are also supported in Sybase SQL Anywhere[2]. In IBM DB2, they are called "materialized query tables"; Microsoft SQL Server has a similar feature called "indexed views"[3]. MySQL doesn't support materialized views natively but workarounds can be implemented using triggers or stored procedures [4] or using the open source application Flexviews[5]. It is also possible to implement materialized views in PostgreSQL[6]

Syntax (Oracle):

 CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS SELECT * FROM <table_name>;

References

External links


Wikimedia Foundation. 2010.

Игры ⚽ Поможем написать реферат

Look at other dictionaries:

  • Materialized View — Eine View (deutsch Sicht) ist eine logische Relation (auch virtuelle Relation oder virtuelle Tabelle) in einem Datenbanksystem. Diese logische Relation wird über eine im Datenbankmanagementsystem (DBMS) gespeicherte Abfrage definiert. Der… …   Deutsch Wikipedia

  • View (database) — In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document oriented database composed of the result set of a query or map and reduce functions. Unlike ordinary… …   Wikipedia

  • View (Datenbank) — Eine View (deutsch Sicht) ist eine logische Relation (auch virtuelle Relation oder virtuelle Tabelle) in einem Datenbanksystem. Diese logische Relation wird über eine im Datenbankmanagementsystem (DBMS) gespeicherte Abfrage definiert. Der… …   Deutsch Wikipedia

  • Direct View — Ein Direct View ist eine lesende und schreibende Sicht auf eine Datenbank. Im Gegensatz zu einem View erlaubt ein Direct View, Daten einzufügen, zu ändern oder zu löschen und die Änderungen in die Datenbank konsistent zurück zu schreiben. Ein… …   Deutsch Wikipedia

  • Database — A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports… …   Wikipedia

  • Comparison of relational database management systems — Programming language comparisons General comparison Basic syntax Basic instructions Arrays Associative arrays String operations …   Wikipedia

  • Sicht (Datenbank) — Eine Sicht (englisch, SQL: View) ist eine logische Relation (auch virtuelle Relation oder virtuelle Tabelle) in einem Datenbanksystem. Diese logische Relation wird über eine im Datenbankmanagementsystem (DBMS) gespeicherte Abfrage definiert. Der… …   Deutsch Wikipedia

  • DbForge Schema Compare for Oracle — Developer(s) Devart Stable release 2.0.62 / 06/06/2011 Development status Active Written in C# …   Wikipedia

  • SQL Anywhere — is an RDBMS product from Sybase iAnywhere. iAnywhere is a subsidiary of Sybase. Features * Database files are operating system independent. This means that they can be copied between supported platforms. * Can be run on Windows, Windows CE,… …   Wikipedia

  • Navicat — Original author(s) PremiumSoft Developer(s) P …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”