ORACLE：使用 LEFT JOIN 时物化视图不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1312980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE : Materialized view not working when Using LEFT JOIN
提问by ddallala
I want to create a MATERIALIZED VIEW from a LEFT JOIN of 2 tables. However the following gives me an error:
我想从 2 个表的 LEFT JOIN 创建一个 MATERIALIZED VIEW。但是以下给了我一个错误：
SELECT field1
FROM table_1 a
LEFT JOIN table_2 b
ON a.field1=b.field2
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性
However the following works:
但是，以下工作：
SELECT field1
FROM table_1 a, table_2 b
WHERE a.field1=b.field2
Does anyone have any ideas why this is happening.
有没有人知道为什么会发生这种情况。
Thx for the help
谢谢你的帮助
回答by Rob van Wijk
There are two conditions that are not satisfied to make that materialized view refresh fast. First one is that you did not specify the rowid columns of every table involved. And the second one is an undocumented restriction: ANSI-joins are not supported.
有两个条件不满足才能使物化视图快速刷新。第一个是您没有指定所涉及的每个表的 rowid 列。第二个是未公开的限制：不支持 ANSI 连接。
Here is an example with DEPT being table_1, alias a and EMP being table_2, alias b:
这是一个示例，其中 DEPT 为 table_1，别名 a，EMP 为 table_2，别名 b：
SQL> create materialized view log on emp with rowid
2 /
Materialized view log created.
SQL> create materialized view log on dept with rowid
2 /
Materialized view log created.
SQL> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.deptno
5 from dept a
6 left join emp b on (a.deptno = b.deptno)
7 /
from dept a
*
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
That mimics your situation. First add the rowid's:
那模仿你的情况。首先添加rowid：
SQL> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 left join emp b on (a.deptno = b.deptno)
9 /
from dept a
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Still it cannot fast refresh, because of the ANSI joins. Converting to old-style outer join syntax:
由于 ANSI 连接，它仍然无法快速刷新。转换为旧式外连接语法：
SQL> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 , emp b
9 where a.deptno = b.deptno (+)
10 /
Materialized view created.
And to prove that it works:
并证明它有效：
SQL> select * from empdept_mv
2 /
DEPT_ROWID EMP_ROWID DEPTNO
------------------ ------------------ ----------
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAA 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF 30
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG 10
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI 10
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN 10
AAARhmAAEAAAAI/AAD 40
15 rows selected.
SQL> insert into dept values (50,'IT','UTRECHT')
2 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select * from empdept_mv
2 /
DEPT_ROWID EMP_ROWID DEPTNO
------------------ ------------------ ----------
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAA 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF 30
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG 10
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI 10
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN 10
AAARhmAAEAAAAI/AAD 40
AAARhmAAEAAAAI7AAA 50
16 rows selected.
The ANSI-join syntax restriction is mentioned in point 6 in this blogpost.
这篇博文的第 6 点提到了 ANSI-join 语法限制。
Regards, Rob.
问候，罗伯。
回答by ron
Since this is an old post; no has mentioned complete solution.
由于这是一个旧帖子；没有提到完整的解决方案。
- The table that is outer joined should have a primary key as mentioned in Oracle doc.
- The query should not have any other constraints i.e should not have any filter criteria
in
WHEREclause, just the joins; nor can haveCASE/DECODEstatements inSELECTclause;GROUP BY,SUM(),COUNT()and such are allowed, though.
- 外部联接的表应该具有 Oracle 文档中提到的主键。
- 查询不应有任何其他约束，即
WHERE子句中不应有任何过滤条件，只有连接；子句中也不能有CASE/DECODE语句SELECT；GROUP BY,SUM(),COUNT()等等都是允许的。
In above sample example the query will work if a primary key is created on department table on dept id column.
在上面的示例示例中，如果在部门表上的部门 id 列上创建了主键，则查询将起作用。
回答by ddallala
Followed the following instructions to make DBMS_MVIEW.EXPLAIN_MVIEW work: http://www.sqlsnippets.com/en/topic-12884.html
按照以下说明使 DBMS_MVIEW.EXPLAIN_MVIEW 工作：http://www.sqlsnippets.com/en/topic-12884.html
Capable of:
能够：
REFRESH_COMPLETE
REFRESH_COMPLETE
Not Capable of:
不具备：
REFRESH_FAST
REFRESH_FAST
REFRESH_FAST_AFTER_INSERT
inline view or subquery in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT
此类 MV 不支持 FROM 列表中的内联视图或子查询
REFRESH_FAST_AFTER_INSERT
inline view or subquery in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT
此类 MV 不支持 FROM 列表中的内联视图或子查询
REFRESH_FAST_AFTER_INSERT
view or subquery in from list
REFRESH_FAST_AFTER_INSERT
从列表中查看或子查询
REFRESH_FAST_AFTER_ONETAB_DML
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML
查看 REFRESH_FAST_AFTER_INSERT 被禁用的原因
MV_REPORT
MV_REPORT
REFRESH_FAST_AFTER_ANY_DML
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_ANY_DML
查看 REFRESH_FAST_AFTER_ONETAB_DML 被禁用的原因
