DB2 Cube Views: A Primer

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

TIMESTAMP.

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

appropriate indexes).

򐂰 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

resource consumption.

򐂰 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)