Hi,

I have just implemented some Refresh Deferred MQTS in version 8 and am having a few issues with the performance of the refresh command.
From a base table(100million rows) i am refreshing an MQT to aggregate up to daily level. This seems to be ok.
I am then refreshing an MQT to aggregate up to weekly level, this is where the problems start.
If you execute the SQL in the create weekly MQT as a simple select the data is retrieved quickly as the select is redirected to the daily MQT.
However as soon as you issue the weekly MQT refresh the refresh is performed against the base tables, hence taking 2 to 3 times longer.
Does anyone know if this is the intended functionality or is there is something i can do to force it to redirect to the MQT.

N.B. we are not in a position to use refresh immediate or staging tables to do an incremental refresh!

Any help/ideas would be grately appriciated.
Clive

Did you do a RUNSTATS on MQT following the change in definition ?

If you haven't, I suggest you do and test ...

Cheers
Sathyaram

Hi Guys,
one question:
if we have host variable in our sql statement can we create MQT?

Thank you in advance for your answer.

I have done a run stats,
Any other ideas.

Only your statistics on your MQT and base table, and your query can explain this behaviour

Cheers
Sathyaram