问题描述
嗨,汤姆,
根据您对线程的回答“https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534336000346923525",我脑海中冒出两个问题。你能帮我吗?
1.如果我想在分区表上创建一个非分区的matview怎么办如果我删除表的任何分区,它是否会使matview无效,刷新将失败?如果是,我该如何克服它,以便即使我删除任何特定分区也不会使matview无效。
2.如果我创建portited matview (表的相同分区键/条件),使用本地索引,在不同的表空间上现在,如果我删除表的特定分区,然后在matview上的分区,它将如何影响 (就像matview将失效和刷新将失败)。
请帮帮我。
根据您对线程的回答“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) 可在分区维护后快速刷新物化视图:
但前提是分区列在MV的select/group by中!
您可以在文档中阅读其他一些限制:
http://docs.oracle.com/database/122/DWHSG/advanced-materialized-views.htm#DWHSG00324
2.同样,这取决于。如果PCT是可能的,并且您只是从表中删除分区,刷新可以继续正常进行:
但是尝试从MV中删除分区,事情变得混乱:
MOS note 1620877.1对此有以下解决方法:
但请注意,您在执行此操作时会锁定基本表。因此,这在生产系统上可能不切实际!
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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
Oracle RAC 一键安装翻车?手把手教你如何排错!
Lucifer三思而后行
592次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布,如何快速升级和安装?
Lucifer三思而后行
556次阅读
2025-04-18 14:18:38
XTTS跨版本迁移升级方案(11g to 19c RAC for Linux)
zwtian
479次阅读
2025-04-08 09:12:48
Oracle数据库一键巡检并生成HTML结果,免费脚本速来下载!
陈举超
470次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
456次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
429次阅读
2025-04-22 00:13:51
Oracle 19c RAC更换IP实战,运维必看!
szrsu
428次阅读
2025-04-08 23:57:08
【活动】分享你的压箱底干货文档,三篇解锁进阶奖励!
墨天轮编辑部
413次阅读
2025-04-17 17:02:24
火焰图--分析复杂SQL执行计划的利器
听见风的声音
357次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
355次阅读
2025-04-15 14:48:05