4 Replies Latest reply on Jun 24, 2014 7:26 PM by Hector Glz

    Create Materialized View Refresh Fast On Commit Peoplesoft (SELECT MAX(L_Ed.Effdt)

    Hector Glz

      Hello All,

       

      I am working on a Lab, prior to do it on Dev.  I took an HR schema, and build a couple of tables, 

       

      CREATE TABLE foo(foo NUMBER, CONSTRAINT foo_pk PRIMARY KEY(foo));

      CREATE TABLE bar(foo NUMBER, bar NUMBER, CONSTRAINT bar_pk PRIMARY KEY(foo, bar));

       

      CREATE MATERIALIZED VIEW LOG ON foo WITH ROWID;

      Create Materialized View Log On Bar With Rowid;

       

      CREATE MATERIALIZED VIEW foo_bar

        NOLOGGING

        CACHE

        BUILD IMMEDIATE

        REFRESH FAST ON COMMIT  AS SELECT foo.foo,

                                          bar.bar,

                                          foo.ROWID AS foo_rowid,

                                          bar.ROWID AS bar_rowid

                                     From Foo, Bar

                                    WHERE foo.foo = bar.foo;

       

      This was token from: sql - Oracle - How to create a materialized view with FAST REFRESH and JOINS - Stack Overflow

      It works, but then I tried this:

      I imported Peoplesoft's Location Table in HR Schema and replicated above: CREATE MATERIALIZED VIEW LOG ON Ps_Location_Tbl WITH ROWID;

       

      What I need is to place on MV the lastest date of a given Location, like this:

       

      CREATE MATERIALIZED VIEW SAT_LOCATION_MV

        NOLOGGING

        CACHE

        BUILD IMMEDIATE

        Refresh Fast On Commit  As

        Select

        L.ROWID AS LOC_ROWID,

        L.*

      FROM HR.Ps_Location_Tbl L

      WHERE L.Effdt = (SELECT MAX(L_Ed.Effdt) FROM Hr.Ps_Location_Tbl L_Ed

        WHERE L.Setid  = L_Ed.Setid

        AND L.Location = L_Ed.Location

        AND L.Effdt   <= L_Ed.Effdt

        );

       

      If I ran the select, works very well, but when I copy and paste it on the MV definition...

       

      I got this error:

      Error SQL: ORA-12054: "cannot set the ON COMMIT refresh attribute for the materialized view"

      *Cause:    The materialized view did not satisfy conditions for refresh at

                 commit time.

      *Action:   Specify only valid options.

       

      This is, perhaps because I have a subquery to retrieve MAX(EFFDT);

       

      Question:  How could I change the approach to build a MV?

       

      Thanks in advance.

      Hector

        • 1. Re: Create Materialized View Refresh Fast On Commit Peoplesoft (SELECT MAX(L_Ed.Effdt)
          BobLilly


          First you should double-check your SQL--the effective date logic doesn't look right. Normally, you would either use

           

          L_Ed.Effdt <= sysdate

           

          to get the most current row that is not future-dated, or not have any condition on L_Ed.Effdt at all to include future-dated rows. i.e.

           

          WHERE L.Effdt = (

            SELECT MAX(L_Ed.Effdt)

            FROM Hr.Ps_Location_Tbl L_Ed

            WHERE L.Setid  = L_Ed.Setid

            AND L.Location = L_Ed.Location

            );

           

          Second, look at Chapter 18 - SQL Access Advisor in the Oracle 11g Performance Tuning manual (or Chapter 17 in the 10g manual) for an explanation and examples of how to use the DBMS_ADVISOR.TUNE_MVIEW procedure to help in rewriting your SQL so that it is fast refreshable.

          • 2. Re: Create Materialized View Refresh Fast On Commit Peoplesoft (SELECT MAX(L_Ed.Effdt)
            Moazzam

            Restrictions for Materialized Views with Subqueries

            The defining query of a materialized view with a subquery is subject to several restrictions to preserve the materialized view's fast refresh capability.

            The following are restrictions for fast refresh materialized views with subqueries:

            • Materialized views must be primary key materialized views.
            • The master's materialized view log must include certain columns referenced in the subquery. For information about which columns must be included, see "Logging Columns in the Materialized View Log".
            • If the subquery is many to many or one to many, join columns that are not part of a primary key must be included in the materialized view log of the master. This restriction does not apply to many to one subqueries.
            • The subquery must be a positive subquery. For example, you can use the EXISTS condition, but not the NOTEXISTS condition.
            • The subquery must use EXISTS to connect each nested level (IN is not allowed).
            • Each table can be in only one EXISTS expression.
            • The join expression must use exact match or equality comparisons (that is, equi-joins).
            • Each table can be joined only once within the subquery.
            • A primary key must exist for each table at each nested level.
            • Each nested level can only reference the table in the level above it.
            • Subqueries can include AND conditions, but each OR condition can only reference columns contained within one row. Multiple OR conditions within a subquery can be connected with an AND condition.
            • All tables referenced in a subquery must reside in the same master site or master materialized view site.

             

            Materialized View Concepts and Architecture

            • 3. Re: Create Materialized View Refresh Fast On Commit Peoplesoft (SELECT MAX(L_Ed.Effdt)
              Hector Glz

              Hello Moazzam,

               

              I read the info that you kindly sent me.  It was very helpful, sadly I still cannot create a Fast Refresh not Commit Fast Refresh.

               

              I added this to materialized log:

              Alter Materialized View Log On Ps_Location_Tbl Add (Setid,LOCATION,Effdt,Eff_Status);

               

              I tried several ways of refresh and it seems that my query is a complex one.

              • 4. Re: Create Materialized View Refresh Fast On Commit Peoplesoft (SELECT MAX(L_Ed.Effdt)
                Hector Glz

                Bob,

                 

                 

                 

                Variable Task_Cust_Mv Varchar2(30);

                VARIABLE create_mv_ddl VARCHAR2(4000);

                Execute :Task_Cust_Mv := 'MYTASK';

                 

                I try two sentences to evalute Dbms_Advisor.Tune_Mview

                This is the first one, plain and easy:

                Execute :Create_Mv_Ddl := 'CREATE MATERIALIZED VIEW CUST_LOCATION_MV USING INDEX REFRESH ON COMMIT FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT * From Ps_Location_Tbl L WHERE L.EFF_STATUS = ''A'' ';

                Execute Dbms_Advisor.Tune_Mview(:Task_Cust_Mv, :Create_Mv_Ddl);

                 

                As result came an error: QSM-03116: The materialized view is already optimal and cannot be tuned any further

                Which is very good.

                 

                 

                 

                 

                However when I added the self join with Select Max:

                 

                Execute :Create_Mv_Ddl := 'CREATE MATERIALIZED VIEW CUST_LOCATION_MV USING INDEX REFRESH ON COMMIT FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT *  FROM Ps_Location_Tbl L  WHERE L.EFF_STATUS = ''A'' AND L.Effdt  = (SELECT MAX(L_Ed.Effdt) FROM Hr.Ps_Location_Tbl L_Ed WHERE L.Setid  = L_Ed.Setid AND L.Location = L_Ed.Location )';

                 

                Execute Dbms_Advisor.Tune_Mview(:Task_Cust_Mv, :Create_Mv_Ddl);    

                 

                As result is this:

                SM-03112: Invalid CREATE MATERIALIZED VIEW statement

                ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

                 

                 

                Thanks