Materialized Views

A materialized view is a variation of the regular database view. Both are results of database queries. The regular (non-materialized) view is only logically stored in the database (i.e. the contents are not physically stored, but dynamically computed whenever needed). On the other hand, the results of the materialized view are physically stored as a concrete table in the database, and are retrieved directly when referenced.

Since materialized views are physically stored, they may become inconsistent with their original data sources (when the data sources change due to updates). For this reason, materialized views have to be periodically updated to account for changes in the base tables. This is not an issue with non-materialized views, since they are re-computed from the original data every time they are referenced.

Therefore, materialized views allow much more efficient access (since they do not have to be re-computed all the time), at the cost of some data being possibly out-of-date. They are most useful in data warehousing scenarios, where the base tables are not updated very often, and frequent queries of the actual base tables can be extremely expensive.

Materialized views can be used exactly in the same way as regular views. They can be used to simplify queries, and hide their details, or to store common computations that are referenced by multiple queries.

However, they have one additional use that is the quite the opposite of regular views. Recall that during query processing, queries that reference views are usually rewritten to reference the base tables. However, the query optimizer tries to reuse materialized views to evaluate queries. This is known as materialized view matching and utilization. Since the contents of these materialized views are already precomputed, using them for query evaluation can result in a significant performance improvement.

Database Views

A database view is a stored query accessible as a virtual table. The contents of this virtual table are the results of that query. Views are only logically stored in the database (i.e. the contents are not physically stored, but dynamically computed whenever needed). Materialized views are an exception to this rule. Changing the data in a base table automatically alters any view that references that table.

A view can be used in exactly the same way as a base table. Views can be used for the following reasons:

• Simplify queries by creating views to represnts parts of these queries
• Hiding the complexity of the data, by creating views that reflect on the parts of the data relevant to a particular user
• Providing extra security by giving users access only to the views, not to the base tables, thus restricting the data they can access
• Since the actual contents of the views are not physically stored, they do not require much space

Views can be seen in analogy to modules (or functions) in programming. They can be used to create abstraction. They can be nested (a query can reference a view, and a view can reference other views, etc.)

For example, consider the following two queries:

Query 1:		Query 2:
SELECT name, balance FROM (SELECT name, money_received, money_sent, (money_received - money_sent) AS balance, address FROM table_customers c, accounts_table a WHERE a.customerid = c.customer_id ) WHERE money_sent > 10000		SELECT name, address FROM (SELECT name, money_received, money_sent, (money_received - money_sent) AS balance, address FROM table_customers c, accounts_table a WHERE a.customerid = c.customer_id ) WHERE balance between 10000 and 20000

Both these queries contain the same sub-query. To simplify these queries, it is possible to define a view (v_cust_account) as follows:

CREATE VIEW v_cust_account AS
SELECT name, money_received, money_sent,
(money_received - money_sent) AS balance, address
FROM table_customers c, accounts_table a
WHERE a.customerid = c.customer_id


Now this view can be treated as a table containing the results of the subquery. Now the 2 initial queries can be simplified as follows:

Query 1a:		Query 2a:
SELECT name, balance FROM v_cust_account WHERE money_sent > 10000		SELECT name, address FROM v_cust_account WHERE balance between 10000 and 20000

Views are usually read-only, i.e. one cannot directly update the data in a view, but can only update the underlying tables. However, in some cases, a view can be updatable, but only if the database system is able to determine the reverse mapping from the view schema to the schema of the underlying base tables. In this case, INSERT, UPDATE, and DELETE operations can be performed. Read-only views do not support such operations because the DBMS is not able to map the changes to the underlying base tables.

During query processing, queries that reference views are usually rewritten to reference the base tables. For example, if a user issues Query 1a to the DBMS, the query processor will determine that the query references the view (v_cust_account). It will then proceed to rewrite this query to reference the base tables (thus it will look like the original query (Query 1).