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 number of columns in the table must be the same as the number of result
columns in the select-statement.
- The column attributes of each column of the table must be compatible to
the column attributes of the corresponding result column in the select-statement.
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.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.