ADD MATERIALIZED QUERY materialized-query-definition

Changes a base table to a materialized query table. If the specified table is already a materialized query table or if the table is referenced in another materialized query table, an error is returned.

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 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
Specifies that the materialized query table can be used for query optimization.
DISABLE QUERY OPTIMIZATION
Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.