5 Replies Latest reply on Sep 23, 2013 1:32 PM by f9smsk

    Fast refresh of a materialized view works incorrect

    f9smsk

      Hi All,

       

      I have created a MV using the sql below:

      create materialized view WLS

      refresh fast

      enable query rewrite

      as

      SELECT Employees.USER_NAME,

             Permissions.ROLE_NAME,

             Vouchers.VOUCHER_ID,

             EmployeeRoles.FROM_DATE,

             EmployeeRoles.TO_DATE,

             VoucherPatterns.rowid   ACC_VOUCHER_PATTERNS_ROW_ID,

             Vouchers.rowid          ACC_VOUCHERS_ROW_ID,

             Ledgers.rowid           ACC_LEDGERS_ROW_ID,

             Employees.rowid         BANK_EMPLOYEES_ROW_ID,

             IncExcUnits.rowid       INC_EXC_UNITS_ROW_ID,

             EmployeeRoles.rowid     BANK_EMPLOYEE_ROLES_ROW_ID,

             ParentRoles.rowid       PARENT_ROLES_ROW_ID,

             Roles.rowid             ROLES_ROW_ID,

             Permissions.rowid       PERMISSIONS_ROLES_ROW_ID,

             RolesMemberships.rowid  ROLES_MEMBERSHIPS_ROW_ID

        FROM ACC_VOUCHER_PATTERNS        VoucherPatterns,

             ACC_VOUCHERS                Vouchers,

             ACC_LEDGERS                 Ledgers,

             BANK_EMPLOYEES              Employees,

             BANK_EMPLOYEE_INC_EXC_UNITS IncExcUnits,

             BANK_EMPLOYEE_ROLES         EmployeeRoles,

             ROLES                       ParentRoles,

             ROLES                       Roles,

             ROLES                       Permissions,

             ROLES_MEMBERSHIPS           RolesMemberships

      WHERE Vouchers.TYPE_VOUCHER_PATTERN_FK =

             VoucherPatterns.VOUCHER_PATTERN_ID

         AND VoucherPatterns.CONFIRM_COUNT = 2

         AND Vouchers.STATE = 'ISU'

         AND Vouchers.LEDGER_FK = Ledgers.LEDGER_ID

         AND Employees.ID = IncExcUnits.BKEM_ID(+)

         AND (((IncExcUnits.INC_EXCLUD_TYPE IS NULL OR

             IncExcUnits.INC_EXCLUD_TYPE != 'EXC') AND

             Ledgers.ORGANIZATION_UNIT_FK = Employees.ORGU_ID) OR

             (Ledgers.ORGANIZATION_UNIT_FK = IncExcUnits.ORGU_ID AND

             IncExcUnits.INC_EXCLUD_TYPE = 'INC'))

         AND EmployeeRoles.BKEM_ID = Employees.ID

         AND ParentRoles.ID = EmployeeRoles.ROLS_ID

         AND ParentRoles.IS_PERMISSION = 'N'

         AND ParentRoles.ROLE_TYPE = 'ORG'

         AND ParentRoles.ID = Roles.ROLS_ID

         AND Roles.IS_PERMISSION = 'N'

         AND Roles.ROLE_TYPE = 'SYS'

         AND Permissions.IS_PERMISSION = 'Y'

         AND Permissions.ROLE_NAME = 'ACC_AccVoucherConfirm'

         AND Roles.ID = RolesMemberships.ROLS_ID

         AND Permissions.ID = RolesMemberships.ROLS_ID_MEMBER_OF;

       

      Then I have issued a SELECT * though it and that works fine.

      And then I've manipulated the data in some tables such that according to MV's WHERE clause it must return no rows.

      Intuitively before refreshing the MV it will return the data last placed on it,

      But it return the same data after issuing the following command in PL/SQL Developer:

      call dbms_mview.refresh(list => 'WLS', method => 'F');

      And unfortunately the following will correct it:

      call dbms_mview.refresh(list => 'WLS', method => 'C');

       

      Why the fast refresh of a fast refreshable MV does not work fine while its complete refresh works good?

       

      How ever after undoing made changes to those tables and issuing the fast refresh using the following command the MV will contain correct updated data:

      call dbms_mview.refresh(list => 'WLS', method => 'F');

       

      It seems that it has bug in deleting rows from Fast Refreshable MV but upserts work fine.

       

      I've tried to simulate the problem for a simpler query with simple tables containing some sample data but those don't have any problem.

       

      Please help me,

      Thanks

        • 1. Re: Fast refresh of a materialized view works incorrect
          user12046702

          Have You created materialized view log ?

           

          CREATE MATERIALIZED VIEW LOG

          • 2. Re: Fast refresh of a materialized view works incorrect
            f9smsk

            As I noted, the process of MV creation has been succeeded and therefore all of the from list tables have a MV Log

            • 3. Re: Fast refresh of a materialized view works incorrect
              Martin Preiss

              you could try to create a sql trace (event 10046) to see the operations that are internally executed in the fast refresh. But I don't see how your different results for fast and complete refresh could be expected behaviour - so maybe it's a bug.

              • 4. Re: Fast refresh of a materialized view works incorrect
                f9smsk

                Thanks Martin,

                 

                Finally I've discovered one of possible several situations in which a MV fast refreshed incorrectly.

                This is happened when an OR condition exists among several AND conditions and at least one outer join symbol there exists!

                this is the scenario:

                 

                -- create two sample tables

                create table t as select 1 a, 2 b from dual;

                create table j as select 1 a, 2 b from dual;

                -- defining primary keys

                alter table t add constraint tp primary key (a);

                alter table j add constraint jp primary key (a);

                -- creating MV Logs

                create materialized view log on t with primary key, rowid;

                create materialized view log on j with primary key, rowid;

                -- creating Fast Refresh MV with a sample query

                create materialized view mvt refresh fast enable query rewrite as

                select t.a, j.b, t.rowid tr, j.rowid jr from t,j where t.a = j.a(+)

                and (t.b = j.b or t.a = t.b);

                -- select through MV, the MV returns correct data

                select * from mvt;

                     A    B    TR                    JR

                    1    2    AAAYwQAAEAAFhwzAAA    AAAYwRAAEAAFhw7AAA

                -- intentionally manipulate the data to the query returns no rows

                update j set a = 2;

                commit;

                -- query the select of MV

                select t.a, j.b, t.rowid tr, j.rowid jr from t,j where t.a = j.a(+)

                and (t.b = j.b or t.a = t.b);

                -- returns now rows, The query rewrite does not work here!

                -- select through MV, the MV returns correct data because it has not refreshed yet!

                select * from mvt;

                     A    B    TR                    JR

                    1    2    AAAYwQAAEAAFhwzAAA    AAAYwRAAEAAFhw7AAA

                -- fast refreshing the mv and issuing select again

                call dbms_mview.refresh(list => 'MVT', method => 'F');

                select * from mvt;

                     A    B        TR                    JR

                    1    null    AAAYwuAAEAAFh2jAAA    null

                -- A change is occured but it is not correct, it must returns no rows!

                -- query the select of MV

                select t.a, j.b, t.rowid tr, j.rowid jr from t,j where t.a = j.a(+)

                and (t.b = j.b or t.a = t.b);

                     A    B        TR                    JR

                    1    null    AAAYwuAAEAAFh2jAAA    null

                -- Query rewrite does work here! because appending a neutral "and 1=1" conditions will show the correct empty result:

                select t.a, j.b, t.rowid tr, j.rowid jr from t,j where t.a = j.a(+)

                and (t.b = j.b or t.a = t.b) and 1=1;

                -- returns no rows

                -- now complete refreshing the MV and achieving the correct result:

                call dbms_mview.refresh(list => 'MVT', method => 'C');

                select * from mvt;

                -- returns no rows

                 

                Is this a BUG?

                Any suggestions?

                • 5. Re: Fast refresh of a materialized view works incorrect
                  f9smsk

                  And the solution is simple,

                   

                  Just avoid using the described OR condition in WHERE clause and use UNION ALL instead!

                   

                  Don't forget you will need a unique maker computed column for each of your unions.

                   

                  Thanks to myself!