DB2 materialized query table

---- Start

The DB2 materialized query table MQT (materialized query tables) stores the results of a query. When we query related tables, DB2 automatically determines whether to use the original table or materialized query table. When the database contains massive data, using materialized query tables can greatly improve the query speed. However, one advantage is that the maintenance of materialized query tables is quite time-consuming. Therefore, materialized query tables are widely used in data warehouses and massive report queries. These queries are characterized by large data volumes, frequent grouping statistics, and frequent data changes. Because of these characteristics, materialized query tables in these cases can give full play to their advantages.

Syntax: <br/> Create Table <Table-Name> as <br/> <select shorttement> <br/> data initially deferred <br/> refresh [deferred | immediate] <br/> [enable qurey optimization | disable qurey optimization] <br/> [maintained by [system | user | federated_toool] </P> <p> example: <br/> Create Table emp_summary as <br/> (<br/> select <br/> workdept <br/>, count (*) as crows <br/>, sum (empno) as sumno <br/> from <br/> employee <br/> group by workdept <br/>) <br/> data initially deferred <br/> refresh immediate; 

After the materialized query table is defined, if we execute the following SQL statements, the DB2 optimizer will use MQT

Select workdept, AVG (empno) from employee group by workdept

The DB2 optimizer converts the preceding SQL into the following:

Select workdept, sumno/crows from emp_summary 

When defining a materialized query table, we can specify whether to immediately refresh the materialized query table (refresh immediate) or refresh deferred when the raw table data changes. We can also specify, when appropriate, allow the optimizer to use the Enable qurey optimization or disable qurey optimization. We can also specify, whether the materialized query table is maintained by the system or by the user ).

If we define a materialized query table as refresh deferred, we must use the refresh TABLE statement to refresh the table before using the materialized query table. If you are responsible for maintaining the materialized query table, you can perform insert update Delete and other operations on the materialized query table. In this case, the materialized query table cannot be refresh.

It is quite time-consuming to maintain the materialized query table. To improve the maintenance efficiency, we can define a staging table for the materialized query table of refresh deferred. The staging table is used to incrementally refresh the materialized query table. When the refresh is completed, the staging table is deleted. For the materialized query table defined above, we can define the following staging table

Create Table emp_summary_st <br/> (<br/> workdept, <br/> crows, <br/> sumno, <br/> globaltransid, <br/> globaltranstime <br/>) for emp_summary propagate immediate; 

The propagate immediate clause indicates that any changes made to the original table will be accumulated in the staging table. Globaltransid indicates the global transaction ID corresponding to each row to be propagated ). Globaltranstime indicates the transaction timestamp. After creating a taging table, it is in the check pending state. We can use the set integrity statement to set the table to normal. In this case, we can use the staging table to refresh the materialized query table.

Set integrity for emp_summary_st staging immediate unchecked; <br/> refresh table emp_summary; 

For more details, see DB2 Information Center.

 

---- For more information, see:DB2 SQL

----Statement: indicate the source for reprinting.

---- Last updated on 2010.1.21

---- Written by shangbo on 2010.1.21

---- End