title: materialized view creation is fast but refresh takes hours
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Does anyone have any clue about how it would happen?</p>
  
  <p>Thanks</p>
  
  <p>Code looks like as following</p>
  
  <pre><code>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 &lt;&gt; 'NS'
  );
  </code></pre>
  
  <p>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</p>