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:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterOracle Database - Standard Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.
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
- Trace abstract:
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |