Marc, Thanks for the * ;-)
Here's a brief on MQTs ..
MQT is Materialized Query Table - the definition of which the optimizer is aware of .. Therefore, you can write a query against the base tables of the MQT, and if the optimizer thinks that the data is available in the MQT and its access is more efficient, data is read from the MQT . This is my preferred method - as the appl is not dependent on the MQT and therefore any delays or problems in building them does not have an impact on the output of the appl ..
Of course, there is nothing to stop you from querying the MQT directly ..
MQTs - like other tables can have indexes and needs RUNSTATS to help the optimizer ..
MQTs can be REFRESH IMMEDIATE or REFRESH DEFERRED.
MQTs can be MAINTAINED BY USER or MAINTAINED BY SYSTEM or MAINTAINED BY FEDERATED_TOOL
Eg:
If the base table tab1 is
col1
col2
col3
col4
col5
..
...
col20
, you can define a MQT like
create table mqt1
as
(select
col1 , col2, col3, col4
count(*) as row_ct
from eudw.dwa_mrch_sales
group by
col1,col2,col3,col4
)
data initially deferred
refresh immediate
[ Can be based on a more complex query as well ]
followed by
REFRESH TABLE MQT1
Becuase it is refresh immediate, any update to tab1 will be updated to mqt1 (and obviously there will be a perf degradation)
For this defintion, all the following requests are likely to use mqt1
select col1,col2,col3,col4,count(*) from tab1 group by col1,col2,col3,col4
select col1,count(*) from tab1 where col2=5 group by col1
select col1,count(*) from tab1 group by col1
select col1,col2,count(*) from tab1 group by col1,col2
etc ..
Even queries like
select distinct col1,col2 from tabl
might use mqt1
Of course, not all MQTs and queries against the base tables are as simple ... So, you may have to test your MQT defn and queries ..
MQT is one area where the optimizer is getting better with every new release of DB2 and so it is worth a try...
Hope this helps
Cheers
Sathyaram
For db2 resoruces visit
More DB2 questions answered at
&