I am using a materialized view, and I cant set it to fast refresh because some of the tables are from remote database which does not have materialized view log.
When I create the materialized view, it took like 20 30 seconds. however when I was trying to refresh it. It took more than 2 3 hours. and total number of records are only around 460,000.
I understand that when the view refreshes, it would take extra step like purge the table however I am not sure why it made so much difference in execution time perspective...
Does the dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) do the same exact insert as when I created the view?
Could anyone please let me know how would this happen? Thanks,
Code looks like as following
create materialized view MY_MV1
refresh force on demand
start with to_date('20-02-2013 22:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate)+1+22/24
as
( SELECT Nvl(Cr.Sol_Chng_Num, ' ') AS Change_Request_Nbr,
Nvl(Sr.Sr_Num, ' ') AS Service_Request_Nbr,
Nvl(Sr.w_Org_Id, 0) AS Org_Id,
Fcr.rowid,
Cr.rowid,
Bsr.rowid,
Sr.rowid,
SYSDATE
FROM Dwadmin.f_S_Change@DateWarehouse.World Fcr
INNER JOIN Dwadmin.d_S_Change@DateWarehouse.World Cr
ON Fcr.w_Sol_Chng_Id = Cr.w_Sol_Chng_Id
INNER JOIN Dwadmin.b_S_Change_Obl@DateWarehouse.World Bsr
ON Fcr.w_Sol_Chng_Id = Bsr.w_Sol_Chng_Id
INNER JOIN Dwadmin.d_S_Rec@DateWarehouse.World Sr
ON Sr.w_Srv_Rec_Id = Bsr.w_Srv_Rec_Id
WHERE Sr.Sr_Num !='NS'
);
I have tried to use dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) but it still took 141 mins to run... vs 159 mins without atomic_refresh=>false
Edited by: 986006 on Feb 20, 2013 2:15 PM