Chapter 4. Using the cube model for summary tables optimization 217
An MQT is a table, which contains pre-computed results whose definition is
based on the result of a query execute against the underlying tables. Based on
this, the size for an MQT is estimated using the same calculation as for a base
table. For information on how to estimate the size of the MQT, please refer to
Chapter 2 in the redbook,
Up and Running with DB2 UDB ESE: Partitioning for
Performance in an e-Business Intelligence World,
Besides the space required for the MQTs, you also need additional temporary
space for joins and aggregations.
The temporary tablespace is an important consideration during full REFRESH of
MQTs. If the MQT has a lot of measures, rollups, and/or group by elements,
more tempspace is generally required.
If the MQT's size estimate provided by DB2 Cube Views is very large, it is
probably an indication that you may need more tempspace.
The following formula helps you to estimate the TEMP space required for refresh
on an MQT:
TEMPSPACE Required = (# of pages required) * (pagesize) where
# of pages required) = (Total # of rows in the MQT) * (10+10) / 2560)
(10 + 10) = Is the size stored in the TEMP per MQT row. It is defined
twice in situation where it needs to the DELETE of old data and
INSERT of new data in the MQT.
2560 is a number based on the fact that we store 256 rows per page
(assuming 256 slots in a page of 10 bytes each).
1. The Delete is referring to deleting the old data in the MQT. If it was an initial
population, you do not need to account for this.
2. Note that the result of the formula gives the number of pages and the
number 2560 is independent of the page size. Depending on the page
size, we need to compute disk space accordingly. An 8K-page size would
require double amount of disk compared to a 4K-page size.
3. Particular care needs to be taken at the catalog node because the refresh
process of MQTs also uses TEMP space on this node. If you have a
separate the catalog node from the data nodes with very little TEMP
tablespace, you can have problems to perform full REFRESH on MQTs.
Make sure you add additional TEMP space on this node to avoid any
problems during the refresh process.