Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Unanswered: Have problem in hitting MQTs

    Hi All,

    i have problem in using MQTs to increase the response time of my normal queries.

    My original query is -

    select sum(sale_amt) sale_amt from db2dba.daily_trx_sale_cmsn_fact a, db2dba.tm_lkup b where a.PAID_DT_KEY_ID = b.time_key_id;

    Since we thought that MQTs will serve the purpose, we created MQTs.
    Even after completing the following steps, still we find that the base table is being accessed and not the MQT.

    a) created matview using the following command :

    create table db2dba.mat_view1 as
    (select year(b.time_date) as year,
    month(b.time_date) as month, sum(a.SALE_AMT) as sale_amt
    from db2dba.daily_trx_sale_cmsn_fact a, db2dba.tm_lkup b
    where a.PAID_DT_KEY_ID = b.time_key_id
    group by year(b.time_date), month(b.time_date)) data initially deferred refresh deferred
    maintained by system enable query optimization ;

    b) refreshed the mat view and set the following parameters:

    refresh table db2dba.mat_view1
    set integrity for db2dba.mat_view1 immediate checked
    SET CURRENT REFRESH AGE = ANY
    SET CURRENT QUERY OPTIMIZATION 7

    We performed all these in "Command Editor" . Then , we executed the query stated first. The access plan in command editor shows that MQT is not being used.

    One intersting fact is that - before MQT was created and refresh was done , the query was taking around 13 seconds. But after MQT creation, refresh and parameters were set , the query comes in 1 or 2 seconds . Not sure why this is not shown in the access plan.

    I am using DB2/UDB - 8.1.10 version

    Can anyone help in this regards??

    Thanks,
    Arunvijay

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did you update your table and MQT statistics?

    Also, your MQT uses GROUP BY. Depending on the statistics, the aggregation of values across groupings may not be considered cheaper than directly querying the table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2008
    Posts
    11
    Ya.. i did executed the runstat commands before running the query

    RUNSTATS ON TABLE DB2dba.daily_trx_sale_cmsn_fact WITH DISTRIBUTION and detailed indexes all;

    RUNSTATS ON TABLE db2dba.mat_view1;

    But even then it was not using the MQTs.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How big are your tables? What's the access plan when you query the MQT directly? And are you using the same or a lower isolation level in your query - compared to the isolation level when the MQT was created?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •