Hi Tom,
Based on your answer to the thread "
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534336000346923525", two questions popped up in my mind. Can you please help me.
1. What if I want to create a non-partitioned matview on a partitioned table. If I drop any partition of the table, would it invalidate the matview and the refresh will fail? If yes, how can I overcome it so that the matview is not invalidated even if I drop any particular partition.
2. If I create portioned matview (same partition key/condition of the table), using local indexes, on different tablespaces. Now if I drop a particular partition of the table and in turn the partition on matview, how will it affect (like will the matview be invalidated and refresh will fail).
Please help me.
1. It depends. Partition change tracking (PCT) enables fast refresh of a materialized view after partition maintenance:
create table t (
x primary key,
y
) partition by range (x)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40)
) as
select rownum, mod(rownum, 3) from dual
connect by level < 40;
create materialized view log on t
with rowid, primary key, sequence (y) including new values;
create materialized view mv
refresh fast on demand as
select x, count(*) from t
group by x;
select count(*) from mv;
COUNT(*)
39
select count(*) from t;
COUNT(*)
39
alter table t drop partition p1 update indexes;
select count(*) from t;
COUNT(*)
30
exec dbms_mview.refresh('mv', 'F');
select count(*) from mv;
COUNT(*)
30 But only if the partition column is in the select/group by of the MV!
drop table t purge;
drop materialized view mv;
create table t (
x primary key,
y
) partition by range (x)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40)
) as
select rownum, mod(rownum, 3) from dual
connect by level < 40;
create materialized view log on t
with rowid, primary key, sequence (y) including new values;
create materialized view mv
refresh fast on demand as
select y, count(*) c from t
group by y;
select count(*), sum(c) from mv;
COUNT(*) SUM(C)
3 39
select count(*) from t;
COUNT(*)
39
alter table t drop partition p1 update indexes;
select count(*) from t;
COUNT(*)
30
exec dbms_mview.refresh('mv', 'F');
ORA-32313: REFRESH FAST of "CHRIS"."MV" unsupported after PMOPs
select count(*), sum(c) from mv;
COUNT(*) SUM(C)
3 39 There are a few other restrictions which you can read about in the docs:
http://docs.oracle.com/database/122/DWHSG/advanced-materialized-views.htm#DWHSG00324 2. Again, it depends. If PCT is possible and you just drop the partition from the table, refreshes can continue as normal:
drop table t purge;
drop materialized view mv;
create table t (
x primary key,
y
) partition by range (x)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40)
) as
select rownum, mod(rownum, 3) from dual
connect by level < 40;
create materialized view log on t
with rowid, primary key, sequence (y) including new values;
create materialized view mv
partition by range(x) (
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40)
)
refresh fast on demand as
select x, count(*) c from t
group by x;
create index ic on mv (c) local;
alter table t drop partition p1 update indexes;
exec dbms_mview.refresh('mv', 'F');
select count(*) from mv;
COUNT(*)
30
select count(*) from t;
COUNT(*)
30
insert into t values (1, 1);
commit;
exec dbms_mview.refresh('mv', 'F');
select count(*) from mv;
COUNT(*)
31
select count(*) from t;
COUNT(*)
31But try and drop the partition from the MV and things get messy:
alter table mv drop partition p1;
insert into t values (2, 2);
commit;
exec dbms_mview.refresh('mv', 'F');
ORA-32320: REFRESH FAST of "CHRIS"."MV" unsupported after container table PMOPs
select count(*) from mv;
COUNT(*)
30
exec dbms_mview.refresh('mv', 'C');
select count(*) from mv;
COUNT(*)
32
select count(*) from t;
COUNT(*)
32 MOS note 1620877.1 has the following workaround for this:
alter table t drop partition p2 update indexes;
exec dbms_mview.SET_I_AM_A_REFRESH(TRUE);
alter materialized view mv drop partition p2;
exec dbms_mview.SET_I_AM_A_REFRESH(FALSE);
lock table t in exclusive mode;
alter materialized view mv consider fresh;
-- validate data in the tables and MV are the same!!
commit;
insert into t values (2, 2);
commit;
exec dbms_mview.refresh('mv', 'F');
select count(*) from mv;
COUNT(*)
21
select count(*) from t;
COUNT(*)
21 But note you're locking the base table(s) while doing this. So this may not be practical on a production system!