View (database)

from Wikipedia, the free encyclopedia

A view ( English , SQL : View ) is a logical relation (also virtual relation or virtual table ) in a database system . This logical relation is defined via a query stored in the database management system (DBMS) . The database user can query a view like a normal table. Whenever a query uses this view, it is calculated beforehand by the database management system. A view is essentially an alias for a query.

Example in SQL

 SELECT SoftwareVerkaeufe AS
   SELECT v.kaeufer, v.verkaeufer
   FROM   produkte p, verkaeufe v
   WHERE  p.produkt_id = v.produkt_id
      AND p.produkt = "Software"

or (produces the same result): 

 CREATE VIEW SoftwareVerkaeufe AS
   SELECT v.kaeufer, v.verkaeufer
   FROM   produkte p, verkaeufe v
   WHERE  p.produkt_id = v.produkt_id 
      AND p.produkt = "Software"

The queries will therefore list the buyer and the seller for all saved verkaeufeby produkte(n)who are "software". If you want to list each “seller / buyer” combination that occurs only once, the query would have to be expanded to include an additional instruction ( 'aggregation' ). This also applies if a certain sort sequence is desired (ORDER BY).

A subsequent query would refer to the result of this view and would only list the sellers (who sold the software), expediently also with an aggregation function. "SELECT verkaeufer FROM SoftwareVerkaeufe"

Function of a view

The task of a view is to simplify access to the database schema. Normalized database schemas distribute data across numerous tables with complex dependencies. This leads to complex SQL queries. In addition, a high level of schema knowledge is required to create such queries. The provision of suitable views allows easy access without knowledge of the underlying schema and without weakening the normalization .

Another advantage of views is that the DBMS does not need any additional effort to prepare the query. The view query was syntactically broken down by the parser when it was created and simplified by the query optimizer .

A disadvantage of views can be that the complexity of the query behind them is underestimated. Calling up a view can lead to very complex queries and the careless use of these can lead to considerable performance problems.

Views are essential when merging (federating) databases, as they make it possible to continue to allow existing programs access to data, the structure of which may have changed due to the federation.

Views can also be used as a means of data protection - for example in connection with roles .

Types of views

Based on the instructions used, views can be divided into different classes that have different tasks.

  1. A selection view filters certain rows from a table.
  2. A projection view filters certain columns.
  3. A composite view links several tables.
  4. An aggregation view applies aggregation functions ( MIN, MAX, COUNTetc.).
  5. A recursive view (not possible in SQL) applies a view to its result over and over again.
  6. An object-relational view is based on a user-defined data type and represents the object-relational view of a relational table.

A view can select data from several tables at the same time.

Updates

Updates to a view are generally not possible as they can lead to anomalies. The view can then only be accessed for reading. An update is possible in special cases in which the DBMS can establish a clear association between the data to be changed in the view and a physical table to which it belongs. An example of such an updatable view would be the following trivial view: 

 CREATE VIEW ''SoftwareVerkaeufe2'' AS ''SELECT verkaeufe.kaeufer''

An update to can be clearly assigned here. SoftwareVerkaeufe2select verkaeufe.kaeufer

In the example below, a clear assignment is not possible because produkt_idboth source relations contain ... 

 CREATE VIEW SoftwareVerkaeufe AS
   SELECT v.kaeufer, v.verkaeufer
     FROM produkte p, verkaeufe v
    WHERE p.produkt_id = v.produkt_id -- ← Achtung!
      AND p.produkt = "Software"

… and Z. B. with a deletion as in ... 

 DELETE FROM SoftwareVerkaeufe WHERE produkt_id = 123456

... it cannot be decided whether data records should be deleted Produkteor Verkaeufedeleted.

Such an anomaly arises in a situation where the implementation of the change does not meet the expectations of the user or it cannot be decided which changes are exactly to be implemented. They can be classified as follows:

  1. In a selection view, data records can disappear from the visible area if a data record in the view is changed in such a way that it falls out of view ("tuple migration").
  2. In a projection NOT NULLview , an insert operation can become problematic if there are fields in the original relation that must be occupied ( ) but do not appear in the view, or if the view combines the DISTINCTsame result tuples into one by specifying the same result tuples.
  3. In a composite view, it is not always possible to decide on which original relation the operation is to be carried out.
  4. In an aggregation view, it cannot be decided how the operation is to be implemented. For example, it is not clear how to halve all sales to the original ratio: Either half of the sales can be deleted or the individual sales can be halved.
  5. With recursive views (possible from Oracle 10g, DB2 V8), e.g. B. the calculation of all ancestors of a person from a table PARENTS (parent, child), it is not clear how an insert operation ("add ancestor") is to be implemented.

In SQL-92 , only the change of pure selection views is allowed. The option WITH CHECK OPTIONin the CREATE VIEWstatement then specifies whether changes that would lead to the data record disappearing from view should be prohibited. In SQL-99 , the number of changeable views has been significantly expanded, but still lags behind what is theoretically possible.

In recent SQL dialects it is possible to trigger to use to implement update operations on views by hand.

Materialized view

In addition to the conventional view, there are so-called materialized views (English materialized views ), and Indexed Views ( Microsoft ) or Automatic Summary Tables ( IBM called). These are copies of views ( master sites ) at a specific point in time stored in the database . They act as a cache to speed up access and reduce network load. This is mainly used in OLAP because of the large amounts of data . These views now also play a role in conventional databases, as they are taken into account when creating and optimizing QEPs .

There are several basic ideas for keeping materialized views up to date:

  • incremental updates (log-based)
  • complete rebuild (simple but extremely expensive)

and times:

  • transaction-based when updating the base tables (thus hidden costs for the updater)
  • time-bound (with temporarily non-current data in the view)

These refreshes come from a higher-level materialized view or from a master site .

The theory of materialized views has been known since the 1980s, but has only been used since 1998. B. implemented by Oracle (from version 8i), IBM (DB2, but not Informix) and Microsoft in their products. The next step on the use of materialized views in the preparation of evaluation plans also is the automatic creation of materialized views in the most meaningful intersection of requests from users.

Individual evidence

  1. ↑ Uwe Klug: Designing & programming database applications. in Google Book Search
  2. ↑ Ashish Gupta, Inderpal Singh Mumick: Materialized views . techniques, implementations, and applications. Massachusetts Institute of Technology, 1999, ISBN 0-262-57122-6 , pp. 3 .
  3. ^ A b Ted Burroughs, Randy Urbano: Oracle9i Advanced Replication . 2nd Edition. Oracle Corporation, 2002, Chapter 3 ( online edition ).
  4. ↑ Oracle 8.1.5 Tuning Release A67775-01