MVIEW Wrong Results Caused by MVIEW Fast Refresh After Update The Outer Join Key Column Of Master Tables of MVIEW With CASE WHEN Over DBLINK (Doc ID 2784230.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Customer created fast refresh MVIEW with 2 tables outer joined together over DBLINK with CASE WHEN clause, and mview column become incorrect after update the outer join column of master table and refresh mview.
  
  
  Wrong results:
  
  SQL> select * from MV1;
  
  COL2     C TABLE1_ROWID       TABLE2_ROWID
  -------- - ------------------ ------------------
           3 AAAURwAAHAAAAGjAAA
          
  Correct results:
  
  COL2     C TABLE1_ROWID       TABLE2_ROWID
  -------- - ------------------ ------------------
  20020101 3 AAAURwAAHAAAAGjAAA
  
  This problem can be reproduced by following simple test case:
  
  
  SQL> create user <USERNAME> identified by <PASSWORD>;
  
  User created.
  
  SQL> grant dba to <USERNAME>;
  
  Grant succeeded.
  
  SQL> conn <USERNAME>/<PASSWORD>
  Connected.
  SQL> create table TABLE1 (
    COL1 NUMBER(18) not null
    , COL2 VARCHAR2(8)
    , constraint TABLE1_PK primary key (COL1)
  );  2    3    4    5
  
  Table created.
  
  SQL> create table TABLE2 (
    COL0 NUMBER(18) not null
    , COL1 NUMBER(18) not null
    , COL2 VARCHAR2(8)
    , COL3 CHAR(1) default '0' not null
    , constraint TABLE2_PK primary key (COL0)
  )
  ;  2    3    4    5    6    7    8
  
  Table created.
  
  SQL> CREATE MATERIALIZED VIEW LOG ON TABLE1 TABLESPACE USERS WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES;
  
  Materialized view log created.
  
  SQL> CREATE MATERIALIZED VIEW LOG ON TABLE2 TABLESPACE USERS WITH PRIMARY KEY, ROWID, SEQUENCE(COL3) INCLUDING NEW VALUES;
  
  Materialized view log created.
  
  SQL> CREATE PUBLIC DATABASE LINK <DBLINK_NAME>
    2     CONNECT TO
                  <USERNAME>
          IDENTIFIED BY
                  <PASSWORD>
          USING
                  '  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <HOST_NAME>)(PORT = 1531))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = <SERVICE_NAME>)
      )
    )
  ';
    3    4    5    6    7    8    9   10   11   12   13   14
  Database link created.
  
  SQL> select * from dual@<DBLINK_NAME>;
  
  D
  -
  X
  
  SQL> CREATE MATERIALIZED VIEW MV1
    2          TABLESPACE      USERS
    3          REFRESH         FAST
          WITH            PRIMARY KEY
  AS
          SELECT
                  CASE
                          WHEN T2.COL0 IS NOT NULL  THEN
                                   T2.COL2
                          ELSE T1.COL2
                  END AS COL2
  ,               CASE
                          WHEN T2.COL0 IS NOT NULL  THEN
                                   '2'
                          ELSE '3'
                  END AS COL3
          ,       T1.ROWID              AS      TABLE1_ROWID
          ,       T2.ROWID              AS      TABLE2_ROWID
          FROM
                  TABLE1@<DBLINK_NAME>                            T1
          ,       TABLE2@<DBLINK_NAME>                            T2
          WHERE
                  T1.COL1 = T2.COL1(+)
          AND     T2.COL3(+) = '0'
  ;
    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25
  
  Materialized view created.
  
  SQL> SQL> insert into TABLE1 (COL1,COL2) values ('812001','20020101');
  
  1 row created.
  
  SQL> insert into TABLE2 (COL0,COL1,COL2,COL3) values ('813001','812001','20020101','0');
  
  1 row created.
  
  SQL> COMMIT;
  
  Commit complete.
  
  SQL> select * from MV1;
  
  no rows selected
  
  SQL> execute dbms_mview.refresh('MV1','F');
  
  PL/SQL procedure successfully completed.
  
  SQL> select * from MV1;
  
  COL2     C TABLE1_ROWID       TABLE2_ROWID
  -------- - ------------------ ------------------
  20020101 2 AAAURwAAHAAAAGjAAA AAAURyAAHAAAAHDAAA
  
  SQL> update TABLE2 set COL3='1';
  
  1 row updated.
  
  SQL> COMMIT;
  
  Commit complete.
  
  SQL> alter session set events '10046 trace name context forever, level 12';
  
  Session altered.
  
  SQL> execute dbms_mview.refresh('MV1','F');
  
  PL/SQL procedure successfully completed.
  
  SQL> alter session set events '10046 trace name context off';
  
  Session altered.
  
  SQL> col value for a100
  SQL> select value from v$diag_info where name like 'Default Trace File%';
  
  VALUE
  --------------------------------------------------------------------------------
  /<File Path>/<Trace File name>.trc
  
  
  SQL> select * from MV1;
  
  COL2     C TABLE1_ROWID       TABLE2_ROWID
  -------- - ------------------ ------------------
           3 AAAURwAAHAAAAGjAAA
  
  SQL> SELECT
    2                  CASE
    3                          WHEN T2.COL0 IS NOT NULL  THEN
    4                                   T2.COL2
                          ELSE T1.COL2
    5    6                  END AS COL2
  ,               CASE
    7    8                          WHEN T2.COL0 IS NOT NULL  THEN
                                   '2'
                          ELSE '3'
                  END AS COL3
          ,       T1.ROWID              AS      TABLE1_ROWID
          ,       T2.ROWID              AS      TABLE2_ROWID
          FROM
                  TABLE1@<DBLINK_NAME>                            T1
          ,       TABLE2@<DBLINK_NAME>                            T2
          WHERE
                  T1.COL1 = T2.COL1(+)
          AND     T2.COL3(+) = '0'
  ;  9   10   11   12   13   14   15   16   17   18   19   20
  
  COL2     C TABLE1_ROWID       TABLE2_ROWID
  -------- - ------------------ ------------------
  20020101 3 AAAURwAAHAAAAGjAAA
  
  SQL>
• The sql trace of MVIEW refresh shows following recusive SQL cleared the COL2 incorrectly:
  
  • Trace abstract:
    
    SQL ID: 4rbvrr68ydnhw Plan Hash: 1361972238
    
    UPDATE "<USERNAME>"."MV1" SNA$ SET "COL2"=CASE  WHEN NULL IS NOT NULL THEN NULL
      ELSE NULL END ,"COL3"=CASE  WHEN NULL IS NOT NULL THEN '2' ELSE '3' END ,
      "TABLE2_ROWID"=NULL
    WHERE
     "TABLE2_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT
      CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM
      "<USERNAME>"."MLOG$_TABLE2"@"<DBLINK_STRING>" "MAS$"   WHERE
      "MAS$".SNAPTIME$$ > :B_ST0 AND NOT        ("MAS$".DMLTYPE$$ = 'U' AND
        (sys.dbms_snapshot_utl.vector_compare@"<DBLINK_STRING>"(:B_CV0,
                                                 "MAS$".change_vector$$) = 0)))
      MAS$)
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          8          2           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          8          2           1

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.