11.2.9 Deleting invalidated viewed tables
If invalidated viewed tables are unnecessary, delete the viewed tables according to the following procedure.
- Example:
-
Of the viewed tables defined by HADB user ADBUSER01, this example deletes the invalidated viewed tables.
Procedure:
-
Acquire a list of viewed tables that have been invalidated.
By retrieving data from a dictionary table, you can acquire a list of viewed tables that have been invalidated. For an example of retrieving data from a dictionary table, see Example 2 in 11.2.7 Checking whether a viewed table has been invalidated.
-
Use the DROP VIEW statement to delete the invalidated viewed tables.
DROP VIEW "CUSTOMER30s" DROP VIEW "CUSTOMER40s" DROP VIEW "CUSTOMER50s"
Execute the DROP VIEW statement for all viewed tables that have been invalidated.
- ▪ When there are many viewed tables to be deleted
-
If you delete the viewed table whose view level is the smallest, all invalidated viewed tables that depend on the deleted viewed table are deleted in a batch.
Example:
DROP VIEW "CUSTOMER30s" CASCADE
If you specify CASCADE for drop behavior as in the preceding example, you can delete all viewed tables that depend on CUSTOMER30s (all viewed tables that have been invalidated).
To check the viewed table whose view level is the smallest among the viewed tables that have been invalidated, execute the following SELECT statement. For the underlined part, specify the schema name.
SELECT "TABLE_NAME","VIEW_LEVEL" FROM "MASTER"."SQL_VIEWS" WHERE "TABLE_SCHEMA"='ADBUSER01' AND IS_INVALID = 'Y' ORDER BY "VIEW_LEVEL"Retrieval result
TABLE_NAME VIEW_LEVEL ----------- ---------- CUSTOMER30s 3 CUSTOMER40s 4 : :The viewed table (CUSTOMER30s) displayed on the first line of the retrieval result is the viewed table whose view level is the smallest.