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.