create materialized view port
refresh complete
start with sysdate
next sysdate + 1/1440
as
with t as
(
select b.ename,b.sal,
(select dname from scott.dept a where a.deptno=b.deptno )dname
from scott.emp b order by dname
)
select * from t
--上面执行时报物化视图里不能出现子查询
--改写成下面的即可
------------------------------------------------------------------------
create materialized view emp1
refresh complete
start with sysdate
next sysdate + 1/1440
as
with t as
(
select a.ename,a.sal,b.dname from scott.emp a
inner join scott.dept b on a.deptno=b.deptno
order by b.dname
)
select * from t
