Materialized view refresh takes lot of time

Materialized View and Atomic Refresh Parameter

Prior to 10g, a complete refresh of materialized view first truncates a materialized view and then insert the records again . But from 10g onwards, a complete refresh performs a delete operation making the materialized view more available to end users at refresh time and then performs insert operation .

But if we need to completely refresh a materialized view having crores of records, then delete operation will be time consuming as it has to make the materialized view available to the users, but who cares  about the availability if this MV refresh is in a data warehouse window where refresh has to be done quikly regardless of availability.

For doing this there is a parameter named ATOMIC_REFRESH in DBMS_MVIEW.REFRESH package.
Prior to 10g this parameter's value was FALSE, but after 10g this parameter value has been changed to TRUE, so complete refresh of materialized view follows DELETE/INSERT instead of TRUNCATE/INSERT.

Example :- 

SQL> select count(1) from AMIT;

  COUNT(1)
----------
   1020748

SQL> create materialized view mview refresh complete aS SELECT * FROM AMIT;

Materialized view created.

SQL> set timing on 

SQL> update AMIT set ID='dfgh1234' where ID in ('wert1234');

249137 rows updated.

Elapsed: 00:00:07.79
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Now refresh the materialized view with ATOMIC_REFRESH parameter set to TRUE and then FALSE and check the refresh time :-

Atomic Refresh Paramerter set to true i,e case with DELETE :-

SQL> EXEC DBMS_MVIEW.REFRESH(LIST => 'MVIEW', METHOD => 'C', ATOMIC_REFRESH => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.61

it takes around 42 seconds to update materialized view.

Atomic Refresh Paramerter set to FALSE i,e case with TRUNCATE :-

SQL> update AMIT set ID='lkjh1234' where ID in ('dfgh1234');

249137 rows updated.

Elapsed: 00:00:09.33
SQL> commit;

Commit complete.

Elapsed: 00:00:00.07
SQL> EXEC DBMS_MVIEW.REFRESH(LIST => 'MVIEW', METHOD => 'C', ATOMIC_REFRESH => FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.01

it takes 2 seconds only to update the materialized view , compared to Atomic_refresh parameter set to TRUE a significant amount of time has been saved. When you deal with huge amount of data especially in data ware house scenarios compared to delete, truncate will save lot of time.

I hope this article helped you.

Regards,
Amit Rath