ORACLE:物化视图使用LEFT JOIN时不工作(ORACLE : Materialized v

2019-08-02 02:29发布

我想创建从左侧物化视图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的帮助

Answer 1:

有两个条件不满足,以使该物化视图的刷新速度很快。 第一个是,你没有指定每个参与表的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点提到我这篇文章 。

关心,罗布。



Answer 2:

由于这是一个古老的职位; 没有提到完整的解决方案。

  1. 即外表加入作为Oracle的文档中提到应该有一个主密钥。
  2. 查询不应该有任何其他方面的限制,即不应该有任何的过滤条件WHERE子句,只是加入; 也不可以具有CASE / DECODE在语句SELECT从句; GROUP BYSUM() COUNT()和这样是允许的,但。

另外,在上述示例实施例,如果上部门表创建在DEPT id列主键查询将起作用。



Answer 3:

遵循以下说明进行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被禁用



文章来源: ORACLE : Materialized view not working when Using LEFT JOIN