貌似物化视图不支持子查询的即时刷新特性(on commit

[Leshami]

???
貌似物化视图不支持子查询的即时刷新特性(on commit)，请大家指点下，谢谢！

--创建物化视图日志
SQL> create materialized view log on emp with rowid (deptno,ename) including new values;

Materialized view log created.

SQL> create materialized view log on dept with rowid(dname,deptno) including new values;

Materialized view log created.

--创建物化视图
SQL> create materialized view mv_join3
2 refresh fast
3 on commit
4 as
5 SELECT dname
6 FROM dept
7 WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');
FROM dept
*
ERROR at line 6:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

SQL> exec dbms_mview.explain_mview('select dname from dept where deptno=(select deptno from emp where ename=''SCOTT'')','new');

PL/SQL procedure successfully completed.

SQL> SELECT capability_name, possible,msgno,SUBSTR(msgtxt,1,60) AS msgtxt
2 FROM mv_capabilities_table
3 WHERE statement_id='new' and capability_name like '%FAST%';

CAPABILITY_NAME POSSIBLE MSGNO MSGTXT
------------------------------ -------- ----- --------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N 2129 join or filter condition(s) are complex
REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N 2157 PCT is not possible on any of the detail tables in the mater

--版本信息
SQL> select * from v$version;

BANNER
----------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production

[yixilan]

楼主的意思说是你创建的物化视图，不能即时刷新，需要你手动执行refresh刷新，是吧？

[yixilan]

create materialized view mv_join3
TABLESPACE APPS_TS_TX_DATA
refresh FORCE
on commit
as
SELECT *
FROM dept
WHERE deptno = 20;
--把“WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');”
改成“WHERE deptno = 20”就行了。

[Leshami]

这个只是个例子，实际生成环境中主表的一个字段值由子表返回，且两个表之间不存在参照关系。