12.6 Creating a view table
(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]](figure/zu120140.gif)
- 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:
- View tables made from selected rows and columns of base tables
- View tables with columns determined from set functions, date operations, time operations, concatenation operations, scalar functions, or arithmetic operations performed on values from columns of base tables
- One view table based on a maximum of 64 base tables
- View tables based on the result of grouping retrieval
- View tables based on base tables that are owned by other users (limited to base tables or owned by other users and for which the SELECT privilege has been granted)
- Rules
- A single view table can be defined with up to 30,000 columns.
- Columns cannot be added to a view table, and indexes cannot be defined.
- 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.
- 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.