A view can be compared to a window through which you can see selected portions of a database. Views retrieve portions of one or more tables or other views.
Views and Tables
Views look like tables to a user, but are virtual, not physical, tables. Views display data in columns and rows and can be used like physical tables. However, only the column definitions for a view are stored: views are not physical tables.
A view does not contain data. A view is a virtual table whose definition is stored in the Data Dictionary. The view is not materialized until referenced by a statement. Operations permitted for the manipulation of tables may not be valid for views, and other operations may be restricted, depending on the view definition.
Defining a View
The CREATE VIEW statement defines a view. The statement names the view and its columns, defines a SELECT on one or more columns from one or more underlying tables or views, and can include conditional expressions and aggregate operators to limit the row retrieval.
Using Views
The primary reason to use views is to simplify end user access to the database. Views provide a constant vantage point from which to examine and manipulate the database. Their perspective is altered neither by adding or nor by dropping columns from its component base tables unless those columns are part of the view definition.
From an administrative perspective, views are useful for providing an easily maintained level of security and authorization. For example, users in a Human Resources department can access tables containing sensitive payroll information without being able to see salary and bonus columns. Views also provide administrators with an ability to control read and update privileges on the database with little effort.
View Restrictions
- You cannot create an index on a view.
- A view definition cannot contain an ORDER BY clause.
- Any derived columns in a view must explicitly specify view column names, for example by using an AS clause or by providing a column list immediately after the view name.
- You cannot update tables from a view under the following circumstances:
- The view is defined as a join view (defined on more than one table)
- The view contains derived columns.
- The view definition contains a DISTINCT clause.
- The view definition contains a GROUP BY clause.
- The view defines the same column more than once.