query on query plan and MQT

[uwcssa]

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

[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.

[uwcssa]

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?

[w.l.fischer]

Did you run runstats on the MQT?