Fast Refresh Mview Not Created Because Of Ora-12015 When Ansi Syntax Used In From Clause

(Doc ID 1372720.1)

Last updated on OCTOBER 11, 2013

Applies to:

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

Symptoms

When ANSI syntax is used in the FROM clause, the create of a fast-refreshable materialized view fails with the error "ORA-12015: cannot create a fast refresh materialized view from a complex query."   For example, this fails:

CREATE MATERIALIZED VIEW MV_KP_TESTCASE
COMPRESS
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH ROWID
AS
SELECT R.ROWID "req_rwid",
P.ROWID "prj_rwid",
SU.ROWID SU_RWID,
R.S_REQUESTID AS REQUEST_ID,
. . .
FROM S_REQUEST R
JOIN S_PROJECT P
ON P.S_PROJECTID = R.PROJECTID
JOIN SYSUSER SU
ON P.S_PROJECTID = R.PROJECTID AND R.U_SUBMITTER = SU.SYSUSERID

WHERE R.REQUESTSTATUS <> 'Draft';


When Oracle syntax moves the joins from the FROM clause to the where clause, the fast-refreshable mview creates without error.  For example, this succeeds:

-- [mview create statement the same up to here]
FROM S_REQUEST R,
S_PROJECT P,
SYSUSER SU
WHERE R.REQUESTSTATUS <> 'Draft'
and P.S_PROJECTID = R.PROJECTID
and P.S_PROJECTID = R.PROJECTID AND R.U_SUBMITTER = SU.SYSUSERID

;



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