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