Refreshing Materialized View
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Refreshing Materialized View

  1. #1
    Join Date
    Oct 2001
    Posts
    34

    Refreshing Materialized View

    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?
    _____________________________
    Loren

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you doing fast refrsh or complete? Is the refresh part of a group?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Refreshing Materialized View

    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width