You Asked
Hi Tom,
I was at your conference in MTL in february. It was a pleasure to finally meet you.
My question is regarding the refresh of MVs. We are using Discoverer with a 9i Database
in Archivelog mode. The problem is when we need to refresh our Materialized Views, a
lot of redo logs are generated. It is somewhow strange to me since I though the refresh
was done with an append hint. The Materialized views have been created with the
NOLOGGING option.
CREATE MATERIALIZED VIEW ....
AS SELECT /* APPEND */...
We are using the DBMS_MVIEWS.REFRESH procedure:
exec DBMS_MVIEW.REFRESH('TBGADM.TBGADM_MV_44','C','',FALSE,FALSE,0,0,0,TRUE);
This refresh generated around 679 MB of REDO (the MV weights around 200MB). How can we
accomplish this refresh using less ARCHIVE LOGS?
Can you shed some light on this?
Rich
and we said...
If you do incremental refreshes, the MV would be refreshed using conventional SQL.
If this is a single table, complete refresh, it will use:
truncate
insert /*+ append */
which DOES NOT negate redo, redo will be generated -- append bypasses UNDO on the table
data only (and hence minimizes redo, but does not bypass it).
Only if it was a NOLOGGING object would the append bypass redo and then only for the
table -- but not any indexes on the table (if this MV has indexes, the will always be
logged regardless, they have to be)
If this table is part of a refresh group, it'll be done with a DELETE (redo) plus insert
(redo).
But here is what you should be seeing if this is a single table (not in a refresh group)
and how you can mimimize the redo generated and what'll happen if you have indexes on
this:
ops$tkyte@ORA920PC> select count(*) from big_table;
COUNT(*)
----------
1000000
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> drop materialized view big_table_mv;
Materialized view dropped.
ops$tkyte@ORA920PC> create materialized view big_table_mv as select * from big_table;
Materialized view created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> column value new_val V
ops$tkyte@ORA920PC> set verify off
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'redo size'
5 /
NAME VALUE
------------------------------ ----------
redo size 153976776
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'redo size'
5 /
NAME VALUE DIFF
------------------------------ ---------- ----------------
redo size 330108116 176,131,340
so, in archivelog mode, about 176m of redo generated for that -- no indexes or
anything. we can drastically reduce that:
ops$tkyte@ORA920PC> alter table big_table_mv nologging;
Table altered.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'redo size'
5 /
NAME VALUE DIFF
------------------------------ ---------- ----------------
redo size 387304376 57,196,260
using nologging on the MV table -- but -- if you have indexes:
ops$tkyte@ORA920PC> create index big_table_mv_idx on
big_table_mv(owner,object_type,object_name);
Index created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'redo size'
5 /
NAME VALUE
------------------------------ ----------
redo size 444214684
ops$tkyte@ORA920PC> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'redo size'
5 /
NAME VALUE DIFF
------------------------------ ---------- ----------------
redo size 709972648 265,757,964
and the only way to reduce that would be to use a custom job to refresh the MV and the
job would:
a) disable indexes
b) call refresh
c) rebuild indexes with no logging
But honestly, 6/700meg of redo isn't but a drop in the proverbial bucket (eg: it really
isn't "that much" in the grand scheme of things) -- but those are the things you can do
about it.
Bear in mind, you need to backup any files with unrecoverable changes right after doing
them!