Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    May 2015
    Posts
    11

    Angry Unanswered: MQT access & Dimensional Filtering

    I have this fact table:

    DAY_ID
    LOCATION_ID
    PRODUCT_ID
    MEASURE 1
    MEASURE 2

    and 3 linked Dimension Table:

    CALENDAR_DIM(DAY_ID)
    LOCATION_DIM(LOCATION_ID)
    PRODUCT_DIM(PRODUCT_ID)
    I built an MQT on fact table as following:

    CREATE SUMMARY TABLE FACT_AGGREGATE AS (
    SELECT CAL.YEAR,CAL.MONTH,F.LOCATION_ID,F.PRODUCT_ID,SUM( F.MEASURE1),SUM(F.MEASURE2)
    FROM CALENDAR CAL,FACT_TABLE F
    WHERE CAL.DAY_ID = F.DAY_ID
    GROUP BY CAL.YEAR,CAL.MONTH,F.LOCATION_ID,PRODUCT_ID)

    For example, using this query:

    SELECT CAL.YEAR,CAL.MONTH,LOC.SECTOR,PRD.FAMILY,SUM(MEASU RE1)
    FROM CALENDAR_DIM CAL ,LOCATION_DIM LOC,PRODUCT_DIM PRD
    WHERE CAL.DAY_ID = F.DAY_ID AND
    LOC.LOCATION_ID = F.LOCATION_ID AND
    PRD.PRODUCT_ID = F.PRODUCT_ID
    GROUP BY CAL.YEAR,CAL.MONTH,LOC.SECTOR,PRD.FAMILY

    the DB2 optimizer points to materialized table, instead using this one:

    SELECT CAL.YEAR,CAL.MONTH,LOC.SECTOR,PRD.FAMILY,SUM(MEASU RE1)
    FROM CALENDAR_DIM CAL ,LOCATION_DIM LOC,PRODUCT_DIM PRD
    WHERE CAL.DAY_ID = F.DAY_ID AND
    LOC.LOCATION_ID = F.LOCATION_ID AND
    PRD.PRODUCT_ID = F.PRODUCT_ID and
    CAL.YEAR = 2014
    GROUP BY CAL.YEAR,CAL.MONTH,LOC.SECTOR,PRD.FAMILY

    it doesn't use the materialized table. Why the filter affects in that way the query execution?

    Can someone explain me this behaviour?

    PS: My db2 version is 9.5

    Thanks in advance

    Emiliano

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Do you have any messages in the 'Extended Diagnostic Information' section of the access plan?
    Regards,
    Mark.

  3. #3
    Join Date
    May 2015
    Posts
    11

    MQT access & Dimensional Filtering

    Hi Mark,
    i have attached the queries execution plans in both cases. The query is the same. explain_noMQT with filters and explain_MQT.txt without filters.

    Thanks a lot

    Emiliano
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Emiliano,

    Can you attach the output of db2exfmt utility?
    db2expln doesn't show the diagnostic messages...

    SET CURRENT REFRESH AGE=ANY;
    EXPLAIN ALL WITH SNAPSHOT FOR
    SELECT ...
    ;

    db2exfmt -d mydb -1 -o explain_noMQT.txt ...
    Regards,
    Mark.

  5. #5
    Join Date
    May 2015
    Posts
    11

    MQT access & Dimensional Filtering

    Hi Mark,

    I attached the db2exfmt command results in both cases.

    The MQT seems more cost expensive than base table, but i do not understand why... It contains less rows than base table...It's aggregated for month...

    Thanks in advance
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Emiliano,

    try db2advis for your query.
    Regards,
    Mark.

  7. #7
    Join Date
    May 2015
    Posts
    11

    MQT-access-amp-Dimensional-Filtering

    I executed the advises suggest by db2advis utility but the problem still persists.

    The optimizer still prefers no MQT path.

    Thanks Mark

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    The last thing I can suggest is to use optimization profile for this query where you can explicitly specify this MQT use.
    You can compare the cost & execution time in both cases...
    Regards,
    Mark.

  9. #9
    Join Date
    May 2015
    Posts
    11

    MQT access & Dimensional Filtering

    How i can set optimization profile for my query?

    Thanks

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Influence query optimization with optimization profiles and statistical views in DB2 9

    Use the following request inside:
    Code:
    <OPTGUIDELINES>
        <MQT NAME='DWH.MQT_FACTS_VENDITE_AGG'/>
    </OPTGUIDELINES>
    The statement inside the profile must be absolutely the same as your query text.

    Do not use SCHEMA attribute inside the STMTKEY tag of the profile.
    I mean use:
    <STMTKEY> ... </STMTKEY>
    instead of:
    <STMTKEY SCHEMA="MYSCHEMA"> ... </STMTKEY>
    Regards,
    Mark.

  11. #11
    Join Date
    Nov 2011
    Posts
    334
    Hi Emiliano,

    Does the mqt table MQT_FACTS_VENDITE_AGG and the base FACTS_VENDITE_AGG
    have the same indexes and the same "organized by" clause ?
    if you could ,please post the tables ddl here.

    thx.

  12. #12
    Join Date
    May 2015
    Posts
    11

    MQT access & Dimensional Filtering

    Hi fengsun2,

    I attached both ddl: base table & MQT

    The column DT_COMPET_CD represents the day key (20130101), the column ANNO_CALENDARIO represents the year (2013) and MESE_CALENDARIO represents the month (1)

    thanks for your help

    Emiliano
    Attached Files Attached Files

  13. #13
    Join Date
    Nov 2011
    Posts
    334
    Hi Emiliano,
    As i mentioned above, the base table and the mqt table has the different organized by column。

    CREATE TABLE "DWH "."FACTS_VENDITE_AGG" (
    ....
    ORGANIZE BY (
    ( "LOCATION_CD" ) ,
    ( "DT_COMPET_CD" ) )
    ;

    CREATE SUMMARY TABLE DWH.MQT_FACTS_VENDITE_AGG AS

    (SELECT DD.ANNO_CALENDARIO,
    .....
    ORGANIZE BY (LOCATION_CD);

    that's why optimizer cannot use the semi-join Anding strategy with the MQT table。
    and it's strange for me that the range partition and mdc use the same partition column(DT_COMPET_CD)。

    PARTITION BY RANGE("DT_COMPET_CD")
    (PART "PART0" STARTING(+20110101.) IN "TS_FACT_1_VEN",
    PART "PART1" STARTING(+20120101.) IN "TS_FACT_2_VEN",
    PART "PART2" STARTING(+20130101.) IN "TS_FACT_3_VEN",
    PART "PART3" STARTING(+20140101.) IN "TS_FACT_4_VEN",
    PART "PART4" STARTING(+20150101.) IN "TS_FACT_5_VEN",
    PART "PART5" STARTING(+20160101.) IN "TS_FACT_6_VEN",
    PART "PART6" STARTING(+20170101.) IN "TS_FACT_7_VEN",
    PART "PART7" STARTING(+20180101.) IN "TS_FACT_8_VEN",
    PART "PART8" STARTING(+20190101.) IN "TS_FACT_9_VEN",
    PART "PART9" STARTING(+20200101.) ENDING(+20201231.) IN "TS_FACT_1_VEN")
    ORGANIZE BY (
    ( "LOCATION_CD" ) ,
    ( "DT_COMPET_CD" ) )
    ;

  14. #14
    Join Date
    May 2015
    Posts
    11
    Hi fengsun2,
    Now it's all ok!!!!
    The problem was exactly what you said.
    The "ORGANIZE BY" clause must be the same!!!
    I set LOCATION_CD in both ddl scripts and now the optimizer understand the semi-join correctly.

    Thanks a lot for your support
    Emiliano

  15. #15
    Join Date
    May 2015
    Posts
    11

    MQT access & Dimensional Filtering

    Hi Fengsun,
    unfortunately the problem occurred another time. I changed indexes setting and now the optimizer doesn't access to MQT.
    I'm very frustrated because I do not understand why it happen!!!
    I have attached both DDL (base and MQT).
    Could you take a look and say me what do you think?
    Thanks in advance.

    Emiliano
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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