12.6 Creating a view table

Tables can be classified into base tables and view tables. A base table is an actual table. A view table is a virtual table defined by selecting rows and columns from the base table.

Organization of this section
(1) Relationship between base tables and view tables
(2) Effects of creating view tables
(3) How to create view tables
(4) Deleting view tables

(1) Relationship between base tables and view tables

The following figure shows the relationship between a base table and a view table.

Figure 12-18 Relationship between a base table and a view table

[Figure]

Explanation:
This example uses base table STOCK to create view table VSTOCK, which consists of the product code (PCODE), stock quantity (SQUANTITY), and unit price (PRICE) columns for those rows with SOCKS in the product name (PNAME) column.
Let's assume that a branch office needs to reference only the three data items product code, stock quantity, and unit price for the products whose product name is SOCKS. For this purpose, base table STOCK is set to be inaccessible, and view table VSTOCK is set to be accessible for referencing purposes only (SELECT privilege). In this way, data can be protected while allowing necessary information to be referenced.

(2) Effects of creating view tables

The effects of creating view tables are discussed as follows.

Improved security
To improve security for a specific table, the table should be used as a base table and view tables should be created from it. Doing this enables only selected columns and rows to be disclosed. Row and column levels of security can be achieved by granting access privileges to the view tables only.
Improved operability
  • If a table is retrieved on the basis of specifying a complicated query, a view table that contains the data obtained from that query should be created, so that there is no need to issue the complicated query again. This simplifies table referencing operations.
  • A view table can be used to reference or update its base table. As a result, when the base table definition is modified, there is no need to modify the SQL statements or the view table definition depending on the nature of the modification.

(3) How to create view tables

View tables are created with the CREATE VIEW definition SQL statement. The CREATE VIEW statement can define the following view tables:

Rules
  1. A single view table can be defined with up to 30,000 columns.
  2. Columns cannot be added to a view table, and indexes cannot be defined.
  3. The owner of a view table defined from base tables owned by that user holds all privileges (row retrieval, add, delete, update) to that view table.
  4. The owner of a view table defined from base tables owned by another user holds the same privileges that he or she holds for those base tables. However, if the view table definition has any of the following definitions, only row retrieval is allowed, regardless of whether the security facility is used:
  • View tables for which the columns contain multiple specifications of the same columns from base tables
  • View tables for which the columns contain the results of literals, USER, CURRENT_DATE, CURRENT_TIME, arithmetic operations, date operations, time operations, concatenation operations, or scalar functions
  • Multiple base tables have been specified
  • DISTINCT, set functions (COUNT(*), AVG, MAX, MIN, SUM), grouping (GROUP BY clause), or group conditions (HAVING clause) have been specified
    If the security facility has not been used, view tables other than those noted above can be freely updated by other users. However, read-only view tables (READ ONLY specification) cannot be updated by other users regardless of the security facility.

(4) Deleting view tables

You use the DROP VIEW definition SQL statement to delete view tables. When a view table is deleted, all related access privileges are also deleted.