Introduction to materialized query tables

The definition of a materialized query table (MQT) is based on the results of one query. MQT can significantly improve query performance. This section describes MQT, summary table, and staging tables, and provides some practical examples to show how to create and use materialized query tables.

Materialized query table (MQT) is a table defined based on the results of one query. The data contained in the materialized query table comes from one or more tables based on the definition of the materialized query table. The summary table (also known as the automatic summary table, AST) should be familiar to users of IBM DB2 Universal Database (UDB) for Linux, UNIX and Windows (DB2 UDB, they can be considered 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 saves the query results as data. You can use the data in MQT instead of the data in the underlying table.

Materialized query tables can significantly improve 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 will be rewritten to use MQT.
MQT can be defined when a table is created, system-maintained MQT, or user-maintained MQT. The following sections describe these two types of mqts. In addition, we will introduce the summary table and staging table. The following example requires connecting to the SAMPLE database. If you have not created a SAMPLE database on your system, you can create the database by entering the db2sampl command at the command line prompt.

MQT maintained by the System
The data in this materialized query table 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. Whether it is refresh deferred or the MQT maintained by the refresh immediate system, the insert, update, or delete operations on them are not allowed. However, for the MQT maintained by the refresh immediate system, you can update the underlying table by modifying the underlying table (that is, the insert, update, or delete operation.

Listing 1 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 select list to be queried have at least one unique key, we first define a uniqueness constraint on the EMPNO column of the EMPLOYEE table, in addition, a uniqueness constraint is 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 an MQT is created, it cannot be queried until the set integrity statement is executed on it. The immediate checked clause specifies that data is CHECKED and refreshed Based on the queries used to define the MQT. The not incremental clause specifies that the integrity of the entire table is checked. By querying the EMP materialized query table, it is found that it is already filled with data.

Listing 1. Creating an MQT maintained by the System
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
Select * from emp
EMPNO FIRSTNME LASTNAME PHONENO DEPTNO DEPARTMENT MGRNO
----------------------------------------------------------------
000010 christine haas 3978 A00 spiffy compu 000010
000020 michael thompson 3476 B01 PLANNING 000020
000030 sally kwan 4738 C01 INFORMATION 000030
000050 john geyer 6789 E01 support serv 000050
000060 irving stern 6423 D11 MANUFACTURIN 000060
000070 eva pulaski 7831 D21 ADMINISTRATI 000070
000090 eileen henderson 5498 E11 OPERATIONS 000090
000100 theodore spenser 0972 E21 software sup 000100
000110 vincenzo lucchessi 3490 A00 spiffy compu 000010
000120 SEAN o'connell 2167 A00 spiffy compu 000010
000130 dolores quintana 4578 C01 INFORMATION 000030
...
000340 jason gounot 5698 E21 software sup 000100
32 record (s) selected.
Connect reset

User-maintained MQT
The data in this materialized query table is maintained by the user. Only the refresh deferred materialized query table can be defined as maintained by user. Do not issue a refresh table statement (MQT for system maintenance) to the MQT maintained by the user ). However, user-maintained mqts allow insert, update, or delete operations on them.

Listing 2 shows an example of an MQT maintained by a user who creates the refresh deferred type. The name of this table is ONTARIO_1995_SALES_TEAM, which is based on the underlying tables EMPLOYEE and SALES in the database SAMPLE. Similarly, the data initially deferred clause means that the DATA is not inserted into the TABLE when the create table statement is executed. After an MQT is created, it is in the check pending state and cannot be queried until the set integrity statement is executed on it. The materialized query immediate unchecked clause specifies that the table will enable integrity check, but does not have to check whether it violates integrity constraints, so that it can be detached from the pending status of the check.

Next, in order to fill in the data to MQT, We will import the data exported from the EMPLOYEE and SALES tables. The query used to export data is consistent with the query used to define MQT. Then, we insert another record to the ONTARIO_1995_SALES_TEAM table.

By querying the ONTARIO_1995_SALES_TEAM materialized query table, it is found that it has already filled in the imported and inserted data, which indicates that the MQT maintained by the user can indeed be directly modified.

List 2. Create an MQT maintained by the user
Connect to sample
...
Create table ontario_1995_sales_team as (select distinct e. empno, e. firstnme,
E. lastname, e. workdept, e. phoneno, 'ontario 'as region,
Year (s. sales_date) as year from employee e, sales s
Where e. lastname = s. sales_person and year (s. sales_date) = 1995
And left (s. region, 3) = 'ont ')
Data initially deferred refresh deferred maintained by user
Set integrity for ontario_1995_sales_team materialized query immediate
Unchecked
Export to ontario_1995_sales_team.del of del
Select distinct e. empno, e. firstnme, e. lastname, e. workdept, e. phoneno,
'Ontario 'as region, year (s. sales_date) as year from employee e,
Sales s
Where e. lastname = s. sales_person and year (s. sales_date) = 1995
And left (s. region, 3) = 'ont'
...
Number of rows exported: 2
Import from ontario_1995_sales_team.del of del insert
Ontario_1995_sales_team
...
Number of rows committed = 2
Insert into ontario_1995_sales_team
Values ('123', 'russ', 'dyers', 'd44', '123', 'ontario ', 006900)
Select * from ontario_1995_sales_team
EMPNO FIRSTNME LASTNAME WORKDEPT PHONENO REGION YEAR
------------------------------------------------------------------
000110 vincenzo lucchessi A00 3490 Ontario 1995
000330 wing lee E21 2103 Ontario 1995
006900 russ dyers D44 1234 Ontario 1995
3 record (s) selected.
Connect reset

The definition of a materialized query table (MQT) is based on the results of one query. MQT can significantly improve query performance. This section describes MQT, summary table, and staging tables, and provides some practical examples to show how to create and use materialized query tables.

Materialized query table (MQT) is a table defined based on the results of one query. The data contained in the materialized query table comes from one or more tables based on the definition of the materialized query table. The summary table (also known as the automatic summary table, AST) should be familiar to users of IBM DB2 Universal Database (UDB) for Linux, UNIX and Windows (DB2 UDB, they can be considered 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 saves the query results as data. You can use the data in MQT instead of the data in the underlying table.

Materialized query tables can significantly improve 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 will be rewritten to use MQT.
MQT can be defined when a table is created, system-maintained MQT, or user-maintained MQT. The following sections describe these two types of mqts. In addition, we will introduce the summary table and staging table. The following example requires connecting to the SAMPLE database. If you have not created a SAMPLE database on your system, you can create the database by entering the db2sampl command at the command line prompt.

MQT maintained by the System
The data in this materialized query table 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. Whether it is refresh deferred or the MQT maintained by the refresh immediate system, the insert, update, or delete operations on them are not allowed. However, for the MQT maintained by the refresh immediate system, you can update the underlying table by modifying the underlying table (that is, the insert, update, or delete operation.

Listing 1 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 select list to be queried have at least one unique key, we first define a uniqueness constraint on the EMPNO column of the EMPLOYEE table, in addition, a uniqueness constraint is 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 is in the check pending status (see DB2 basics: clarifying the status of the table and tablespace). Before executing the set integrity Statement on it, it cannot be queried. The immediate checked clause specifies that data is CHECKED and refreshed Based on the queries used to define the MQT. The not incremental clause specifies that the integrity of the entire table is checked. By querying the EMP materialized query table, it is found that it is already filled with data.

Listing 1. Creating an MQT maintained by the System
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
Select * from emp
EMPNO FIRSTNME LASTNAME PHONENO DEPTNO DEPARTMENT MGRNO
----------------------------------------------------------------
000010 christine haas 3978 A00 spiffy compu 000010
000020 michael thompson 3476 B01 PLANNING 000020
000030 sally kwan 4738 C01 INFORMATION 000030
000050 john geyer 6789 E01 support serv 000050
000060 irving stern 6423 D11 MANUFACTURIN 000060
000070 eva pulaski 7831 D21 ADMINISTRATI 000070
000090 eileen henderson 5498 E11 OPERATIONS 000090
000100 theodore spenser 0972 E21 software sup 000100
000110 vincenzo lucchessi 3490 A00 spiffy compu 000010
000120 SEAN o'connell 2167 A00 spiffy compu 000010
000130 dolores quintana 4578 C01 INFORMATION 000030
...
000340 jason gounot 5698 E21 software sup 000100
32 record (s) selected.
Connect reset

User-maintained MQT
The data in this materialized query table is maintained by the user. Only the refresh deferred materialized query table can be defined as maintained by user. Do not issue a refresh table statement (MQT for system maintenance) to the MQT maintained by the user ). However, user-maintained mqts allow insert, update, or delete operations on them.

Listing 2 shows an example of an MQT maintained by a user who creates the refresh deferred type. The name of this table is ONTARIO_1995_SALES_TEAM, which is based on the underlying tables EMPLOYEE and SALES in the database SAMPLE. Similarly, the data initially deferred clause means that the DATA is not inserted into the TABLE when the create table statement is executed. After an MQT is created, it is in the check pending state (see DB2 basics: clarifying the status of tables and tablespaces) and cannot be queried until the set integrity statement is executed on it. The materialized query immediate unchecked clause specifies that the table will enable integrity check, but does not have to check whether it violates integrity constraints, so that it can be detached from the pending status of the check.

Next, in order to fill in the data to MQT, We will import the data exported from the EMPLOYEE and SALES tables. The query used to export data is consistent with the query used to define MQT. Then, we insert another record to the ONTARIO_1995_SALES_TEAM table.

By querying the ONTARIO_1995_SALES_TEAM materialized query table, it is found that it has already filled in the imported and inserted data, which indicates that the MQT maintained by the user can indeed be directly modified.

List 2. Create an MQT maintained by the user
Connect to sample
...
Create table ontario_1995_sales_team as (select distinct e. empno, e. firstnme,
E. lastname, e. workdept, e. phoneno, 'ontario 'as region,
Year (s. sales_date) as year from employee e, sales s
Where e. lastname = s. sales_person and year (s. sales_date) = 1995
And left (s. region, 3) = 'ont ')
Data initially deferred refresh deferred maintained by user
Set integrity for ontario_1995_sales_team materialized query immediate
Unchecked
Export to ontario_1995_sales_team.del of del
Select distinct e. empno, e. firstnme, e. lastname, e. workdept, e. phoneno,
'Ontario 'as region, year (s. sales_date) as year from employee e,
Sales s
Where e. lastname = s. sales_person and year (s. sales_date) = 1995
And left (s. region, 3) = 'ont'
...
Number of rows exported: 2
Import from ontario_1995_sales_team.del of del insert
Ontario_1995_sales_team
...
Number of rows committed = 2
Insert into ontario_1995_sales_team
Values ('123', 'russ', 'dyers', 'd44', '123', 'ontario ', 006900)
Select * from ontario_1995_sales_team
EMPNO FIRSTNME LASTNAME WORKDEPT PHONENO REGION YEAR
------------------------------------------------------------------
000110 vincenzo lucchessi A00 3490 Ontario 1995
000330 wing lee E21 2103 Ontario 1995
006900 russ dyers D44 1234 Ontario 1995
3 record (s) selected.
Connect reset

Summary table
You should remember that the summary table is a special type of MQT. Their fullselect contains a group by clause, which summarizes the data in the table referenced in fullselect. Listing 3 shows an example of creating a summary table. The table name is SALES_SUMMARY, which is based on the underlying table SALES in the SAMPLE database. Similarly, the data initially deferred clause means that the DATA is not inserted into the TABLE when the create table statement is executed. The refresh deferred clause indicates that you can use the refresh table statement to REFRESH the data in the TABLE at any time. When this MQT has just been created and no refresh table statement has been issued, an error is returned for its query. After the refresh table statement is executed, the query can be run successfully.

After performing the insert operation on the SALES table and refresh the summary table, the query on the summary table shows that the changes to the underlying table have been reflected in the summary table: sales of salesman Lee in Ontario-South increased by 100. Similarly, you can observe the changes in the summary table after performing the update or delete operation on the underlying table.

Listing 3. Creating a summary table
Connect to sample
...
Create table sales_summary as (select sales_person, region, sum (sales)
As total_sales
From sales group by sales_person, region)
Data initially deferred refresh deferred
Select * from sales_summary
SALES_PERSON REGION TOTAL_SALES
-----------------------------------------
SQL0668N Operation not allowed for reason code "1" on table
"MELNYK. SALES_SUMMARY". SQLSTATE = 57016
Refresh table sales_summary
Select * from sales_summary
SALES_PERSON REGION TOTAL_SALES
-----------------------------------------
GOUNOT Manitoba 15
GOUNOT Ontario-North 1
GOUNOT Ontario-South 10
GOUNOT Quebec 24
LEE Manitoba 23
LEE Ontario-North 8
LEE Ontario-South 34
LEE Quebec 26
LUCCHESSI Manitoba 3
LUCCHESSI Ontario-South 8
LUCCHESSI Quebec 3
11 record (s) selected.
Insert into sales values ('2014/1/123', 'lil', 'ontario-South', 06/28)
Refresh table sales_summary
Select * from sales_summary
SALES_PERSON REGION TOTAL_SALES
-----------------------------------------
...
LEE Ontario-North 8
LEE Ontario-South 1, 134
LEE Quebec 26
...
11 record (s) selected.
Update sales set sales = 50 where sales_date = '2014/1/123' and
Sales_person = 'lil'
And region = 'ontario-South'
Refresh table sales_summary
Select * from sales_summary
SALES_PERSON REGION TOTAL_SALES
-----------------------------------------
...
LEE Ontario-North 8
LEE Ontario-South 84
LEE Quebec 26
...
11 record (s) selected.
Delete from sales where sales_date = '2014/1/123' and sales_person = 'lil'
And region = 'ontario-South'
Refresh table sales_summary
Select * from sales_summary
SALES_PERSON REGION TOTAL_SALES
-----------------------------------------
...
LEE Ontario-North 8
LEE Ontario-South 34
LEE Quebec 26
...
11 record (s) selected.
Connect reset

Staging table
If refresh deferred mqt has an associated staging table, you can perform incremental REFRESH on it. The staging table collects changes to apply these changes so that the MQT is synchronized with its underlying table. You can use the create table statement to CREATE a staging TABLE. Then, when the underlying table of the mqt is modified, the changes will be transmitted and immediately added to the staging table. The idea is to use the staging table to incrementally refresh the MQT, instead of re-generating the MQT from the beginning. Incremental Maintenance can significantly improve performance. When the refresh operation is complete, the staging table is deleted.

After the staging table is created, it is suspended (inconsistent. It must be detached from this state before it starts to collect changes to the underlying table. Therefore, you can use the set integrity statement.

Listing 4 shows an example of using a staging table with an associated summary table. The summary table is named EMP_SUMMARY, which is based on the underlying table 'employe' in the SAMPLE database. You should remember that the data initially deferred clause does not insert DATA into the TABLE when executing the create table statement. The refresh deferred clause means that you can use the refresh table statement to REFRESH the data in the TABLE at any time. The name of the staging table is EMP_SUMMARY_S, which is associated with the summary table EMP_SUMMARY. The propagate immediate clause specifies that any changes made to the underlying table in the insert, update, or delete operation will be accumulated in the staging table. For both tables, the set integrity statement is issued to remove them from the suspension state.

As expected, no data is returned for the query of the summary table. The refresh table statement returns a warning indicating "integrity of non-incremental data remains unverified ". This is not surprising. When you query the summary table again, no data is returned. However, when we Insert a new row of data to the underlying EMPLOYEE table, we query the staging table EMP_SUMMARY_S again and return a row of records, which is consistent with the data just inserted. In the staging table, three columns are the same as those in the underlying summary table, and two other columns are used by the system: GLOBALTRANSID (the Global Transaction ID corresponding to each row to be propagated) and GLOBALTRANSTIME (the timestamp of the transaction ). When the summary TABLE is queried again, no data is returned. However, after the refresh table statement is executed, the query runs successfully.

Listing 4. Using the staging table with an associated summary table
Connect to sample
...
Create table emp_summary as (select workdept, job, count (*) as count
From employee group by workdept, job)
Data initially deferred refresh deferred
Create table emp_summary_s for emp_summary propagate immediate
Set integrity for emp_summary materialized query immediate unchecked
Set integrity for emp_summary_s staging immediate unchecked
Select * from emp_summary
WORKDEPT JOB COUNT
---------------------------
0 record (s) selected.
Refresh table emp_summary
SQL1594W Integrity of non-incremental data remains unverified by
Database manager. SQLSTATE = 01636
Select * from emp_summary
WORKDEPT JOB COUNT
---------------------------
0 record (s) selected.
Insert into employee
Values ('123', 'russ', 'l', 'dyers', 'd44', '123', '2017 ',
'Fieldup', 5, 'M', '2017-04-02 ', 1940 0)
Select * from emp_summary_s
WORKDEPT JOB COUNT GLOBALTRANSID GLOBALTRANSTIME
----------------------------------------------...-----------------------------...
D44 FIELDREP 1 x '00000000000000cd' x '000000'
1 record (s) selected.
Select * from emp_summary
WORKDEPT JOB COUNT
---------------------------
0 record (s) selected.
Refresh table emp_summary
SQL1594W Integrity of non-incremental data remains unverified by the database
Manager. SQLSTATE = 01636
Select * from emp_summary
WORKDEPT JOB COUNT
---------------------------
D44 FIELDREP 1
1 record (s) selected.
Connect reset

Conclusion
In the SYSCAT. TABDEP system catalog view, each dependency of a materialized query table on other objects has a corresponding row. You can query this view to get a Summary of the dependencies of the created MQT (listing 5. MQT has a DTYPE with the value 'S. The TABNAME column lists the MQT names, and the BNAME column lists the names of the database objects on which the corresponding MQT depends. The BTYPE column displays the object type: 'T' indicates the table, 'I' indicates the index, and 'F' indicates the function instance.

Listing 5. query the SYSCAT. TABDEP system catalog view to view the dependencies of MQT on other database objects.
Connect to sample
...
Select substr (tabname, 1, 24) as tabname, dtype, substr (bname, 1, 24) as bname, btype
From syscat. tabdep where tabschema = 'melnyk' and dtype = 'S'
TABNAME DTYPE BNAME BTYPE
----------------------------------------------------------
EMP S DEPARTMENT T
EMP S EMPLOYEE T
Emp s SQL050829104058970 I
Emp s SQL050829104058800 I
EMP_SUMMARY S EMPLOYEE T
ONTARIO_1995_SALES_TEAM S LEFT1 F
ONTARIO_1995_SALES_TEAM S SALES T
ONTARIO_1995_SALES_TEAM S EMPLOYEE T
SALES_SUMMARY S SALES T
9 record (s) selected.
Connect reset