MQT optimization

[JBaileys]

When using a base underlining table to query data that is already calculated in the MQT table, I assume the system would pull from the MQT table.

What conditions "encourage" the system to pull from the MQT table rather than the underlining base tables.

 

[blom0344]

http://www.craigsmullins.com/zjdp_008.htm

says it all really..

Ties Blom

 

[JBaileys]

Ties,

Thanks for the article - but this provides an overview of MQT which I am already familiar with.

It talks about the automatic query rewrite (AQR) - which uses the MQT rather than the base table that is referenced in the query. I developed 2 queries - one against the base table taking 3 - 5 seconds and the same data represented in the MQT taking less that .5 second. I don't know why the AQR doesn't know to use the MQT which is obviously faster. I ran them both the SQL Explain (with in quest).

Thanks,

-JB

 

[blom0344]

I guess that DB2 optimizer will need statistics to be able to make a decision to deploy AQR strategy. Did you make the effort to update statistics AFTER creating the MQT?

Ties Blom

 

[JBaileys]

Ran update statistics with this command:

Reorgchk update statistics on table all

Also refreshed the mqt table:


refresh table <mqt_table_name>