By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Optimizer not using MQT ?

P: n/a
Sa
DB2 V 8.1.6 and DB2 V8.2.4

I'm using a Fact Table and 7 Dimension tables in a MQT definition.

create mqt1(col-list) as
(
select dim1_desc,
dim2_desc,
....
dim6_date,
dim7_desc,
sum(amount),
count(amount),
grouping(dim1_desc),
grouping(dim2_desc),
...
grouping(dim7_desc)
from fact , dim1,dim2,dim3....dim7
where <join condition - matching the dimension keys between fact and
dimension table>
and dim6.date between '2001-01-01' and '2001-01-31'
) data initially deferred refresh deferred enable query optimization ..
When I execute queries, for example,

select dim1_desc,dim6_date,sum(amount) from dim1,dim6,fact where <join
conditions> and dim6.date='2001-01-15'

or

select dim1_desc,dim6_date,sum(amount) from dim1,dim6,fact where <join
conditions> and dim6.date between '2001-01-01' and '2001-01-30'

they do not use the MQT (CURRENT REFRESH AGE is set to ANY)
But the same queries use the MQT if the MQT defintion excludes the
clause "and dim6.date between '2001-01-01' and '2001-01-31' " ...

Is this a known limitation of query rewrite using MQT or am I missing
something ?

Thanks in advance.

Mar 16 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
SA,

Did you try putting dim6.date into "group by" clause in the MQT
definition?

Regards,

Mehmet

Mar 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.