In the materialized view of DB2 database, how does one operate the MQT materialized query table?

The following articles mainly describe the correct use of the MQT materialized query table in the materialized view of the DB2 database. If you encounter the application of the MQT materialized query table in the materialized view of the DB2 database in actual operations, however, you do not know how to solve the problem correctly, so the following articles must be good teachers and friends for you.

Use MQT to Materialized the Query table Materialized Query Tables, MQT)

The MQT definition is based on the query results. MQT can significantly improve query performance. This tutorial will introduce MQT, summary table) and staging tables, and show how to create and use materialized query tables through some practical examples.

MQT is a table defined based on the query results. The data contained in MQT comes from one or more tables defined by MQT. The summary table, also known as the automatic summary table [AST]) should be familiar to IBM DB2 for Linux, UNIX, and Windows users. They can be seen as special mqts. Fullselect is part of the summary table definition. It contains a group by clause that summarizes the data in the table referenced in fullselect.

You can regard MQT as a materialized view. Both the view and MQT are defined based on a query. When a view is referenced, the view-Based Query runs. However, MQT actually 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 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, inserting, updating, or deleting the table.

The following section shows an example of creating an MQT maintained by a refresh immediate system. The table name is EMP, which is based on the underlying tables of the SAMPLE database, namely, EMPLOYEE and DEPARTMENT. Because refresh immediate mqt requires that each table referenced in the query have at least one unique DB2 database key to appear in the select list, we first define a uniqueness constraint on the EMPNO column of the EMPLOYEE table, in addition, a uniqueness constraint must be defined in the DEPTNO column of the DEPARTMENT table.

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. You cannot query it before executing the set integrity Statement 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.

 
 

  
  
CONNECT TO SAMPLE  
  
  
...  
  
  
ALTER TABLE EMPLOYEE ADD UNIQUE (EMPNO)  
  
  
ALTER TABLE DEPARTMENT ADD UNIQUE (DEPTNO)  
  
  
CREATE TABLE EMP AS (SELECT E.EMPNO, E.FIRSTNME, E.LASTNAME, E.PHONENO, D.DEPTNO,  
  
  
SUBSTR(D.DEPTNAME, 1, 12) AS DEPARTMENT, D.MGRNO FROM EMPLOYEE E, DEPARTMENT D  
  
  
WHERE E.WORKDEPT = D.DEPTNO)  
  
  
DATA INITIALLY DEFERRED REFRESH IMMEDIATE  
  
  
SET INTEGRITY FOR EMP IMMEDIATE CHECKED NOT INCREMENTAL   
 
 

The above content is an introduction to the usage of the MQT materialized query table in the materialized view of the DB2 database.