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.
dBforums
>
Query Optimization does NOT work with MQT
11-09-06, 13:02
Linear Mode
