6 Replies Latest reply on Feb 1, 2011 8:51 PM by user13148231

    How to make materialized view fast refresh parallel

      We have Oracle on Redhat 5.2. We use a dozen of fast refresh materialized views in our application.
      From time to time MV refresh takes longer time than the refresh interval.

      One of way to improve performance of MV refresh is to make the refresh parallel, e.g.
      - Use parallel DML - Oracle author Michael Armstrong Smith notes "I've done parallel materialized view refreshing
       on tables recently and improved the load times considerably. Rather than having one load which took 2 hours,
       I run 4 parallel loads, one for each partition. The length of time for the whole process is now determined by
       how long the biggest partition takes to load. In my case, this is 40 minutes, with two 30 minute loads and one
       20 minute load.  Overall I am saving 1 hour 20 minutes. I can now add further partitions and do the same thing.
       My only limitation is the parallel loads because I don't have unlimited processing power.
      {code} My master tables are not partitioned, but think parallel will still better than non-parallel.
      Now the question, how to make refresh parallel. According to the paragraph cited above, Use parallel DML appears doing the job. Can someone  confirm that.
      We use {code}
      alter materialied view MV_OFFENSE parallel REFRESH FAST start with sysdate next sysdate+ 1/24;
      {code}to schedule refresh. Does the parallel clause here parallels query the MV or refresh the MV?
      Any other way to make MV refresh parallel?