Application of MQT materialized query table in DB2 Materialized View

Today, we will mainly discuss the correct use of MQT materialized query tables in the DB2 materialized view, the following is a detailed description of the main content of the correct use of the MQT materialized query table in the materialized view of the DB2 database.

The MQT definition is based on the query results. MQT can significantly improve the 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.

Use MQT to Materialized Query Tables (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. Summary tables (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, the insert, update, or delete operation.

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 must have at least one unique key 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 DB2 materialized view 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.

 
 

  
  
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 DB2.