Wednesday, August 18, 2010

Speed up mview refresh with atomic_refresh=>false

SQL> create materialized view mv_sales
2 refresh complete
3 enable query rewrite
4 as
5 select * from sales;

Materialized view created.

SQL> select count(*) from mv_sales;


SQL> exec dbms_mview.refresh(list=>'DONGHUA.MV_SALES',atomic_refresh=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:01:34.24
SQL> exec dbms_mview.refresh(list=>'DONGHUA.MV_SALES',atomic_refresh=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.09

When atomic_refresh=>true, Oracle performs deleting from MView table.
When atomic_refresh=>false, Oracle trunctates the MView table.