ORACLE:使用LEFT JOIN时，物化视图不起作用 [英] ORACLE : Materialized view not working when Using LEFT JOIN

问题描述

我想从2个表的LEFT JOIN创建一个MATERIALIZED VIEW.但是以下内容给我一个错误:

I want to create a MATERIALIZED VIEW from a LEFT JOIN of 2 tables. However the following gives me an error:

    SELECT field1 
     FROM table_1 a 
     LEFT JOIN table_2 b 
     ON a.field1=b.field2

ORA-12054:无法为实例化视图设置ON COMMIT刷新属性

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

但是，以下方法可行:

SELECT field1 
 FROM table_1 a, table_2 b 
 WHERE a.field1=b.field2

有人知道为什么会这样吗.

Does anyone have any ideas why this is happening.

寻求帮助

推荐答案

要使物化视图快速刷新，有两个条件不满足.第一个是您没有指定每个涉及的表的rowid列.第二个是未记录的限制:不支持ANSI联接.

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.

下面是一个示例，其中DEPT为table_1，别名为a，EMP为table_2，别名为b:

Here is an example with DEPT being table_1, alias a and EMP being table_2, alias 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:

That mimics your situation. First add the rowid's:

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连接，它仍然无法快速刷新.转换为旧式外部联接语法:

Still it cannot fast refresh, because of the ANSI joins. Converting to old-style outer join syntax:

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.

此博文.

关于， 罗布.

这篇关于ORACLE:使用LEFT JOIN时，物化视图不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！