4 Replies Latest reply: May 21, 2012 1:55 AM by 645740 RSS

    Materialized View Concept

    795902
      Hi,

      I am a newbie in Oracle materialized view. What is the difference between materialized view and materized view log? I setup the materialized view by the following steps in Oracle 11gR2.

      Step 1:
      On master site, I create user TEST and create a testing table by
      CREATE TABLE XYZ
      (
      XX NUMBER,
      YY VARCHAR2(50 BYTE) ,
      ZZ NUMBER
      )

      LOGGING
      NOCACHE
      NOPARALLEL;

      Step 2:
      On destination site (Oracle 11gR2), I create a dblink (LinkABC), user TEST1 and create a materialized view by
      CREATE MATERIALIZED VIEW MV_XYZ
      TABLESPACE USERS
      NOCACHE
      LOGGING
      NOPARALLEL
      REFRESH FORCE
      START WITH TO_DATE('12-MAR-2012 12:18:04','dd-mon-yyyy hh24:mi:ss')
      NEXT SYSDATE + 1/144
      AS
      SELECT "XX","YY","ZZ"
      FROM XYZ@LinkABC;
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      After the setup, I can select the data from MV_XYZ when table XYZ is updated.

      Now, I have the following questions
      1. Why I do setup the materialized view log and it works?
      2. What is the purpose of materialized view log (CREATE MATERIALIZED VIEW LOG ON ......)? Where I have to apply (master or destination site)?
      3. When will I apply utlxmv.sql and DBMS_MVIEW.EXPLAIN_MVIEW(.....)?

      Anyone can I help to clarify? Thank you so much!

      Ming
        • 1. Re: Materialized View Concept
          NikolayIvankin
          user12360077 wrote:

          Now, I have the following questions
          1. Why I do setup the materialized view log and it works?
          When you want to use FAST REFRESH of MView.
          2. What is the purpose of materialized view log (CREATE MATERIALIZED VIEW LOG ON ......)? Where I have to apply (master or destination site)?
          It contains records about all MViews which refer to master table, those records contains info about if MView got changed row or not.
          MView Log is created for a table at master site.
          • 2. Re: Materialized View Concept
            Hemant K Chitale
            A Materialized View Log ("MV Log") is on the source table. It captures information of all DML that is executed against the table.
            The Materialized View ("MV") Refresh can be executed as a FAST refresh by reading the MV Log and identifying rows that have undergone since the last DML and then replicating only those selected rows -- instead of doing a Full Scan of the source table and replicating all the rows of the source table each time.

            Hemant K Chitale
            • 3. Re: Materialized View Concept
              Leo Leung-OC
              Hi Hemant K Chitale,

              Thanks for your explaination. Now I am facing the problem of resource exhaustion in destination size. The case is when the master listener stop, the resource of destination site will be exhausted that lead destination listener cannot function at all. The master site is now using 11.2.0. and destination site is running Windows server with 9.2.0.8.0. Any idea? How can I check the materialized view status when no log generate (force update)? Thx!

              Ming
              • 4. Re: Materialized View Concept
                645740
                Hi Hemant,

                I want to continue in this thread which is related to mview refresh.

                Current Situation:: I have a table 'Tbl_Voicechat' on Destination site. M-view Log already on Table A and also there is an index on SNAPTIME$$. A mview TBL_VOICECHAT_NEW1 is running on table using the db link.

                When I am recreating all this things it is refreshing fine for few refreshes. But After 1-2 Days its is being like hangs.

                Can you please tell me, what to do in which area for this refreshment problems.

                Thanks in Advance

                Pradeep Sharma