ALTER MATERIALIZED QUERY statement
The ALTER MATERIALIZED QUERY statement alters the definition of a materialized query table (MQT).
This statement is an executable statement that cannot be dynamically prepared.
The privileges that are held by the authorization ID of the statement must include at least one of the following authorities:
- ALTER privilege on the table to be altered
- CONTROL privilege on the table to be altered
- ALTERIN privilege on the schema of the table
- DBADM authority
- Specifies the name of the materialized query table whose definition is to be altered.
- ENABLE QUERY OPTIMIZATION
- The materialized query table can be used for query optimization. This is the default.
- DISABLE QUERY OPTIMIZATION
- The materialized query table is not used for query optimization. The table can still be queried directly.
A new user-maintained MQT is an empty table that must be populated with data. But before this can occur, query optimization must be disabled to avoid wrong results. Moreover, query optimization needs to be disabled during MQT maintenance windows.
If you alter the table on which an MQT is based, such as drop a column or change a column type, you might need to drop and recreate that MQT. These user-maintained MQTs are not automatically maintained by the system.
CREATE HADOOP TABLE mqt1 AS ( SELECT * FROM t1 WHERE c1 > 10) DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER DISABLE QUERY OPTIMIZATION; INSERT INTO mqt1 ( SELECT * FROM t1 WHERE c1 > 10); ALTER MATERIALIZED QUERY mqt1 SET ENABLE QUERY OPTIMIZATION; SET CURRENT REFRESH AGE ANY; SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION USER; SELECT * FROM t1 WHERE c1 > 10;