Running materialized view refresh in parallel

data-warehouseoracleoracle-11g-r2

I have a large materialized view that is refreshed periodically (complete refresh) and I would like to do it in parallel. I can already run the query part in parallel but the problem is (much slower) delete in the beginning of refresh.

I have done the following:

  • set the table and query degree to 6
  • alter session enable parallel dml;
  • run refresh with option "parallelism => 6"

But delete still runs serially. Is there some option I have missed?

Best Answer

Set atomic_refresh to false. It will truncate your MV table instead of deleting records.