Content deleted Content added
→Equivalency: pre instead of code |
Rescuing 1 sources and tagging 0 as dead.) #IABot (v2.0.9.5 |
||
| (329 intermediate revisions by more than 100 users not shown) | |||
Line 1:
{{Short description|Database stored query result set}}
{{More citations needed|date=December 2023}}
In a [[database]], a '''view''' is the [[result set]] of a stored [[Query language|query]] that presents a limited perspective of the database to a user.<ref name="mysql-views">{{cite web | url=https://dev.mysql.com/doc/refman/8.0/en/views.html | title=25.5 Using Views | author=<!--Not stated--> | date=2023-12-12 | website=MySQL | publisher=Oracle | access-date=2023-12-12 | quote=Views are stored queries that when invoked produce a result set. A view acts as a virtual table. | archive-url=https://web.archive.org/web/20231123070115/http://dev.mysql.com/doc/refman/8.0/en/views.html | archive-date=2023-11-23 | url-status=live }}</ref> This pre-established query command is kept in the [[data dictionary]]. Unlike ordinary ''[[base table]]s'' in a [[relational database]], a view does not form part of the [[database design|physical schema]]: as a result set, it is a virtual table<ref name="mysql-views" /> computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant ''underlying table'' are reflected in the data shown in subsequent invocations of the view.
Views can provide advantages over tables
* Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.<ref name="SQL-reference-groff-weinberg">{{cite book
| last1 = Groff
| first1 = James R.
| last2 = Weinberg | first2 = Paul N.
| date = 1999
| title = SQL: The Complete Reference
| url = http://englishonlineclub.com/pdf/SQL%20-%20The%20Complete%20Reference%20[EnglishOnlineClub.com].pdf
| ___location = <!-- not stated -->
| publisher = Osborne/McGraw-Hill
| pages = 291–292
| isbn = 0072118458
}}</ref>
*
* Views can act as aggregated tables, where
* Views can hide the complexity of data
* Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
* Views structure data in a way that classes of users find natural and intuitive.<ref name="SQL-reference-groff-weinberg"/>
Just
Just as [[Row (database)|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 — neither are the rows of a view. Therefore, an [[order by (SQL)|ORDER BY]] clause in the view definition is meaningless; the SQL standard ([[SQL:2003]]) does not allow an ORDER BY clause in the subquery of a CREATE VIEW command, just as it is refused 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 (programming)|statement]] on that view. Nevertheless, some DBMS (such as [[Oracle Database]]) do not abide by this SQL standard restriction.
▲* They can [[Join (SQL)|join]] and simplify multiple tables into a single virtual view
▲* Views can act as aggregated tables, where aggregated data ([[sum]], [[average]] etc.) are calculated and presented as part of the data
▲* Views can hide the complexity of data, for example a view could appear as Sales2000 or Sales2001, transparently [[Partition (database)|partitioning]] the actual underlying table
== Read-only vs. updatable views ==
▲Just like functions (in programming) provide abstraction, views can be used to create abstraction. Also, just like functions, views can be nested, thus one view can aggregate data from other views. Without the use of views it would be much harder to normalise databases above 2nd normal form. Views can make it easier to create lossless join decomposition.
Views can be
Some systems support the definition of INSTEAD OF [[Database trigger|triggers]] on views. This technique allows the definition of
==
{{further|Materialized view}}
▲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 (SQL)|INSERT]], [[Update (SQL)|UPDATE]], and [[Delete (SQL)|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 [[Database trigger|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.
Materialized views were introduced by [[Oracle Database]], while [[IBM 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. [[PostgreSQL]] implemented materialized views in its 9.3 release.
▲The [[Oracle database]] introduced the concept of [[materialized view]]s, 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. The equivalent of this in [[Microsoft SQL Server]], introduced in the 2000 version, is an indexed view. DB2 provides so-called ''materialized query tables (MQTs)'' for the same purpose.
==
A view is equivalent to its source query. When queries are run against views, the query is modified.
<syntaxhighlight lang="sql">
-- accounts_view:
-------------
SELECT name,
money_received,
money_sent,
(money_received -
address,
...
FROM table_customers c
JOIN accounts_table a
ON a.
</syntaxhighlight>
<syntaxhighlight lang="sql">
-- Simple query
------------
SELECT name,
balance
FROM accounts_view
</syntaxhighlight>
The RDBMS then takes the simple query, replaces the equivalent view, then sends the following to the
<syntaxhighlight lang="sql">
SELECT NAME,▼
-- Preprocessed query:
------------------
balance
FROM (SELECT name,
money_received,
money_sent,
(money_received -
address,
...
FROM table_customers c JOIN accounts_table a
ON a.
</syntaxhighlight>
The optimizer then removes unnecessary fields and 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]]
{{Databases}}
==References==
{{Reflist}}
{{DEFAULTSORT:View (Database)}}
[[Category:Database management systems]]
[[Category:SQL]]
[[Category:Database theory]]
| |||