| content | |||||||||||||||||||||||
Contents |
Read-only vs. updatable views
Views can be read-only or updatable. If the database system is able to determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable. INSERT, UPDATE, and DELETE operations can be performed on updatable views. Read-only views do not support such operations because the DBMS is not able to map the changes to the underlying base tables.
Some systems support the definition of INSTEAD OF triggers on views. This technique allows the definition of logic that shall be executed instead of an insert, update, or delete operation on the views. Thus, data modifications on read-only views can be implemented. However, an INSTEAD OF trigger does not change the read-only or updatable property of the view itself.
Advanced view features
Various database management systems have extended the views from read-only subsets of data. The Oracle database introduced the concept of materialized views, which are pre-executed, non-virtual views commonly used in data warehousing. They are a static snapshot of the data and may include data from remote sources. The accuracy of a materialized view depends on the frequency or trigger mechanisms behind its updates. DB2 provides so-called materialized query tables (MQTs) for the same purpose. Microsoft SQL Server, introduced in the 2000 version, indexed views which only store a separate index from the table, but not the entire data.
Equivalence
A view is equivalent to its source query. When queries are run against views, the query is modified. For example, if there exists a view named Accounts_view and the content is:
accounts view:
-------------
SELECT name,
money_received,
money_sent,
(money_received - money_sent) AS balance,
address,
...
FROM table_customers c
JOIN accounts_table a
ON a.customerid = c.customer_id
The application would simply run a simple query such as:
Sample query
------------
SELECT name,
balance
FROM accounts_view
The RDBMS then takes the simple query, replaces the equivalent view, then sends the following to the optimiser:
Preprocessed query:
------------------
SELECT name,
balance
FROM (SELECT name,
money_received,
money_sent,
(money_received - money_sent) AS balance,
address,
...
FROM table_customers c JOIN accounts_table a
ON a.customerid = c.customer_id )
From this point on the optimizer takes the query, removes unnecessary complexity (i.e. it is not necessary to read the address, since the parent invocation does not make use of it) and then sends the query to the SQL engine for processing.
External links
|
||||||||||||||||||||
