Skip to Main Content
  • Questions
  • Cannot Create Fast Refreshable On-Commit MV using new SQL JOIN Syntax

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Martin.

Asked: February 15, 2017 - 2:36 pm UTC

Last updated: February 16, 2017 - 5:59 am UTC

Version: 11g2

Viewed 1000+ times

You Asked

The subject says it all, really. Using the old-style comma joins, succeeds. Trying to create a fast refreshable on-commit materialized view using the new SQL JOIN syntax fails. The docs. do not expect such behaviour. Any guidance on the matter?

(In case you're wondering why the new style is important to me, I was hoping to make use of its ability to full outer join; something impossible with the old-style syntax. Of course, that might end up not even being possible, but I figured I should at least report this discovery to you as I can find no mention of this bug anywhere).

This is all under 11g2.

CREATE TABLE A (A INTEGER);
CREATE TABLE B (B INTEGER);

CREATE MATERIALIZED VIEW LOG ON A WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON B WITH ROWID;

Old-style joins:

BEGIN
dbms_mview.explain_mview ('SELECT A.ROWID AS A_ROWID, B.ROWID AS B_ROWID
FROM A, B
WHERE A.A = B.B', 'xxx');
END;
/

Output from MV_CAPABILITIES table:

CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, RELATED_NUM, MSGNO, MSGTXT
===================================================================
SEQ
PCT N     1
REFRESH_COMPLETE Y     1002
REFRESH_FAST Y     2003
REWRITE Y     3004
PCT_TABLE N A 114 2068 relation is not a partitioned table 4005
PCT_TABLE N B 117 2068 relation is not a partitioned table 4006
REFRESH_FAST_AFTER_INSERT Y     5007
REFRESH_FAST_AFTER_ONETAB_DML Y     6008
REFRESH_FAST_AFTER_ANY_DML Y     7009
REFRESH_FAST_PCT N   2157 PCT is not possible on any of the detail tables in the materialized view 8010
REWRITE_FULL_TEXT_MATCH Y     9011
REWRITE_PARTIAL_TEXT_MATCH Y     10012
REWRITE_GENERAL Y     11013
REWRITE_PCT N   2158 general rewrite is not possible or PCT is not possible on any of the detail tables 12014
PCT_TABLE_REWRITE N A 114 2068 relation is not a partitioned table 13015
PCT_TABLE_REWRITE N B 117 2068 relation is not a partitioned table 13016


New-style joins:

BEGIN
dbms_mview.explain_mview ('SELECT A.ROWID AS A_ROWID, B.ROWID AS B_ROWID
FROM A JOIN B ON A.A = B.B', 'yyy');
END;
/

It appears the new-style joins are being interpreted as an inline view or subquery.

CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, RELATED_NUM, MSGNO, MSGTXT
===================================================================
PCT N     1
REFRESH_COMPLETE Y     1002
REFRESH_FAST N     2003
REWRITE Y     3004
REFRESH_FAST_AFTER_INSERT N   2153 inline view or subquery in FROM list not supported for this type MV 5005
REFRESH_FAST_AFTER_INSERT N   2153 inline view or subquery in FROM list not supported for this type MV 5006
REFRESH_FAST_AFTER_INSERT N   2042 view or subquery in from list 5007
REFRESH_FAST_AFTER_ONETAB_DML N   2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled 6008
REFRESH_FAST_AFTER_ANY_DML N   2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 7009
REFRESH_FAST_PCT N   2196 PCT FAST REFRESH is not possible if query contains an inline view 8010
REWRITE_FULL_TEXT_MATCH Y     9011
REWRITE_PARTIAL_TEXT_MATCH Y     10012
REWRITE_GENERAL N   2141 the reason why the capability is disabled has escaped analysis 11013
REWRITE_PCT N   2158 general rewrite is not possible or PCT is not possible on any of the detail tables 12014

and Connor said...

Yup...its a known problem.

Fast Refresh Mview Not Created Because Of Ora-12015 When Ansi Syntax Used In From Clause (Doc ID 1372720.1) 

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

Bug 13066053 - FAST-REFRESH MVIEW NOT CREATED WHEN ANSI SYNTAX USED IN FROM CLAUSE, was filed for this issue and closed as not a bug.   The ANSI join transforms to an inline query, which is not supported for fast refresh.  Development is looking to see if this restriction can be lifted in unpublished Bug 4215478: ENHANCE DOCUMENTATION ON USE OF ANSI JOIN IN MATERIALIZED VIEW.

Solution

Workaround (applies through 11gR2):
Use Oracle syntax, which moves the joins from the FROM clause to the WHERE clause; the fast-refreshable mview creates without error when this is done.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Martin Rose, February 16, 2017 - 9:02 am UTC

Many thanks. I'll have to come up with a creative work-around, possibly involving nested MVs.

More to Explore

DBMS_MVIEW

More on PL/SQL routine DBMS_MVIEW here