Oracle 无法使用新的SQL JOIN语法创建可快速刷新的提交时MV

主题说了一切，真的。使用旧式逗号连接，成功。尝试使用新的SQL JOIN语法创建快速可刷新的提交时实体化视图失败。文档。不要期望这样的行为。关于这件事有什么指导吗？

(如果您想知道为什么新样式对我很重要，我希望利用它的能力来完全外部连接; 旧样式的语法是不可能的。当然，这可能最终甚至是不可能的，但是我认为我至少应该向您报告此发现，因为我在任何地方都找不到此错误的提及)。

这都在11g2以下。

创建表A (整数);
创建表B (B整数);

用ROWID在A上创建物化视图日志;
使用ROWID在B上创建物化视图日志;

旧式加入:

开始
dbms_mview.explain_mview ('选择A.ROWID为A_ROWID，B.ROWID为B_ROWID
来自A，B
其中A.A = b.b'，'xxx');
结束;
/

MV_CAPABILITIES表的输出:

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
复制

新型连接:

开始
dbms_mview.explain_mview ('选择A.ROWID为A_ROWID，B.ROWID为B_ROWID
从A.A = b.b'，'yyy' 上的一个连接B);
结束;
/

似乎新式联接被解释为内联视图或子查询。

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
复制

是的... 这是一个已知的问题。

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.
复制