6 Replies Latest reply on Oct 10, 2013 4:18 PM by rp0428

    Nologging - Mviews

    n2813

      Hi,

       

      Please let me know if I can create a materialized view fast refresh on commit with nologging.

       

       

      I am aware that fast refresh needs a mv log.But is there a way a log can be avoided.

       

       

      I am working on 10g.

       

       

       

      Thanks in advance

        • 1. Re: Nologging - Mviews
          Chris Hunt

          I am aware that fast refresh needs a mv log.

          Good.

          But is there a way a log can be avoided.

          No, for the reason that you've just said that you're aware of.

           

          Fast refreshing means that Oracle only applies changes to the MV that have arisen since the last time it was refreshed. The only way it can know what those changes are is to log them somehow. That's why you need a MV log.

          • 2. Re: Nologging - Mviews
            n2813

            Thanks for the reply.

             

            Please let me know the effect of log on the performance of mv.

             

            Complete refresh will surely take time than a fast refresh.So was thinking to create a fast refresh mview but was unsure if a log can be avoided (reason performance)

             

            And also while creating a mv with complete refresh i am not specifying log/nolog so when does the nologging comes into picture.

            • 3. Re: Nologging - Mviews
              SomeoneElse

              > Complete refresh will surely take time than a fast refresh.

               

              Well...maybe.  It would depend on the activity, number of changes, etc.

               

              On the other hand if you do a complete refresh with atomic = false, that will do a truncate and an insert/append.  That might be pretty fast.

               

              Only testing will tell you for sure.

               

              But first you should see if your mv is fast refreshable.  Not all are.

               

              The procedure dbms_mview.explain_mview can tell you if an mview is fast refreshable (among other things).

              1 person found this helpful
              • 4. Re: Nologging - Mviews
                rp0428
                Please let me know if I can create a materialized view fast refresh on commit with nologging.

                Yes - you can. The NOLOGGING refers to the REDO logging and applies when the MV is first created or when a complete, NON-ATOMIC refresh is performed.

                I am aware that fast refresh needs a mv log.But is there a way a log can be avoided.

                I am working on 10g.

                You are confusing some responders with your use of the word 'log'.

                 

                Fast refresh requires an MV log and will always generate REDO log information.

                 

                If the MV is defined as NOLOGGING then you can avoid REDO logging when a COMPLETE NON-ATOMIC refresh is performed.

                 

                See this AskTom blog for an explanation by Oracle ACE and author Tom Kyte:

                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:616795500346622064

                1 person found this helpful
                • 5. Re: Nologging - Mviews
                  n2813

                  yeah actually I am bit confused about the logging/nologging and mv log.Could you please correct if I am going wrong

                  mv log records the dml changes made to the table and is used for refreshing the data in a fast refresh scenario.

                   

                  as far as I understood during complete refresh a delete +insert is happening so the redo log information(of the delete) is stored in the log (logging clause used during create mv).

                  In order to avoid the log to be created we can use nologging .

                   

                  Atomic refresh will do a truncate + insert so no log is generated.

                   

                  I will try a sample to check the working.

                   

                  Thanks in advance.

                  • 6. Re: Nologging - Mviews
                    rp0428

                    That pretty much sums it up. You don't even need to create an MV log if you only do complete refreshes.