How could the optimizer choose to use the MQT

[Angela]

I had built a summary table as follow:

db2 "create table db2inst1.thirdcube as (select a,b,c,sum(fact) as
amount from db2inst1.fact1 group by cube(a,b,c) ) DATA INITIALLY
DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION"
db2 "refresh table db2inst1.thirdcube"
db2 "RUNSTATS ON table db2inst1.thirdcube WITH DISTRIBUTION"

But when I run a qurey:
select a, sum(fact) from fact1 group by a
The query still used the base table fact1 instead of the summary
table.
I also tried:
SET CURRENT REFRESH AGE ANY
SET CURRENT QUERY OPTIMIZATION 9
But it does not work.
Any one of you would help me figure it out?

Thanks a lot,
Angela

[Anton Versteeg]

I think you should do the select straight from the MQT table , not from
fact1.

Angela wrote:

I had built a summary table as follow:

db2 "create table db2inst1.thirdcube as (select a,b,c,sum(fact) as
amount from db2inst1.fact1 group by cube(a,b,c) ) DATA INITIALLY
DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION"
db2 "refresh table db2inst1.thirdcube"
db2 "RUNSTATS ON table db2inst1.thirdcube WITH DISTRIBUTION"

But when I run a qurey:
select a, sum(fact) from fact1 group by a
The query still used the base table fact1 instead of the summary
table.
I also tried:
SET CURRENT REFRESH AGE ANY
SET CURRENT QUERY OPTIMIZATION 9
But it does not work.
Any one of you would help me figure it out?

Thanks a lot,
Angela

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

[AK]

Angela,

have you tried "group by a,b,c" instead of "group by cube(a,b,c)"?

[Spencer]

One of the points of using MQT's is that the optimizer is supposed to
do query rewrite to use this table. Angela's question is valid and
warrants an answer not a workaround.
Anton Versteeg <an************@nnll.iibbmm.com> wrote in message news:<3F**************@nnll.iibbmm.com>...

I think you should do the select straight from the MQT table , not from
fact1.

Angela wrote:

I had built a summary table as follow:

db2 "create table db2inst1.thirdcube as (select a,b,c,sum(fact) as
amount from db2inst1.fact1 group by cube(a,b,c) ) DATA INITIALLY
DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION"
db2 "refresh table db2inst1.thirdcube"
db2 "RUNSTATS ON table db2inst1.thirdcube WITH DISTRIBUTION"

But when I run a qurey:
select a, sum(fact) from fact1 group by a
The query still used the base table fact1 instead of the summary
table.
I also tried:
SET CURRENT REFRESH AGE ANY
SET CURRENT QUERY OPTIMIZATION 9
But it does not work.
Any one of you would help me figure it out?

Thanks a lot,
Angela

[miro flasza]

Angela wrote:

I had built a summary table as follow:

db2 "create table db2inst1.thirdcube as (select a,b,c,sum(fact) as
amount from db2inst1.fact1 group by cube(a,b,c) ) DATA INITIALLY
DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION"
db2 "refresh table db2inst1.thirdcube"
db2 "RUNSTATS ON table db2inst1.thirdcube WITH DISTRIBUTION"

But when I run a qurey:
select a, sum(fact) from fact1 group by a
The query still used the base table fact1 instead of the summary
table.
I also tried:
SET CURRENT REFRESH AGE ANY
SET CURRENT QUERY OPTIMIZATION 9
But it does not work.
Any one of you would help me figure it out?

Thanks a lot,
Angela

This query should route to your MQT. Are the columns A,B,C nullable?
If yes, we usually recommend including the GROUPING() function for
each nullable column that is part of the cube in the MQT definition.
Otherwise we might not be able to uniquely identify the row in the
cube representing the rollup by A.

Regards,
Miro