2.1.2 Viewed tables

A virtual table created by defining the result of the query expression specified in the CREATE VIEW statement as a new table is called a viewed table. For example, you can define the result of a set operation for multiple tables as a viewed table, or can define the specific rows or columns in a table as a viewed table.

By predefining a viewed table for the results of a conditioned search that will be performed many times, you can simplify the database retrieval operation. The following figure shows an example of a viewed table.

Figure 2‒1: Example of a viewed table

Explanation: The VSTOCK viewed table is created from the STOCK table, which is the base table. The VSTOCK viewed table consists of the columns PCODE (product code), PRICE (unit price), and QUANTITY (inventory quantity), and contains only the rows whose entry in the PNAME (product name) column is Shirt.

Note

Defining a viewed table has advantages like the following in terms of security:

By defining a viewed table that allows only specific columns or rows in the table to be referenced, you can hide data that is irrelevant and data to which access must be restricted.
Because there is no need to notify database users of the table name of the base table that actually stores the data, the use of viewed tables has the effect of preventing unauthorized access using base table names. This applies when you create a viewed table whose underlying table is a viewed table.

You can allow database users to reference data by directly referencing the base table in which the data is actually stored. However, if you want to allow users to only reference certain data within the table (such as a specific column), we recommend that you use viewed tables.

Organization of this subsection

(1) Updatable viewed tables and read-only viewed tables
(2) About underlying tables of viewed tables
(3) Invalidating viewed tables

(1) Updatable viewed tables and read-only viewed tables

Viewed tables are categorized into the following two types. A viewed table can be defined as an updatable viewed table or read-only viewed table.

Updatable viewed table

You can insert, update, and delete rows for this type of viewed table, in the same way as for a base table. However, you cannot add columns and define indexes.

When you perform a row insertion, update, or deletion operation for an updatable viewed table, the same operation (row insertion, update, or deletion) is also performed for the underlying table of the updatable viewed table.
Read-only viewed table

You cannot insert, update, and delete rows for this type of viewed table. You cannot add columns and define indexes, either.

To Page Top

(2) About underlying tables of viewed tables

The table that underlies a viewed table is called an underlying table. The table specified in the query expression of a CREATE VIEW statement becomes the underlying table.

▪ What is a view level?

A value that indicates the hierarchical depth of a defined viewed table from the base table. The following figure shows an example of view levels.

Figure 2‒2: Example of view levels

Explanation

The view level of viewed table V1 is 1 because only base tables underlie V1.
The view level of viewed table V2 is 2 because a base table and a viewed table at view level 1 underlie V2.
The view level of viewed table V3 is 1 because only a base table underlies V3.
The view level of viewed table V4 is 3 because the underlying tables of V4 are at view levels 1 and 2.

Note

Dictionary tables and system tables are treated as viewed tables at view level 1.
Derived tables that are derived by table value constructors are treated the same as base tables.

To Page Top

(3) Invalidating viewed tables

If you perform any of the following operations for a base table or viewed table, all viewed tables that are dependent on the operation-target table are invalidated.

Using the DROP TABLE statement (without specifying drop behavior) to delete a base table

In this case, all viewed tables that were dependent on that table (deleted base table) are invalidated.
Using the DROP VIEW statement (without specifying drop behavior) to delete a viewed table

In this case, all viewed tables that were dependent on that table (deleted viewed table) are invalidated.
Using the ALTER TABLE statement to rename a column of a base table

In this case, all viewed tables that were dependent on that table (base table in which a column was renamed) are invalidated.
Using the ALTER TABLE statement to change a regular multi-chunk table into an archivable multi-chunk table

In this case, all viewed tables that were dependent on that table (table that was changed to an archivable multi-chunk table) are invalidated.
Using the ALTER TABLE statement to change an archivable multi-chunk table into a regular multi-chunk table

In this case, all viewed tables that were dependent on that table (table that was changed to a regular multi-chunk table) are invalidated.
Using the ALTER VIEW statement to re-create a viewed table

In this case, all viewed tables that were dependent on the viewed table that was re-created are invalidated.
Using the REVOKE statement to revoke the SELECT privilege for a table^#

In this case, all viewed tables that were dependent on that table (table for which the SELECT privilege was revoked) are invalidated.
#
- This is the case where both the SELECT privilege granted by specifying an authorization identifier and the SELECT privilege granted by specifying PUBLIC are revoked.
- Viewed tables might be invalidated when a SELECT privilege with the grant option is revoked, or only the grant option of a SELECT privilege is revoked. If the grant option used to grant the SELECT privilege to another HADB user is revoked, that SELECT privilege will also be revoked. If the table for which the SELECT privilege was revoked is an underlying table, the viewed tables that depend on that underlying table are invalidated. For specific examples, see Examples under Revoking access privileges in the manual HADB SQL Reference.
  
  For details about grant options, see (3) Granting access privileges under 2.7.5 Access privileges.

▪ What are invalidated viewed tables?

If a viewed table that can be accessed becomes inaccessible, we say that the viewed table was invalidated. If you want to make an invalidated viewed table accessible again, you must resolve the issue that caused the viewed table to be invalidated, and then use an ALTER VIEW statement to re-create the viewed table. Alternatively, after resolving the issue that caused the viewed table to be invalidated, you can use a DROP VIEW statement to delete the viewed table, and then redefine the viewed table using a CREATE VIEW statement.

▪ What are dependent viewed tables?

The viewed tables that are affected by the preceding seven operations are called dependent viewed tables. The following figure shows an example of dependent viewed tables.

Figure 2‒3: Example of dependent viewed tables

Explanation

Viewed tables V1, V2, and V4 are dependent on base table T1.
Viewed tables V1, V2, and V4 are dependent on base table T2.
Viewed tables V2 and V4 are dependent on base table T3.
Viewed tables V3 and V4 are dependent on base table T4.
Viewed tables V2 and V4 are dependent on viewed table V1.
Viewed table V4 is dependent on viewed table V2.
Viewed table V4 is dependent on viewed table V3.
There is no dependent viewed table for viewed table V4.

To Page Top