469,348 Members | 1,322 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,348 developers. It's quick & easy.

query on query plan and MQT

i have two questions:

1. A query plan only shows estimated cost and size at each operator.
is it possible to show the actual cost/cardinality of each plan
operator after running the query?

2. I created user-defined MQT with "enable optimization" option, and
bring it out of integrity pending state, however, I found the query
optimizer will never use this MQT for query processing. Is there any
reason for it? (how shall i debug)?

thanks
Sep 17 '08 #1
3 1715
Ian
uw****@gmail.com wrote:
i have two questions:

1. A query plan only shows estimated cost and size at each operator.
is it possible to show the actual cost/cardinality of each plan
operator after running the query?
No.

There was a research project called the Learning Optimizer (LEO) that
intended to provide a feedback loop -- so DB2 can compare the
optimizer's estimates with reality -- but I don't know what happened
to this project.

It appears as though some of this work went into the brains behind
automatic statistics profiles. But I would imagine that any actual
information would not be externalized.
2. I created user-defined MQT with "enable optimization" option, and
bring it out of integrity pending state, however, I found the query
optimizer will never use this MQT for query processing. Is there any
reason for it? (how shall i debug)?
What does your query plan tell you? You'll get output in db2exfmt as
to why MQTs were or were not used.
Sep 17 '08 #2
Thanks Ian for the first question.
2. I created user-defined MQT with "enable optimization" option, and
bring it out of integrity pending state, however, I found the query
optimizer will never use this MQT for query processing. Is there any
reason for it? (how shall i debug)?

What does your query plan tell you? You'll get output in db2exfmt as
to why MQTs were or were not used.
I doult this will happen. DB2 is trying to match each subquery to each
MQT in its catalog. will it tell me the matching result of this N:N
matching algorithm in its query plan?
Sep 18 '08 #3
Did you run runstats on the MQT?
Sep 18 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Bob | last post: by
11 posts views Thread by Eugenio | last post: by
2 posts views Thread by jim_geissman | last post: by
reply views Thread by apb18 | last post: by
7 posts views Thread by stig | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.