Database vs. Application controlled Redirection
What factors are involved in deciding if DB2 should rewrite the query redirecting to the right MQT or should we delegate it to the application? PMO stands for "Poor Man’s Optimization", a term jokingly used to describe a mechanism, implemented through union views, that redirected queries to a desired MQT level. Some BI applications such as Business Objects and Microstrategy provide ways for achieving the same goal. Should we fully rely on optimizer-induced redirection to the MQTs or should we force the level in the application? How can one achieve application-driven redirection? Can we have the cake and eat it too? DB2 can make way more sophisticated decisions regarding optimal performance, however there are trade-offs that need to be evaluated. The decision largely depends on whether MQT matching is fast and reliable enough, or conversely, what simplifications one is forced into so that the optimizer can reliably and effectively redirect queries to the appropriate MQTs.