After detaching a partition from a range partitioned table with
a dependent materialized query table (MQT), a unique global
index might get created.
If a unique global index gets created and the detached partition
has any duplicates, refreshing the MQT might lead to incorrect
results.
The following commands illustrate how refreshing the MQT might
lead to incorrect results:
create table t1(rk int not null, uk int not null) partition by
range(rk) (starting 0 ending 10001 every 1000);
insert into t1 values(1,1),(2,1);
create table t2 (uk int not null);
insert into t2 values(1);
create table m1 as (select t1.uk, count(*) as count from t1, t2
where t1.uk=t2.uk group by t1.uk) data initially deferred
refresh immediate;
refresh table m1;
select * from m1;
UK COUNT
----------- -----------
1 2
1 record(s) selected.
alter table t1 detach partition part0 into part0;
create unique index t1_uk on t1(uk) not partitioned;
refresh table m1;
select * from m1;
UK COUNT
----------- -----------
1 1
1 record(s) selected.
To identify the MQTs that are affected by this problem, perform
the following steps:
1) Record the current query optimization level and set it to 3
by issuing the following commands:
select current query optimization from sysibm.sysdummy1;
set current query optimization 3;
2) Verify if querying the MQT and the MQT query definition
return different result set by issuing the following commands:
(mqt_query_definition) except all select * from mqt
select * from mqt except all (mqt_query_definition)
Using the previous example, the resultant queries are as
follows:
(select t1.uk, count(*) as count from t1, t2 where t1.uk=t2.uk
group by t1.uk) except all select * from m1
UK COUNT
----------- -----------
0 record(s) selected.
select * from m1 except all (select t1.uk, count(*) as count
from t1, t2 where t1.uk=t2.uk group by t1.uk)
UK COUNT
----------- -----------
1 1
1 record(s) selected.
If either of the EXCEPT ALL queries return any record, then that
MQT is
impacted and is returning incorrect result. |
