在过去的几个小时里,我已经阅读了很多关于在Oracle中刷新MV的内容,但我仍然无法找到问题的答案.想象一下,我在一个带有更改日志的表顶部有一个MV视图.这个MV中有三条记录:
COL_ID, COL1
1, "OLD"
2, "OLD"
3, "OLD"
现在假设COL1的值已经变为“EDITED”,表示用于创建MV的表中的记录1.我想执行快速的就地刷新以尽快更新MV.在具有大约50M记录的现实生活示例中,这将花费大约3分钟来刷新.
想象一下情况.
>刷新过程仍在运行(仍有MV中尚未修改的记录).
> Neverthelss,已经处理了ID = 1的记录,因此它在MV中具有“EDITED”的值.
>在另一个会话中,执行对MV的查询以获取ID = 1的记录值.
结果将记录值“OLD”或“EDITED”?
我理解,因为这是刷新机制处理此记录后的就地刷新,物化视图中的值将反映原始表中的值(“EDITED”).但有没有任何机制(如撤消日志)会使整个刷新原子?我的意思是,除非对物化视图进行所有修改(除非刷新过程完成),否则如果用户查询在刷新过程中已经修改的行,他/她将被呈现给旧的,缓存价值 – 改变之前.
我认为这个bevahiour对于不合适的刷新是正确的,但是由于前者似乎在时间消耗方面更有效率,我很好奇这对于就地转换是否也是如此.如果不是默认情况下,有没有办法强制这种原子行为?
—– [编辑]
我在测试后运行测试,看看在刷新过程中,我从物化视图得到的结果是否会逐渐变化.
-- create table
create table MV_REFRESH_ATOMICITY_TEST
(
id NUMBER,
value NUMBER
)
-- populate initial data
-- delete from MV_REFRESH_ATOMICITY_TEST
declare
begin
for i in 1..10000000 loop
insert into MV_REFRESH_ATOMICITY_TEST values(i, 0);
end loop;
end;
-- check if equal zero and 1M
select sum(value) from MV_REFRESH_ATOMICITY_TEST
select to_char(count(*),'999,999,999') as COUNT from MV_REFRESH_ATOMICITY_TEST
-- create mv logs on the table
-- drop materialized view log on MV_REFRESH_ATOMICITY_TEST;
create materialized view log on MV_REFRESH_ATOMICITY_TEST with rowid;
-- create mv on top
-- drop materialized view MV_REFRESH_ATOMICITY_TEST_MV
create materialized view MV_REFRESH_ATOMICITY_TEST_MV
refresh fast on demand with rowid
as
select
fact.*,
fact.ROWID "FACT_ROWID"
from
MV_REFRESH_ATOMICITY_TEST fact
-- check if equals zero and 10M
select sum(value) from MV_REFRESH_ATOMICITY_TEST_MV
select to_char(count(*),'999,999,999') as COUNT from MV_REFRESH_ATOMICITY_TEST_MV
-- change value for first million records, 1 milion records in the middle, last milion of records
update MV_REFRESH_ATOMICITY_TEST set value = 1 where id between 1 and 1000000
update MV_REFRESH_ATOMICITY_TEST set value = 1 where id between 5000001 and 6000000
update MV_REFRESH_ATOMICITY_TEST set value = 1 where id between 9000001 and 10000000
-- check if equals 3.000.000
select to_char(sum(value),'999,999,999') as "SUM" from MV_REFRESH_ATOMICITY_TEST
-- check if equals 3.000.000
select to_char(count(*),'999,999,999') from MLOG$_MV_REFRESH_ATOMICITY;
--select * from MLOG$_MV_REFRESH_ATOMICITY;
-- while refreshing mv
-- exec dbms_mview.refresh('MV_REFRESH_ATOMICITY_TEST_MV', 'F');
-- below sum should be equal 0
select
( select sum(value) from MV_REFRESH_ATOMICITY_TEST_MV ) "SUM",
( select count(*) from MV_REFRESH_ATOMICITY_TEST_MV ) "NUMBER OF RECORDS"
from dual
因此,我通过不断执行最后一个选择语句而发现的是,唯一一次SUM值发生了变化,它已经被3M改变了,这意味着所有记录都已经一次性更改 – 原子.
尽管如此,我并不是100%肯定我可以相信这个实验,因为在某些时候执行这些选择查询需要大约40秒.整个刷新语句需要911秒才能执行.
[编辑]
这个问题已被标记为this thread的可能副本.另一个线程确实响应了类似的问题,但是对于完全刷新,我的理解是以与快速刷新非常不同的方式执行,这是这里的情况.因此,我不确定这里是否可以应用相同的解释.
A materialized view’s data does not necessarily match the current data of its master table or master materialized view at all times. A materialized view is a transactionally (read) consistent reflection of its master as the data existed at a specific point in time (that is, at creation or when a refresh occurs).
Oracle为物化视图组提供了更高的读取一致性:
To preserve referential integrity and transactional (read) consistency among multiple materialized views, Oracle has the ability to refresh individual materialized views as part of a refresh group. After refreshing all of the materialized views in a refresh group, the data of all materialized views in the group correspond to the same transactionally consistent point in time.