View (database)

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 tables (base tables) in a relational database, a view does not form part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database. Changing the data in a table alters the data shown in subsequent invocations of the view. In some NoSQL databases views are the only way to query data.

Views can provide advantages over tables:

  • Views can represent a subset of the data contained in a table
  • Views can join and simplify multiple tables into a single virtual table
  • Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
  • Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
  • Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents
  • Depending on the SQL engine used, views can provide extra security
  • Views can limit the degree of exposure of a table or tables to the outer world

Just as functions (in programming) can provide abstraction, so database users can create abstraction by using views. In another parallel with functions, database users can manipulate nested views, thus one view can aggregate data from other views. Without the use of views the normalization of databases above second normal form would become much more difficult. Views can make it easier to create lossless join decomposition.

Just as rows in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered - by definition - the rows in a view are not ordered, either. Therefore, an ORDER BY clause in the view definition is meaningless. The SQL standard (SQL:2003) does not allow an ORDER BY clause in a subselect in a CREATE VIEW statement, just as it is not allowed in a CREATE TABLE statement. However, sorted data can be obtained from a view, in the same way as any other table - as part of a query statement. Nevertheless, some DBMS (such as Oracle and SQL Server) allow a view to be created with an ORDER BY clause in a subquery, affecting how data is displayed.

Contents

Read-only vs. updatable views

Database practitioners can define views as read-only or updatable. If the database system can 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 cannot map the changes to the underlying base tables. A view update is done by key preservation.

Some systems support the definition of INSTEAD OF triggers on views. This technique allows the definition of other logic for execution in place of an insert, update, or delete operation on the views. Thus database systems can implement data modifications based on read-only views. 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: pre-executed, non-virtual views commonly used in data warehousing. They give 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 its 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 with the content as follows:

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.customer_id = c.customer_id

then the application could 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.customer_id = c.customer_id        )

From this point on the optimizer takes the query, removes unnecessary complexity (for example: 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.

See also

  • Bidirectionalization

External links


Wikimedia Foundation. 2010.

Игры ⚽ Нужно сделать НИР?

Look at other dictionaries:

  • View (database) — Представление (англ. view, в сленге программистов часто используется в качестве заимствования из английского «вью») виртуальная (логическая) таблица, представляющая собой поименованный запрос (алиас к запросу), который будет подставлен как… …   Википедия

  • View (disambiguation) — A view is something which may be observed as a result of visual perception.View or viewing may also refer to: * View (database), a virtual or logical table composed of the result set of a query in relational databases * View , a word processor… …   Wikipedia

  • View — A view is what can be seen in a range of vision. View may also be used as a synonym of point of view in the first sense. View may also be used figuratively or with special significance for example, to imply a scenic outlook or special vantage… …   Wikipedia

  • Database Marketing Agency — Type Private Industry Financial Services Founded Beaverton, Oregon (1996 (1996)) Founder(s) Rich Weissman …   Wikipedia

  • Database design — is the process of producing a detailed data model of a database. This logical data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a Data Definition Language, which… …   Wikipedia

  • Database administration and automation — Database administration is the function of managing and maintaining database management systems (DBMS) software. Mainstream DBMS software such as Oracle, IBM DB2 and Microsoft SQL Server need ongoing management. As such, corporations that use… …   Wikipedia

  • Database activity monitoring — (DAM) is a database security technology for monitoring and analyzing database activity that operates independently of the database management system (DBMS) and does not rely on any form of native (DBMS resident) auditing or native logs such as… …   Wikipedia

  • Database storage structures — Database tables/indexes are typically stored on hard disk in one of many forms, ordered/unordered Flat files, ISAM, Heaps, Hash buckets or B+ Trees. These have various advantages and disadvantages discussed in this topic. The most commonly used… …   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

  • Database management system — A database management system (DBMS) is a software package with computer programs that control the creation, maintenance, and the use of a database. It allows organizations to conveniently develop databases for various applications by database… …   Wikipedia

Share the article and excerpts

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