Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Query Optimization does NOT work with MQT

    Hi All,
    I am using DB2 V8.2 in Linux.
    I want to make DB2 use MQT for better performance. By using "enable query optimization" when creating MQT, I expected DB2 to rewrite my SQL to access the MQT when my SQL references the base table. Somehow this didn't work for me. I tried to define the MQT exactly the same as my query. It still didn't work. Here are the the scripts (current query optimization = 5):

    The query:
    Code:
     
    select columns-list
    from base_table 
    WHERE    (RUN_STA =6 OR RUN_STA =2) AND 
             ITM_TYP <>4                AND 
             APPL_ID =6                 AND
             (APPL_SRV_NM =108 OR APPL_SRV_NM is NULL)
    The MQT and others:
    Code:
     
    create table query_base_table as 
    (
    select same-column-list-as-the-query
    from base_table 
    WHERE    (RUN_STA =6 OR RUN_STA =2) AND 
             ITM_TYP <>4                AND 
             APPL_ID =6                 AND
             (APPL_SRV_NM =108 OR APPL_SRV_NM is NULL)
    )
    data initially deferred
    refresh immediate
    enable query optimization
    maintained by system
    ;
     
    refresh table query_base_table;
    CREATE INDEX N1_qBSAGENT_BS_AGE ON query_base_table 
      (RUN_STA,APPL_ID,APPL_SRV_NM,ITM_TYP);
     
    runstats on table schema.base_table with distribution and detailed indexes all;
    runstats on table schema.query_base_table with distribution and detailed indexes all;
     
    db2expln -database db-name -output o.out -stmtfile q.sql -terminator ';' -graph
    Output from the explain tool showed DB2 chose to access the base table directly through an index on column RUN_STA which has a cardinality of 6, while the table has 155K rows.
    I don't know what I am missing and how to make DB2 "optimize" the query by using the MQT.

    Thank you for advice in advance.
    Last edited by DBA-Jr; 11-09-06 at 14:27.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the value of CURRENT QUERY OPTIMIZATION. As the manual says,

    REFRESH IMMEDIATE MAINTAINED BY SYSTEM materialized query tables can always be used to optimize the processing of a query if the CURRENT QUERY OPTIMIZATION special register is set to 2 or a value greater than or equal to 5.

  3. #3
    Join Date
    Mar 2005
    Posts
    108
    The Value is 5 as mentioned 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
  •