貌似物化视图不支持子查询的即时刷新特性(on commit
???
貌似物化视图不支持子查询的即时刷新特性(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


------解决方案--------------------
楼主的意思说是你创建的物化视图，不能即时刷新，需要你手动执行refresh刷新，是吧？
------解决方案--------------------
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”就行了。