我想创建从左侧物化视图2个表JOIN。 但是下面给我一个错误:
SELECT field1
FROM table_1 a
LEFT JOIN table_2 b
ON a.field1=b.field2
ORA-12054:不能设置ON COMMIT刷新属性的物化视图
但是以下工作:
SELECT field1
FROM table_1 a, table_2 b
WHERE a.field1=b.field2
任何人都不会有为什么发生这种情况的任何想法。
THX的帮助
有两个条件不满足,以使该物化视图的刷新速度很快。 第一个是,你没有指定每个参与表的ROWID列。 而第二个是一个无证的限制:ANSI-连接并不支持。
下面是使用DEPT是TABLE_1,别名和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
模仿你的情况。 首先添加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
不过它不能快速刷新,因为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.
要证明它的工作原理:
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.
在ANSI-连接语法限制在6点提到我这篇文章 。
关心,罗布。
由于这是一个古老的职位; 没有提到完整的解决方案。
- 即外表加入作为Oracle的文档中提到应该有一个主密钥。
- 查询不应该有任何其他方面的限制,即不应该有任何的过滤条件
WHERE
子句,只是加入; 也不可以具有CASE
/ DECODE
在语句SELECT
从句; GROUP BY
, SUM()
COUNT()
和这样是允许的,但。
另外,在上述示例实施例,如果上部门表创建在DEPT id列主键查询将起作用。
遵循以下说明进行DBMS_MVIEW.EXPLAIN_MVIEW工作: http://www.sqlsnippets.com/en/topic-12884.html
能够:
REFRESH_COMPLETE
不能够:
REFRESH_FAST
REFRESH_FAST_AFTER_INSERT
在FROM列表内嵌视图或子查询不支持此类型MV
REFRESH_FAST_AFTER_INSERT
在FROM列表内嵌视图或子查询不支持此类型MV
REFRESH_FAST_AFTER_INSERT
视图或子查询中从列表中
REFRESH_FAST_AFTER_ONETAB_DML
看到相关的原因REFRESH_FAST_AFTER_INSERT被禁用
MV_REPORT
REFRESH_FAST_AFTER_ANY_DML
看到相关的原因REFRESH_FAST_AFTER_ONETAB_DML被禁用