ALTER MATERIALIZED QUERY materialized-query-table-alteration

Changes the attributes of a materialized query table. The table-name must identify a materialized query table.

select-statement
Defines the query on which the table is based. The columns of the existing table must meet the following characteristics:

The select-statement for a materialized query table must not contain a reference to the table being altered, a view over the table being altered, or another materialized query table. For additional details about specifying select-statement for a materialized query table, see CREATE TABLE.

refreshable-table-options
Specifies the materialized query table options for altering a base table to a materialized query table.
DATA INITIALLY DEFERRED
Specifies that the data in the table is not refreshed or validated as part of the ALTER TABLE statement. A REFRESH TABLE statement can be used to make sure the data in the materialized query table is the same as the result of the query in which the table is based.
DATA INITIALLY IMMEDIATE
Specifies that the data is inserted in the table from the result of the query as part of processing the ALTER TABLE statement.
REFRESH DEFERRED
Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.
MAINTAINED BY USER
Specifies that the materialized query table is maintained by the user. The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the table.
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether this materialized query table can be used for query optimization.
ENABLE QUERY OPTIMIZATION
The materialized query table can be used for query optimization.
DISABLE QUERY OPTIMIZATION
The materialized query table will not be used for query optimization. The table can still be queried directly.
SET refreshable-table-alteration
Changes how the table is maintained or whether the table can be used in query optimization.
MAINTAINED BY USER
Specifies that the materialized query table is maintained by the user. The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the table.
REFRESH DEFERRED
Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether this materialized query table can be used for query optimization.
ENABLE QUERY OPTIMIZATION
The materialized query table can be used for query optimization.
DISABLE QUERY OPTIMIZATION
The materialized query table will not be used for query optimization. The table can still be queried directly.