Lets try it !
This page demonstrates creation of replicated MQT on the dimension table.
Operation
- Create a Replicated MQT on the dimension table to reside on the partitions containing the fact tables
- Refresh the MQT to organize the data within the MQT
- Validate the data by issuing the SET INTEGRITY statement. The data is accessible only after integrity check is performed
- Execute RUNSTATS to ensure that current statistics are present in the MQT
Best Practices
- Focus on frequently-used queries that use a lot of resources. These queries provide the greatest opportunities for performance gains through MQTs.
- Set a limit on the number of MQTs that you are willing to maintain. There are
two reasons for this :
- Each MQT uses storage space on disk and additional UPDATE overhead
- Each MQT adds complexity to the search for the optimal QEP, increasing query compilation time
- Decide on a limit for the amount of disk space available for MQTs. Generally, do not allocate more than 10% to 20% of the total system storage of a data
warehouse for MQTs
- Keep statistics on the MQTs up-to-date
- Avoid problematic MQT designs that make routing difficult. Try to avoid using EXISTS, NOT EXISTS, and SELECT DISTINCT. Unless the MQT is an exact match for a query, these predicates can make it difficult for the query compiler to make use of the MQT.