ALTER MATERIALIZED QUERY statement

The ALTER MATERIALIZED QUERY statement alters the definition of a materialized query table (MQT).

Invocation

This statement is an executable statement that cannot be dynamically prepared.

Authorization

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

Syntax

Read syntax diagramSkip visual syntax diagram ALTER MATERIALIZED QUERY table-name SET ENABLE QUERY OPTIMIZATIONDISABLE QUERY OPTIMIZATION

Description

table-name
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.

Usage notes

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.

Examples


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;