Hi,
I am testing a mview fast refresh for one of my project and here is what I have done so far:
a) We have a production table, lets call it TAB_A and this is one of our busiest table in OLTP environment, receiving millions of DML's in hour.
b) I created a MView Log on this table using:
create materialized view log on tab_a with rowid, primary key, commit scn, sequence (some join columns) including new values;
c) Then I created a TAB_A_N empty table with same structure as TAB_A and created MView on top of it using prebuilt table option, using:
create materialized view tab_a_n on prebuilt table refresh fast on demand
as
select /*+ APPEND PARALLEL(tab_a, 32) */ * from tab_a
where
(
case
when a is null and b is null then c
when a is null and b is not null then b
when a is not null and b is null then a
when a is not null and b is not null then greatest(a, b)
end >= '02-15-2011'
)
OR
(
e = 'p' and f is null
)
;
d) And did a complete refresh which took 10 hours.
Now the question is, after doing 1 complete refresh when I am running the FAST refresh, it doing checking the updated rows from MView log and DELETING them from TAB_A_N and then inserting the same rows from TAB_A to TAB_A_N. And because these tables have billions of rows, that DELETE is taking forever to finish and it's not FAST by any mean. So why it's doing a DELETE? Why not just change the actual records in TAB_A_B using the stored change_vector from mview log? Or is it the normal operation of FAST refresh to do DELETE + INSERT??
We are using 11.2.0.3 (3 node RAC) on AIX.
Thanks
Daljit Singh