I have produced a person-maintained MQT (Materialized Query Table) in DB2 9.7 (not fixpack 1). It is a simple grouping from the primary fact table. But my queries aren't being rewritten hitting it.
This is what I have attempted:
- Produced the MQT with ENABLE QUERY Optimisation and MAINTAINED BY USER qualities
- Also incorporated REFRESH DEFERRED &lifier DATA INITIALLY DEFERRED. (Maybe I should not have?)
- Set registry variables telling DB2 to make use of all kinds of MQTs for optimisation
- Went "SET INTEGRITY for tableX ALL IMMEDIATE UNCHECKED"
- Went runstats
- Flushed the cache: FLUSH PACKAGE CACHE DYNAMIC
- Made certain that default query optimisation class was a minimum of at level 2 (it's at 5)
- Set default refresh age to (tho I suppose this does not matter with user-defined MQTs)
Then attempted to find out when the optimizer would make use of the MQT:
- Attempted various simple queries which i be prepared to make use of the MQT - either:
- Choose COUNT(*) FROM fact_table
- or Choose group-dimension, COUNT(*) FROM fact_table GROUP BY group-dimension.
- Explain (using db2expln) only recommended the very fact table and never the MQT
- Query results demonstrated counts in conjuction with the fact table and never MQT table
- Query duration was in line with fact table and never MQT table.
Any suggestions on whether simpler method to know if a question is applying an MQT or things i need next to have it for doing things?
1) What's the
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION register set to? It defaults to regardless of the
DFT_MTTB_TYPES database configuration parameter is -- and also the default value is 'SYSTEM' - therefore the optimizer would ignore your MQT.
2) Also, your assumption about
DFT_REFRESH_AGE and MAINTAINED BY USER MQTs is wrong.
DFT_REFRESH_AGE still is applicable -- for any user-maintained MQT, the
CURRENT REFRESH AGE register should be set to the for a refresh deferred MQT that need considering.