Compares refreshing materialized view with NOLOGGING

1) Refresh Materialized view with NOLOGGING

-- Capture redo size before refreshing materialized view
SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 148331524

-- Check if the materialized view has nologging turned on
SQL> SELECT logging FROM user_tables WHERE table_name = ‘SALES_MV’;

LOG
—
NO

-- Passing atomic_refresh will refresh the table with nologging if the materialized view is set to nologging
SQL> exec dbms_mview.refresh(‘sales_mv’, atomic_refresh=>false);

PL/SQL procedure successfully completed.

-- Capture redo size after refreshing materialized view
SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 157719208

2) Refresh materalized view which has NOLOGGING turned on without using atomic_refresh option which defaults to true

-- Capture redo size after refreshing materialized view

SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 157719208

SQL> exec dbms_mview.refresh(‘sales_mv’);

PL/SQL procedure successfully completed.

SQL> SQL> SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 278564284

Compare Size using atomic refresh set to false (NOLOGGING is used) – 9,387,684
Compare Size using atomic refresh set to true – 12,0845,076
As you see the amount of redo generated is less when table is NOLOGGING and atomic_refresh is used