Unanswered: Replicated MQTs REFRESH DEFERRED vs. REFRESH IMMEDIATE
I have seen where despite the existence of a replicated MQT, the base table was being used when joined with a partitioned table due to the replicated MQT defined as REFRESH DEFERRED. Once it was recreated as REFRESH IMMEDIATE, REFRESHed and RUNSTATS run, it and not the base table was used for the join.
The key reason to having REFRESH DEFERRED is so it doesn't get populated every time a row gets inserted into the base table.
Is there a way to force the Optimizer to use the replicated MQT and yet having REFRESH DEFERRED?
P.S. The reason that the Optimizer chose the base table was that with DEFERRED set it thinks that the base table has the most current data, and not the replicated MQT.