A materialized query table is a table whose definition is based on the result of a query. As such, the materialized query table typically contains pre-computed results based on the data existing in the table or tables that its definition is based on. If the SQL compiler determines that a query will run more
efficiently against a materialized query table than the base table or tables, the query executes against the materialized query table, and you obtain the result faster than you otherwise would.
Restrictions:
If you create a user-maintained materialized query table, the restrictions associated with a system-maintained materialized query table still apply but with the following exceptions:
v INSERT, UPDATE, and DELETE operations are allowed on the materialized query table. However, no validity checking is done against the underlying base tables. You are responsible for the correctness of the data.
v LOAD, EXPORT, IMPORT, and data replication will work with this type of materialized query table except there is no validity checking.
v You are not allowed to use the REFRESH TABLE statement on this type of materialized query table.
v You are not allowed to use the SET INTEGRITY ... IMMEDIATE CHECKED statement on this type of materialized query table.
v User-maintained materialized query tables must be defined as REFRESH DEFERRED.
Materialized query tables defined with REFRESH DEFERRED are not used to optimize static SQL.
Setting the CURRENT REFRESH AGE special register to a value other than zero should be done with caution. By allowing a materialized query table that may not represent the values of the underlying base table to be used to optimize the processing of the query, the result of the query may not
accurately represent the data in the underlying table. This may be reasonable when you know the underlying data has not changed, or you are willing to accept the degree of error in the results based on your knowledge of the data.
If you want to create a new base table that is based on any valid fullselect, specify the DEFINITION ONLY keyword when you create the table. When the create table operation completes, the new table is not treated as a materialized query table, but rather as a base table. For example, you can create the
exception tables used in LOAD and SET INTEGRITY as follows:
CREATE TABLE XT AS
(SELECT T.*, CURRENT TIMESTAMP AS TIMESTAMP,CLOB(",32K) AS MSG FROM T) DEFINITION ONLY
Here are some of the key restrictions regarding materialized query tables:
1. You cannot alter a materialized query table.
2. You cannot alter the length of a column for a base table if that table has a materialized query table.
3. You cannot import data into a materialized query table.
4. You cannot create a unique index on a materialized query table.
5. You cannot create a materialized query table based on the result of a query that references one or more nicknames.
Procedure:
The creation of a materialized query table with the replication option can be used to replicate tables across all nodes in a partitioned database
environment. These are known as “replicated materialized query tables”.
In general a materialized query table, or a replicated materialized query table, is used for optimization of a query if the isolation level of the materialized query table, or the replicated materialized query table, is higher than or equal to the isolation level of the query. For example, if a query is running under the cursor stability (CS) isolation level, only materialized query tables, and replicated materialized query tables, that are defined under CS or higher isolation levels are used for optimization.
To create a materialized query table, you use the CREATE TABLE statement with the AS fullselect clause and the IMMEDIATE or REFRESH DEFERRED options.
You have the option of uniquely identifying the names of the columns of the materialized query table. The list of column names must contain as many names as there are columns in the result table of the full select. A list of column names must be given if the result table of the full select has duplicate column names or has an unnamed column. An unnamed column is derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list. If a list of column names is not specified, the columns of the table inherit the names of the columns of the result set of the full select.
In large database environments, or data warehouse environments, there are often custom applications that maintain and load user-maintained
materialized query tables. When creating a materialized query table, you have the option of specifying whether the system will maintain the materialized query table or the user will maintain the materialized query table. The default is system-maintained, which can be explicitly specified using the
MAINTAINED BY SYSTEM clause. User-maintained materialized query tables are specified using the MAINTAINED BY USER clause.
If you create a system-maintained materialized query table, you have a further option of specifying whether the materialized query table is refreshed
automatically when the base table is changed, or whether it is refreshed by using the REFRESH TABLE statement. To have the materialized query table refreshed automatically when changes are made to the base table or tables, specify the REFRESH IMMEDIATE keyword. An immediate refresh is useful when:
v Your queries need to ensure the data they access is the most current v The base table or tables are infrequently changed
v The refresh is not expensive.
The materialized query table, in this situation, can provide pre-computed results. If you want the refresh of the materialized query table to be deferred, specify the REFRESH DEFERRED keyword. materialized query tables
specified with REFRESH DEFERRED will not reflect changes to the
underlying base tables. You should use materialized query tables where this is not a requirement. For example, if you run DSS queries, you would use the materialized query table to contain legacy data.
A materialized query table defined with REFRESH DEFERRED may be used in place of a query when it:
v Conforms to the restrictions for a fullselect of a refresh immediate summary table, except:
– The SELECT list is not required to include COUNT(*) or COUNT_BIG(*) – The SELECT list can include MAX and MIN column functions
– A HAVING clause is allowed.
You use the CURRENT REFRESH AGE special register to specify the amount of time that the materialized query table defined with REFRESH DEFERRED can be used for a dynamic query before it must be refreshed. To set the value of the CURRENT REFRESH AGE special register, you can use the SET CURRENT REFRESH AGE statement.
The CURRENT REFRESH AGE special register can be set to ANY, or a value of 99999999999999, to allow deferred materialized queries to be used in a dynamic query. The collection of nines is the maximum value allowed in this special register which is a timestamp duration value with a data type of DECIMAL(20,6). A value of zero (0) indicates that only materialized query tables defined with REFRESH IMMEDIATE may be used to optimize the processing of a query. In such a case, materialized query tables defined with REFRESH DEFERRED are not used for optimization.
Materialized query tables defined with REFRESH IMMEDIATE are applicable to both static and dynamic queries and do not need to use the CURRENT REFRESH AGE special register.
Materialized query tables have queries routed to them when the table has been defined using the ENABLE QUERY OPTIMIZATION clause, and, if a deferred materialized query table, the CURRENT REFRESH AGE special register has been set to ANY. However, with user-maintained materialized query tables, the use of the CURRENT REFRESH AGE special register is not the best method to control the rerouting of queries. The CURRENT
MAINTAINED TABLE TYPES FOR OPTIMIZATION special register will indicate which kind of cached data will be available for routing.
With activity affecting the source data, a materialized query table over time will no longer contain accurate data. You will need to use the REFRESH TABLE statement.
Related concepts:
v “Isolation levels” in the SQL Reference, Volume 1 Related tasks:
v “Altering materialized query table properties” on page 203 v “Refreshing the data in a materialized query table” on page 204 v “Dropping a materialized query or staging table” on page 214 Related reference:
v “CREATE TABLE statement” in the SQL Reference, Volume 2 v “REFRESH TABLE statement” in the SQL Reference, Volume 2
v “SET CURRENT REFRESH AGE statement” in the SQL Reference, Volume 2 v “CURRENT REFRESH AGE special register” in the SQL Reference, Volume 1 v “CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special
register” in the SQL Reference, Volume 1
v “SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement” in the SQL Reference, Volume 2