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 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 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.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.