DB2 Materialized View (Materialized Query Tables, MQT), materializedmqt

DB2 Materialized View (Materialized Query Tables, MQT), materializedmqt

The Materialized View of DB2 MQT is a table defined based on the query results. The data contained in the MQT comes from one or more tables based on the MQT definition, using MQT can significantly improve the query operation performance.

Both the database view and MQT are defined based on a query. When a view is referenced, the view-Based Query runs. However, MQT stores the query results as data. You can use the data in MQT instead of the data in the underlying table.

MQT can significantly improve the query performance, especially the performance of complex queries. If the optimizer determines that a part of the query can be solved using an MQT, the query can be overwritten to use MQT. MQT can be defined when a table is created. It can be defined by the system or by the user.

The data initially deferred clause means that the DATA is not inserted into the TABLE when the create table statement is executed.
After the MQT is created, it will be in the check pending status. It cannot be queried before the set integrity statement is executed on it. The immediate checked clause specifies that the data must be CHECKED and refreshed Based on the queries used to define the MQT. The not incremental clause specifies the integrity check for the entire table.

The data in this MQT is maintained by the system. When creating this type of MQT, you can specify whether the table data is refresh immediate or refresh deferred. You can use the REFRESH keyword to specify how to maintain data. DEFERRED means that the data in the TABLE can be refreshed at any time through the refresh table statement.

The MQT maintained by the system, whether it is refresh deferred or refresh immediate, cannot be inserted, updated, or deleted. However, for the refresh immediate-type MQT maintained by the system, you can update it by modifying the underlying table (that is, the insert, update, or delete operation.

The following is an example.

First, create the original table.

CREATE TABLE T (   ID                INTEGER  NOT NULL,   COL1              VARCHAR(128),   COL2              VARCHAR(128),   COL3              VARCHAR(128),   COL4              VARCHAR(128),   COL5              VARCHAR(128),   PRIMARY KEY (ID)) ORGANIZE BY ROW;

Create an MQT table

CREATE TABLE T_MQT ( ID, COL1, COL2, COL3 ) AS ( select ID, COL1, COL2, COL3  from T ) DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM;SET INTEGRITY FOR T_MQT IMMEDIATE CHECKED FULL ACCESS;

Write Data to the original table

insert into T(ID, COL1, COL2, COL3, COL4, COL5) values (1, 'col1', 'col2', 'col3', 'col4', 'col5');insert into T(ID, COL1, COL2, COL3, COL4, COL5) values (2, 'col1', 'col2', 'col3', 'col4', 'col5');

When querying the original table and MQT table, you will find data in the MQT table.

select * from T;select * from T_MQT;

Original article: DB2 Materialized View (Materialized Query Tables, MQT)