View (SQL)
A view (English, SQL: View) is a logical relation ( virtual relation or virtual table ) in a database system. This logical relation is defined in a database management system (DBMS ) query. The database user can query a view like a normal table. Whenever a query this view uses, this is previously calculated by the database management system. A view is an alias for a query essentially dar.
Example in SQL
SELECT SoftwareVerkaeufe AS SELECT v.kaeufer, v.verkaeufer FROM products p, sales v WHERE p.produkt_id = v.produkt_id AND p.produkt = "Software" or ( produces the same result ):
CREATE VIEW AS SoftwareVerkaeufe SELECT v.kaeufer, v.verkaeufer FROM products p, sales v WHERE p.produkt_id = v.produkt_id AND p.produkt = "Software" The queries are stored ie for all sales of products (s) that are " software ", the buyer and the seller list. If you want each occurring combination " seller / buyer " list only once, so the query to include an additional statement ( ' aggregation ') would have to be extended. This is true even if a particular sort order would be desired (SORT BY).
A subsequent query "SELECT FROM leaderseminar SoftwareVerkaeufe " would refer to the result of this view and would only list the seller ( who sold software), conveniently also with aggregation function.
Function of a view
The purpose of a view is to simplify access to the database schema. Normalized database schemas distribute data to many tables with complex dependencies. This leads to complex SQL queries. In addition, a high level of knowledge of the schema is provided to create such queries. The provision of appropriate perspectives enables easy access, without knowledge of the underlying schema, and without relaxing the normalization.
Another advantage of the views is that the DBMS does not require additional costs for the preparation of the query. The visual query is syntactically decomposed by the parser when creating and simplifies the query optimizer.
A disadvantage of views may be that the complexity of the underlying query is underestimated. The call of a view can lead to very complex queries and the reckless use of such then to significant performance problems.
Views are essential when merging ( Federate ) databases, since it is possible through them, to continue to allow existing programs to access the data whose structure may have changed by the Federation.
Older literature also provides views as a primitive means of data protection dar. Since modern DBMS, however, allow much more extensive data protection mechanisms and a means of sifting Privacy is not so given, views should not be used for this.
Types of views
Views can be divided into different classes according to the instructions used, which have different functions.
A view can select data from multiple tables simultaneously.
Updates
Updates to a view are not possible in general, since it can lead to anomalies. In the view can be read-only. In special cases where the DBMS in the view data to change and a physical table to which they belong, to establish a unique relationship between the an update is possible. Example of such an updatable view would be trivial following list:
CREATE VIEW '' SoftwareVerkaeufe2 '' AS '' SELECT '' verkaeufe.kaeufer An update on SoftwareVerkaeufe2 can be assigned unambiguously select verkaeufe.kaeufer here.
In the example below an unambiguous assignment is not possible, as is contained product_id in both source relations, ...
CREATE VIEW AS SoftwareVerkaeufe SELECT v.kaeufer, v.verkaeufer FROM products p, sales v WHERE p.produkt_id = v.produkt_id - ← Attention! AND p.produkt = "Software" And ... eg a deletion as in ...
DELETE FROM WHERE product_id = 123456 SoftwareVerkaeufe ... Is not decidable whether records from data truncated products or to delete.
Such anomaly arises in a situation where an implementation of the change is not the user's expectations corresponds or not is decidable, what changes must be carried out exactly. They can be divided as follows:
In SQL -92 only the change in pure selection tables is permitted. The WITH CHECK OPTION option in the CREATE VIEW statement then specifies whether changes that would lead to the disappearance of the record from the point of view, should be prohibited. In SQL -99, the amount has been significantly expanded to changeable views, but still remains behind the theoretically possible.
In more recent SQL dialects it is possible to use triggers to implement update operations on tables by hand.
Materialized View
In addition to conventional views, there are so-called materialized views ( materialized views in English ), also Indexed Views ( Microsoft) or Automatic Summary Tables (IBM) called. These are stored in the database copies of views (master sites) at any given time. They act as a cache to speed up access and to reduce the network load. Is utilized because of the large amounts of data, especially in OLAP. Meanwhile, these views also play a role in conventional databases, as they are considered in the design and optimization of QEPs.
There are several basic concepts to keep materialized views to date:
- Incremental updates ( logbasiert )
- Complete rebuild ( simple but extremely expensive )
And times:
- Transaction based on updating the base tables (so hidden cost of the updater )
- Time point bonded ( with occasionally not current data in the View)
These updates ( refreshes ) are based on a higher-level materialized view, or from a master site.
The theory of materialized views has been known since the 80s, but was implemented only recently, for example, Oracle, IBM and Microsoft in their products. The next step on the use of materialized views in the preparation of evaluation plans addition, the automatic creation of materialized views from the most meaningful intersection of requests from users.