A replicated MQT is not matched for an insert-from-select. In
the explain plan, EXP0060W is reported for the replicated MQT.
Some necessary conditions to hit this problem are:
1. It's an INSERT statement.
2. The INSERT statement source subquery has an EXISTS predicate
that references a base table with an MQT created on top.
3. The INSERT statement source subquery queries a UNION ALL
view, and the EXISTS predicate is applied on that UNION ALL
view.
4. The UNION ALL has non-deterministic or side-effect
expression, such as RAND() in example below.
Example:
create table dim1 ( col1 int not null, col2 int);
create table dim2 ( col1 int not null, col2 int);
create table fact_1 ( col1 int, col2 int, col3 int, col4 int,
col5 int ) DISTRIBUTE BY HASH("COL1") ;
create table fact_2 ( col1 int, col2 int, col3 int, col4 int,
col5 int ) DISTRIBUTE BY HASH("COL1") ;
create view fact_all as (
select * from (
select fact_1.* from fact_1, dim2 where fact_1.col1 = dim2.col1
union all
select fact_2.* from fact_2, dim2 where fact_2.col1 = dim2.col1
)
where CASE WHEN 1 = 0 THEN RAND () ELSE 1 END = 1);
create table dim1_rep as ( select col1 from dim1 ) data
initially deferred refresh deferred replicated;
refresh table dim1_rep;
set current refresh age any;
create table tab2 (tmp_col int);
For the following INSERT statement, dim1_rep is not matched.
EXP0060W is caught in db2exfmt plan.
insert into tab2
select count(*)
from fact_all f
where exists (select 1 from dim1 d1 where d1.col1 = f.col1); |
