Refreshing Materialized View

[lorenchu]

I am currently using oracle 9i. When refreshing the materialized views, about 5Gb of archive logs will be generated. However, about 500Mb of archive logs generated if the same materialized view were refreshed in oracle 7.3.4. All the setting are the same in both database. Is there any parameter that I can set to disable the logging when refreshing the materialized view? Or anything I should set to reduce the archive logs generated?

[slimdave]

Are you doing fast refrsh or complete? Is the refresh part of a group?

[julian]

Originally posted by lorenchu
I am currently using oracle 9i. When refreshing the materialized views, about 5Gb of archive logs will be generated. However, about 500Mb of archive logs generated if the same materialized view were refreshed in oracle 7.3.4. All the setting are the same in both database. Is there any parameter that I can set to disable the logging when refreshing the materialized view? Or anything I should set to reduce the archive logs generated?

This is a very interesting question. For 3 years I have been working daily with MVs and here is what I know on that topic:

You are probably talking about C-refresh. You would not have that problem with F-refresh.

Now if you mean C-refresh MVs, then if you created them in the following fashion (NOLOGGING is UNRECOVERABLE in 7.3.4):

CREATE MATERIALIZED VIEW lorenchu
NOLOGGING
REFRESH COMPLETE
AS SELECT * from ...;

only DML on the table WILL NOT be sent to the redo logs upon creation. Afterwards you cannot avoid all the redo.

The refresh process is not aware of NOLOGGING! Even if the snapshots are created with nologging, dbms_refresh.refresh doesn't use the "TRUNCATE" while refreshing (group of multiple) snapshots since it has to preserve transactional consistency of all snapshots being refreshed. Instead it uses "DELETE".

Much more REDO is generated if you use groups, so do not use them!

There is one way to reduced the REDO a bit. Drop all indexes on the MVs before the refresh process and recreate them in NOLOGGING mode afterwards.

Conclusion: LOGGING/NOLOGGING is to much relevant with MVs.

[pando]

I am not 100% sure, but I think I remembered seeing a trace file generated by tracing a MV Complete refresh process and it used insert /*+ APPEND */

may be you can trace your process and see what exactly it's doing