467,887 Members | 1,568 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

Optimizer not using MQT ?

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
  • viewed: 1577
Share:
1 Reply
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.

Similar topics

1 post views Thread by Nick Mudge | last post: by
14 posts views Thread by Bupp Phillips | last post: by
3 posts views Thread by Peter Arrenbrecht | last post: by
2 posts views Thread by Brian Ross | last post: by
5 posts views Thread by Kevin | last post: by
3 posts views Thread by aj | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.