5 Replies Latest reply: Jun 17, 2013 2:43 AM by Alvaro RSS

    Mview refresh how

    902332

      hi all,

       

      if we do materialized view refresh, how it will get refresh. does mview pull the data from master table or master table only will push the data in the mview.

       

      thank you

        • 1. Re: Mview refresh how
          Alvaro

          Hello there,

           

          How a  Materialized View gets refresh is up to you, the following options are available as of 11gR2:

           

          1) Manual Refresh:

          1.1) You can do a complete manual refresh. You must manually call the API to do this. A Complete refresh re-builds the entire MVIEW, it runs the whole definition of the view on each refresh.

          1.2) You can do a fast-refresh. Fast refreshes rely on supplemental mview logs which tracke the changes on the base tables to incrementally apply the changes to the MVIEW. When doing this manually, you must also call the API.

           

          2) Automatic Refresh:

          2.1) You can set a complete refresh through a database job.

          2.2) You can set a fast-refrsh through a database job.

          2.3) You can set a complete or fast-refresh after each commit on the base tables(s).

          2.4) You can set a complete or fast-refresh after a set interval.

          • 2. Re: Mview refresh how
            902332

            yes, you are right.

             

            my doubt is, we have a table called tb1 on server serv1 and materialized view mv1 on serv2. now if we do refresh how data will get update. data will automatically from tb1 to mv1 or mv1 will fetch the data from tb1.

             

            thank you

            • 3. Re: Mview refresh how
              Alvaro

              When a commit on the tb1 table on the serv1 occurs it will signal the mview on serv2 to fetch the data. That is, assuming you've created a mview with refresh fast on commit.

              • 4. Re: Mview refresh how
                Hemant K Chitale

                Such a refresh (across databases) can only be defined as a REFRESH ON DEMAND.   Cross-database refreshes cannot be ON COMMIT.

                 

                On serv2 you might either have a

                a) DBMS_JOB job to execute the refresh

                b) DBMS_SCHEDULER job to execute the refresh

                c) Calls to DBMS_MVIEW.REFRESH initiated through an application front-end   (e.g. chosen by a user)

                d) Refresh scheduled through CRON or any third party scheduler that calls an SQL script that executes DBMS_MVIEW.REFRESH.

                 

                 

                Hemant K Chitale


                • 5. Re: Mview refresh how
                  Alvaro

                  That's correct, thanks for reminding me of that restriction!