Chapter 4. Using the cube model for summary tables optimization 215
In a partitioned environment the query must have the partition key as a subset
of the GROUP BY items.
The query should not contain any special registers like CURRENT
4.10 MQT tuning
Since an MQT is just another table, any normal tuning technique applies, such as
having RUNSTATS currently updated and having appropriated indexes defined.
The following are some recommendations that you need to carefully evaluate to
implement in your production environment:
Create index on MQT columns that are referenced on the where clause for
most used queries (use the DB2 Index Advisory to help you identify
Evaluate the possibility to use unique index with include columns on
dimension tables. It can speed up retrieval time from these tables during the
INCREMENTAL and FULL refresh of MQTs.
Create a non-unique index on the MQT columns that guarantee uniqueness
of rows in an MQT. In case of a partitioned MQT, the partition key should be a
subset of the columns described above.
Do not create an index on the staging table, since such indexes degrade the
performance of appends to the staging table.
For partition tables, make sure that you partition the staging table according
to the partitioning of the MQT to promote collocated joins.
Refresh of MQTs consumes CPU, I/O, and buffer pool resources, which
ultimately impacts other users contending for the same resources. Refresh
resource consumption can be reduced by combining multiple MQTs in a
single refresh statement, since DB2 uses “multiple-query optimization” to
share joins and aggregations required of each MQT in order to reduce the
Reorganize tables (regular and MQTs) after incremental load, insert, and
delete of large amounts of data.
Collect statistics for underlying tables and on the MQTs:
– After performing INCREMENTAL refresh on MQTs
– After performing FULL refresh on MQTs
– Perform any changes on existing MQTs (such as create, alter, or remove
index, alter table)