Introduction to DB2 materialized query tables and other common tables

Source: Internet
Author: User

This article mainly describes the DB2 materialized query table, and also introduces MQT, summary table (summary), and staging table. To better describe it, we use some practical examples to show how to create and use 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) for IBM®DB2®Universal Database™(UDB) for Linux, UNIX®And Windows®(DB2 UDB) users should be familiar with them. 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 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

  1. connect to sample   
  2. ...   
  3. alter table employee add unique (empno)   
  4. alter table department add unique (deptno)   
  5. create table emp as (select e.empno, e.firstnme, e.lastname, e.phoneno, d.deptno,   
  6. substr(d.deptname, 1, 12) as department, d.mgrno from employee e, department d   
  7. where e.workdept = d.deptno)   
  8. data initially deferred refresh immediate   
  9. set integrity for emp immediate checked not incremental   
  10. select * from emp   
  12. ------ ------------ --------------- ------- ------ ------------ ------   
  13. 000010 CHRISTINE HAAS 3978 A00 SPIFFY COMPU 000010   
  14. 000020 MICHAEL THOMPSON 3476 B01 PLANNING 000020   
  15. 000030 SALLY KWAN 4738 C01 INFORMATION 000030   
  16. 000050 JOHN GEYER 6789 E01 SUPPORT SERV 000050   
  17. 000060 IRVING STERN 6423 D11 MANUFACTURIN 000060   
  18. 000070 EVA PULASKI 7831 D21 ADMINISTRATI 000070   
  19. 000090 EILEEN HENDERSON 5498 E11 OPERATIONS 000090   
  20. 000100 THEODORE SPENSER 0972 E21 SOFTWARE SUP 000100   
  21. 000110 VINCENZO LUCCHESSI 3490 A00 SPIFFY COMPU 000010   
  22. 000120 SEAN O'CONNELL 2167 A00 SPIFFY COMPU 000010   
  23. 000130 DOLORES QUINTANA 4578 C01 INFORMATION 000030   
  24. ...   
  25. 000340 JASON GOUNOT 5698 E21 SOFTWARE SUP 000100   
  26. 32 record(s) selected.   
  27. connect reset  

The above content is an introduction to the DB2 materialized query table. I hope you will have some gains.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.