This tutorial gives an overview of views that can be created on tables and demonstrates the same. Some of the benefits of views are discussed.

Problem Description


Business data contains multiple tables and queries typically address only some relevant data in the tables. A full table scan during query processing in such cases may be avoided.

Business data invariably contains sensitive information. A view can restrict access to this sensitive information from user to user thus ensuring security of data.

Operation


Views are created over the tables for faster query processing and protecting sensitive information present in some tables

Solution


A view is an efficient way of representing data without the need to maintain it. A view is not an actual table and requires no permanent storage. A "virtual table" is created and used. It consists of a stored query accessible as a virtual table composed of the result set of a query. 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.

Views can provide advantages over tables :

  1. Views can represent a subset of the data contained in a table
  2. Views can join and simplify multiple tables into a single virtual table
  3. 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
  4. Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
  5. 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
  6. Views can limit the degree of exposure of a table or tables to the outer world