I created a MQT defined as:
select a.col1,a.col2,a.col3,b.col1.b.col2
from a , b
where a.id = b.id

Now, I execute this query which get rewritten to the MQT:
select a.col1,b.col1, c.val
from a, b, c
where a.id = b.id
and a.col2 = c.id

But when I execute the exact same query with a filter on c, it wouldnt
rewrite to MQT:
select a.col1,b.col1, c.val
from a, b, c
where a.id = b.id
and a.col2 = c.id and c.type = 'xyz' and c.bu = 'abc'

But with only one filter on c, it rewrites. Isnt that strange? Any
input is highly appreciated.
select a.col1,b.col1, c.val
from a, b, c
where a.id = b.id
and a.col2 = c.id and c.type = 'xyz'

Look at explains of the statements. I'd suspect that the additional
predicate on table c makes a big difference in (row) selectivity of that
table. Indexes on a, b, and the MQT will impact the access path too.

Phil Sherman



rajesh.balu@gmail.com wrote:
> But with only one filter on c, it rewrites. Isnt that strange? Any
> input is highly appreciated.
> select a.col1,b.col1, c.val
> from a, b, c
> where a.id = b.id
> and a.col2 = c.id and c.type = 'xyz'
>

Thanks for the response, Phil.
Is that right? If it finds that the explain plan cost or estimated rows
is better when hitting the base table than the MQT, it would hit base
table? I tried various combinations of various filters and I did find
that when filtered on certain columns than others, the plan changes
from MQT to base table.
But shouldn't MQT always be better than the base? Is there a way to
direct DB2 to use MQT irrespective of the plan cost difference between
base table and MQT.

Phil Sherman wrote:
> Look at explains of the statements. I'd suspect that the additional
> predicate on table c makes a big difference in (row) selectivity of that
> table. Indexes on a, b, and the MQT will impact the access path too.
>
> Phil Sherman
>
>
>
> rajesh.balu@gmail.com wrote:
> > But with only one filter on c, it rewrites. Isnt that strange? Any
> > input is highly appreciated.
> > select a.col1,b.col1, c.val
> > from a, b, c
> > where a.id = b.id
> > and a.col2 = c.id and c.type = 'xyz'
> >

The optimizer's job is to find the most efficient (lowest cost) way to
retrieve the data. Assume no indexes in the MQT and indexes on
appropriate columns of tables a and b. If the selectivity on table c
yields only a few rows, then nested loop joins, using indexes are very
efficient. When selectivity on table c yields a larger number of rows,
then a scan/sort of the MQT becomes more efficient.

You don't state if you placed indexes on the MQT. Try adding one on the
a.col2 column and see if that encourages the optimizer to always use the
MQT. I'd also suspect that if you use "optimize for 1 rows" then the
optimizer will be biased to use the base tables a and b if there are no
indexes on the MQT.

Phil Sherman



rajesh.balu@gmail.com wrote:
> Thanks for the response, Phil.
> Is that right? If it finds that the explain plan cost or estimated rows
> is better when hitting the base table than the MQT, it would hit base
> table? I tried various combinations of various filters and I did find
> that when filtered on certain columns than others, the plan changes
> from MQT to base table.
> But shouldn't MQT always be better than the base? Is there a way to
> direct DB2 to use MQT irrespective of the plan cost difference between
> base table and MQT.
>
> Phil Sherman wrote:
>
>>Look at explains of the statements. I'd suspect that the additional
>>predicate on table c makes a big difference in (row) selectivity of that
>>table. Indexes on a, b, and the MQT will impact the access path too.
>>
>>Phil Sherman
>>
>>
>>
>>rajesh.balu@gmail.com wrote:
>>
>>>But with only one filter on c, it rewrites. Isnt that strange? Any
>>>input is highly appreciated.
>>>select a.col1,b.col1, c.val
>>>from a, b, c
>>>where a.id = b.id
>>>and a.col2 = c.id and c.type = 'xyz'
>>>
>
>