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

Last updated on FEBRUARY 27, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms