Oracle 实体化视图刷新和删除分区

嗨，汤姆，

根据您对线程的回答“https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534336000346923525",我脑海中冒出两个问题。你能帮我吗？

1.如果我想在分区表上创建一个非分区的matview怎么办如果我删除表的任何分区，它是否会使matview无效，刷新将失败？如果是，我该如何克服它，以便即使我删除任何特定分区也不会使matview无效。

2.如果我创建portited matview (表的相同分区键/条件)，使用本地索引，在不同的表空间上现在，如果我删除表的特定分区，然后在matview上的分区，它将如何影响 (就像matview将失效和刷新将失败)。

请帮帮我。

1.这取决于。分区更改跟踪 (PCT) 可在分区维护后快速刷新物化视图:

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 

但前提是分区列在MV的select/group by中!

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  

您可以在文档中阅读其他一些限制:

http://docs.oracle.com/database/122/DWHSG/advanced-materialized-views.htm#DWHSG00324

2.同样，这取决于。如果PCT是可能的，并且您只是从表中删除分区，刷新可以继续正常进行:

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(*)  
31

但是尝试从MV中删除分区，事情变得混乱:

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对此有以下解决方法:

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   

但请注意，您在执行此操作时会锁定基本表。因此，这在生产系统上可能不切实际!