materialized view creation is fast but refresh takes hours

Question

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.

Does anyone have any clue about how it would 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 [email protected] Fcr
 INNER JOIN [email protected] Cr
    ON Fcr.w_Sol_Chng_Id = Cr.w_Sol_Chng_Id
 INNER JOIN [email protected] Bsr
    ON Fcr.w_Sol_Chng_Id = Bsr.w_Sol_Chng_Id
 INNER JOIN [email protected] 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

Answer 1

I would personally NOT use the scheduler built into the mat view CREATE statement (start with ... next clause).

The main reason (for me) is that you cannot declare the refresh non-ATOMIC this way (at least I haven't found the syntax for this at CREATE time). Depending on your refresh requirements and size, this can save A LOT of time.

I would use dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false). This would:

1. Truncate MY_MATVIEW snapshot table
2. Insert append into MY_MATVIEW table

If you use the next clause in the create statement, it will setup an atomic refresh, meaning it will:

1. Delete * from MY_MATVIEW
2. Insert into MY_MATVIEW
3. Commit

This will be slower (sometimes much slower), but others can still query from MY_MATVIEW while the refresh is occurring. So, depends on your situation and needs.