Lets try it !

This page demonstrates creation of replicated MQT on the dimension table.

Operation


  1. Create a Replicated MQT on the dimension table to reside on the partitions containing the fact tables
  2. Refresh the MQT to organize the data within the MQT
  3. Validate the data by issuing the SET INTEGRITY statement. The data is accessible only after integrity check is performed
  4. Execute RUNSTATS to ensure that current statistics are present in the MQT


Best Practices


  1. Focus on frequently-used queries that use a lot of resources. These queries provide the greatest opportunities for performance gains through MQTs.
  2. Set a limit on the number of MQTs that you are willing to maintain. There are two reasons for this :
  3. 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
  4. Keep statistics on the MQTs up-to-date
  5. 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.