WRONG RESULTS FROM FAST REFRESH MVIEW HAVING OUTER JOINS (Doc ID 1532598.1)

Last updated on JANUARY 19, 2022

Applies to:

Symptoms

Wrong results(like extra rows)can be seem after refresh of materialized view having outer joins.
This can be illustrated with following example:

CREATE TABLE TABLE_A( PK NUMBER NOT NULL PRIMARY KEY, JOIN_COL NUMBER );
CREATE TABLE TABLE_B( PK NUMBER NOT NULL PRIMARY KEY, JOIN_COL NUMBER );
CREATE TABLE TABLE_C( PK NUMBER NOT NULL PRIMARY KEY, JOIN_COL NUMBER );

CREATE MATERIALIZED VIEW LOG ON TABLE_A WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON TABLE_B WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON TABLE_C WITH ROWID;

CREATE MATERIALIZED VIEW MV_A
BUILD IMMEDIATE REFRESH FAST ON DEMAND DISABLE QUERY REWRITE AS
SELECT
TABLE_A.PK AS A_PK,
TABLE_A.JOIN_COL,
TABLE_B.PK AS B_PK,
TABLE_C.PK AS C_PK,
TABLE_A.ROWID AS A_ROWID,
TABLE_B.ROWID AS B_ROWID,
TABLE_C.ROWID AS C_ROWID
FROM
TABLE_A,
TABLE_B,
TABLE_C
WHERE
TABLE_A.JOIN_COL = TABLE_B.JOIN_COL (+)
AND TABLE_A.JOIN_COL = TABLE_C.JOIN_COL (+);

INSERT INTO TABLE_A VALUES ( 1, 100 );
COMMIT;
EXECUTE DBMS_MVIEW.REFRESH( 'MV_A', 'f' );

INSERT INTO TABLE_B VALUES ( 1001, 100 );
INSERT INTO TABLE_C VALUES ( 2001, 100 );
COMMIT;
EXECUTE DBMS_MVIEW.REFRESH( 'MV_A', 'f' );

-- These two queries should return the same rows

SQL> SELECT * FROM MV_A;

A_PK JOIN_COL B_PK C_PK A_ROWID
---------- ---------- ---------- ---------- ------------------
B_ROWID C_ROWID
------------------ ------------------
1 100 AAAVrKAAEAAC3cHAAA


1 100 1001 2001 AAAVrKAAEAAC3cHAAA
AAAVrMAAEAAC3cnAAA AAAVrOAAEAAC3c3AAA


SQL> SELECT -- this query is the mview definition
2 TABLE_A.PK AS A_PK,
3 TABLE_A.JOIN_COL,
4 TABLE_B.PK AS B_PK,
5 TABLE_C.PK AS C_PK,
6 TABLE_A.ROWID AS A_ROWID,
7 TABLE_B.ROWID AS B_ROWID,
8 TABLE_C.ROWID AS C_ROWID
9 FROM
10 TABLE_A,
11 TABLE_B,
12 TABLE_C
13 WHERE
14 TABLE_A.JOIN_COL = TABLE_B.JOIN_COL (+)
15 AND TABLE_A.JOIN_COL = TABLE_C.JOIN_COL (+);


A_PK JOIN_COL B_PK C_PK A_ROWID
---------- ---------- ---------- ---------- ------------------
B_ROWID C_ROWID
------------------ ------------------
1 100 1001 2001 AAAVrKAAEAAC3cHAAA
AAAVrMAAEAAC3cnAAA AAAVrOAAEAAC3c3AAA

The mview has 1 extra row

Cause

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