DB2 materialized view (materialized Query Tables, ()

Materialized view of DB2 a table based on the definition of a query result, which contains data from one or more tables on which the definition of the base is based, the use of which can significantly improve the operational performance of the query.

Both the view and the base of the database are defined based on a single query. The query on which the view is based is run whenever the view is referenced. But it actually stores the results of the query as data, and you can use that data from the base, rather than using the data from the underlying table.

It can significantly improve the performance of queries, especially the performance of complex queries. If the optimizer determines that a part of a query or query can be resolved with a single address, then the query can be rewritten to take advantage of the. It can be defined when a table is created, can be defined as being maintained by the system, or it can be defined as being maintained by the user.

data initially DEFERRED clause means that the data is not inserted into the table when the CREATE table statement is executed.
MQT is created, it is in the check pending state and cannot be queried until the SET INTEGRITY statement is executed against it. The IMMEDIATE CHECKED clause specifies that the data must be checked against the query used to define it, and the data is refreshed. The NOT INCREMENTAL clause specifies an integrity check for the entire table.

The data in this case is maintained by the system. When creating this type of IMMEDIATE, you can specify whether the table data is refresh or refresh DEFERRED. The REFRESH keyword allows you to specify how the data is maintained. DEFERRED means that the data in the table can be refreshed at any time by using the Refresh Table statement.

System-maintained DEFERRED, whether for refresh type or refresh IMMEDIATE type, are not allowed for INSERT, UPDATE, or delete operations. However, for system maintenance of the REFRESH IMMEDIATE type, it can be updated by changes to the underlying table (that is, insert, UPDATE, or delete operations).

Let's look at an example

First create the original table

CREATE TABLE T (   ID                INTEGER not  NULL,   COL1              varchar),   COL2              varchar ($),   COL3              VARCHAR (+),   COL4              varchar (+),   COL5              varchar,   PRIMARY KEY (ID)) ORGANIZE by ROW;

Create a 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 (IDs, 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 ');

Query the original table and the table, and you'll find data in the list.

SELECT * FROM T;select * from T_MQT;

Original link: DB2 materialized view (materialized Query Tables, the

DB2 materialized view (materialized Query Tables, ()