Views

A view can be thought of as a virtual table. Only the definition for the view is stored, not the data. A table on which a view operates is called a base table.

A view definition can encompass 1 to 31 base tables. It can involve multiple tables joined together by their common columns using a where qualification.

A view can be created on other views or on physical database tables, including secondary indexes.

Primary uses for views include:

• Providing security by limiting access to specific columns in selected tables, without compromising database design
• Simplifying a commonly used query
• Defining reports

Because a view is a device designed primarily for selecting data, all selects on views are fully supported. Simply use a view name in place of a table name in the select statement. Updating views is also supported (see Updates on Views), but updating a database by means of a view is not recommended.

Views and Permissions

Any user can create a view on any other user's tables or views, provided they have the permissions required to execute the select statements that define the view. Any user can grant permissions on their views to any other user, provided they either own the base tables in the view or have the with grant option on the permissions they are granting. The granting of permissions is described in Object Permissions.

Working with View Objects

You can perform the following basic operations on views:

• Create view objects
• View existing view objects, including details such as the view definition, grantees, and rows
• Drop view objects

In VDBA, use the Views branch for a particular database in the Database Object Manager window.

You can also accomplish these tasks using the SQL statements create view, help view, and drop view. For more information, see the SQL Reference Guide.

Note: To drop a view the cache size that was needed to create the view must be enabled.

Updates on Views

Only a limited set of updates on views is supported because of problems that can occur. Updates are not supported on views that have more than one base table, or on any column whose source is not a simple column name (for example, set functions or computations). If the view was created using the With Check Option enabled in the Create View dialog, no updates or inserts are allowed on columns that are in the qualification of the view definition. For more information on the With Check Option control, see online help for the Create View dialog.

Updating is supported only if it can be guaranteed (without looking at the actual data) that the result of updating the view is identical to that of updating the corresponding base table.

Note: Updating, deleting, or inserting data in a table using views is not recommended. You cannot update or insert into a view with Query-By-Forms. You can update, delete, or insert with SQL statements, but you must abide by the following rules, keeping in mind that an error occurs if you attempt an operation that is not permitted.

Types of Updates Not Permitted on Views

You cannot perform the following types of updates on a view:

• One that involves a column that is a set function (aggregate) or derived from a computational expression

  In the following example of a select statement used to define a view, you cannot update the tsal column because it is a set function:

  select dept, sum(sal) as tsal
   from deptinf group by dept

• One that causes more than one table to be updated

  Consider the following example of a select statement used to define a view:

  select e.name, e.dept, e.div, d.bldg
    from emp e, deptinf d
    where e.dept = d.dname and e.div = d.div

  Updates to this data must be done through the underlying base tables, not this view.

• You can update a column that appears in the qualification of a view definition, as long as the update does not cause the row to disappear from the view. For example, if the where clause is as follows, update the deptno from 5 to 8, but not from 5 to 20:

  where deptno < 10