Create Fast Refreshable Materialized View With Self Join and Avoid ORA-12052, ORA-957

(Doc ID 789912.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.5 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.


Note: This solution is not possible in 9i; trying it for 9i and below will result in ORA-12015: cannot create a fast refresh materialized view from a complex query.


Goal

This article will help to create a FAST refreshable materialized view that contains a SELF-JOIN.

Consider the following testcase:

CREATE TABLE "TESTTB"
(
"COL1" VARCHAR2(10 BYTE) NOT NULL,
"COL2" VARCHAR2(10 BYTE) NOT NULL,
"COL3" VARCHAR2(10 BYTE) NOT NULL,
CONSTRAINT "TESTTB_PK" PRIMARY KEY ("COL1")
);
create materialized view log on TESTTB WITH PRIMARY key, rowid;
create materialized view MVTESTTB
refresh fast
as
select tb1.col1, tb1.col2, tb1.col3,
       tb2.col1, tb2.col2, tb2.col3
from testtb tb1, testtb tb2
where tb1.col1 = tb2.col1
/

from testtb tb1, testtb tb2
*
ERROR at line 8:
ORA-12052: cannot fast refresh materialized view USER1.MVTESTTB

Solution

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