I have read quite a lot for past few hours about refreshing MV in Oracle, but I cannot still find an answer to my question. Imagine I have a MV view on top of a table with change logs. So that there are three records in this MV:
COL_ID, COL1
1, "OLD"
2, "OLD"
3, "OLD"
Now let's say the value for COL1 has changed to "EDITED" for record 1 in the table used to create MV. I want to perform fast, in-place refresh to update MV as fast as possible. In real life example with around 50M records, this would take around 3 minutes to refresh.
Imagine situation.
- Refresh process is still running (there are still records that have not been amended in the MV).
- Neverthelss, record with ID = 1 has been already processed so that it has value of "EDITED" in MV.
- In another session, a query to MV is executed to get the value of record with ID = 1.
As the result will record with value "OLD" or "EDITED" be given?
I understand that because this is an in-place refresh after this record is processed by refresh mechanism the value in the materialized view will reflect the value in origin table ("EDITED"). But is there any mechanism (like undo logs) that would make the whole refresh atomic? And by this I mean that unless all amendments are done to the materialized views (unless the refresh process is done) if user queries for rows that have been modified in the process of undergoing refresh, he/she will be presented to an old, cached value - before the change.
I presume that this bevahiour is true for out-of-place refresh, but since the former seems to be working way more efficient in terms of time consumption I was curious whether this is also true for the in-place transformation. If not by default, is there any way to force this atomic behaviour?
----- [EDIT]
I run the test following test to see if during the refresh process the results I get from the materialized view will change gradually.
-- 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
So what I discovered by constantly executing the last select statement was that the only time that the SUM value changed, it already changed by 3M, meaning all the records have been changed in one go - atomically.
Nevertheless, I am not 100% percent sure I can trust this experiment as at some point it took around 40s to execute these select queries. The whole refresh statement took 911s to execute.
[EDIT]
This question has been marked as a possible duplicate of this thread. The other thread does respond to a similar problem, but for a complete-refresh which to my understanding is performed in very different way than fast-refresh which is the case here. Therefore I am not sure whether the same explanation can be applied here.
