暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

askTom 2017-09-05
311

问题描述

嗨,汤姆,

根据您对线程的回答“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   
复制


但请注意,您在执行此操作时会锁定基本表。因此,这在生产系统上可能不切实际!
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论