Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: DB2 MQT optimizer not working

    Hello,
    I'm using DB2 v9.7.0.4 fix pack 4 on Linux.
    I have the MQT named SUM_F2_USAGE created based on this SQL statement (IMMEDIATE REFRESH):
    Code:
    SELECT D_DATE.DATE_SK,
           D_CENTER.CENTER_SK,
           D_PACKAGE.PACKAGE_ID,
           D_POSTAL_GROUP.PARTNER_GROUP_ID,
           D_BAND_DURATION_RANGE.BAND_DURATION_RANGE_LINE_ID,
           SUM (F2_USAGE_MQT.CALL_COUNT) TOTAL,
           SUM (F2_USAGE_MQT.CALL_DURATION) TOTAL_DURATION,
           count ( * ) count1,
           count (F2_USAGE_MQT.CALL_COUNT) count2,
           count (F2_USAGE_MQT.CALL_DURATION) count3
      FROM D_POSTAL_GROUP,
           F2_USAGE,
           D_BAND_DURATION_RANGE,
           D_CENTER,
           D_TIME_HOUR,
           D_DATE,
           D_PACKAGE
     WHERE F2_USAGE_MQT.DIRECTION_ID = D_POSTAL_GROUP.PARTNER_GROUP_ID
       AND F2_USAGE_MQT.BAND_DURATION_ID =
              D_BAND_DURATION_RANGE.BAND_DURATION_RANGE_ID
       AND F2_USAGE_MQT.CENTER_ID = D_CENTER.CENTER_SK
       AND F2_USAGE_MQT.TIME_HOUR_ID = D_TIME_HOUR.TIME_HOUR_ID
       AND F2_USAGE_MQT.PACKAGE_ID = D_PACKAGE.PACKAGE_ID
       AND F2_USAGE_MQT.DATE_ID = D_DATE.DATE_SK
    GROUP BY D_DATE.DATE_SK,
             D_CENTER.CENTER_SK,
             D_PACKAGE.PACKAGE_ID,
             D_POSTAL_GROUP.PARTNER_GROUP_ID,
             D_BAND_DURATION_RANGE.BAND_DURATION_RANGE_LINE_ID
    Some parts of the query is not necessary but never mind, it's auto-generated so I have to create MQ Table based on it.
    - When I used Explain Plan function of Toad for DB2 to check the select statement used to create the MQT, it shown that db2 will query to table SUM_F2_USAGE -> it's working fine.
    - When I added some condition in WHERE clause:
    Code:
    SELECT D_DATE.DATE_SK,
           D_CENTER.CENTER_SK,
           D_PACKAGE.PACKAGE_ID,
           D_POSTAL_GROUP.PARTNER_GROUP_ID,
           D_BAND_DURATION_RANGE.BAND_DURATION_RANGE_LINE_ID,
           SUM (F2_USAGE_MQT.CALL_COUNT) TOTAL,
           SUM (F2_USAGE_MQT.CALL_DURATION) TOTAL_DURATION,
           count ( * ) count1,
           count (F2_USAGE_MQT.CALL_COUNT) count2,
           count (F2_USAGE_MQT.CALL_DURATION) count3
      FROM D_POSTAL_GROUP,
           F2_USAGE,
           D_BAND_DURATION_RANGE,
           D_CENTER,
           D_TIME_HOUR,
           D_DATE,
           D_PACKAGE
     WHERE F2_USAGE_MQT.DIRECTION_ID = D_POSTAL_GROUP.PARTNER_GROUP_ID
       AND F2_USAGE_MQT.BAND_DURATION_ID =
              D_BAND_DURATION_RANGE.BAND_DURATION_RANGE_ID
       AND F2_USAGE_MQT.CENTER_ID = D_CENTER.CENTER_SK
       AND F2_USAGE_MQT.TIME_HOUR_ID = D_TIME_HOUR.TIME_HOUR_ID
       AND F2_USAGE_MQT.PACKAGE_ID = D_PACKAGE.PACKAGE_ID
       AND F2_USAGE_MQT.DATE_ID = D_DATE.DATE_SK
       AND D_DATE.DATE_SK IN (1,2,3,4,5)
       AND D_POSTAL_GROUP.PARTNER_GROUP_ID IN (1000)
       AND D_CENTER.CENTER_SK IN (1)
    GROUP BY D_DATE.DATE_SK,
             D_CENTER.CENTER_SK,
             D_PACKAGE.PACKAGE_ID,
             D_POSTAL_GROUP.PARTNER_GROUP_ID,
             D_BAND_DURATION_RANGE.BAND_DURATION_RANGE_LINE_ID
    The blue code is the code added and the Explain Plan function aslo shown that db2 will query to table SUM_F2_USAGE -> working fine, but problem is the red code, when I added it, Explain Plan function of Toad for DB2 shown that db2 query and join from all 6 source table -> MQT optimizer not working.
    I can't find the different of my added WHERE clauses.
    Any help will be appreciated.
    Thank you!
    Last edited by vietnt9; 05-21-12 at 01:05.

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    can you show the both explain ( use mqt and not use mqt) here ?
    maybe db2 think not to use mqt is a better plan.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My guess was...

    Your query(and prefix of schema names) suggested star join, where the number of rows of result of joins and grouping would be at least multiply of cardinarities of grouping columns of dimension tables.

    So, the number of rows of the MQT might be not so drastically reduced from the number of rows of the fact table.

    If you specified more and more conditions for dimention tables, the rows need to be fetched get less and less.

    As a cosequence,
    the cost of fetch rows(of dimention tables and the fact table), join them, and grouping
    eventually get less than the cost of scanning whole rows of the MQT table,
    if approprate indexes were on dimention tables and the fact table.

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    try to create a index on mqt SUM_F2_USAGE (CENTER_SK) to see whether it works.

  5. #5
    Join Date
    May 2012
    Posts
    2
    Hello,
    This is how db2 query to MQT:
    http://i181.photobucket.com/albums/x...uperde/1-2.jpg
    And this is how db2 query to the fact table:
    http://i181.photobucket.com/albums/x...uperde/2-1.jpg

    The second image shows that it's much more complicated than the first.
    I created index for CENTER_SK and nothing happened

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    Can you post the full plan here ( use db2exfmt)
    on command line:
    db2 set current explain mode explain
    run the sql
    db2 set current explain mode no
    db2exfmt -d <dbname> -1 > 1.exfmt
    and show the 1.exfmt here
    Last edited by fengsun2; 05-22-12 at 02:54. Reason: from the pic you shown above,

Posting Permissions

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