create materialized view log on emp
with rowid,sequence(deptno,sal)
including new values;
create materialized view scott_emp
build immediate
refresh fast on commit
with primary key
as
select deptno,count(*) amount,sum(sal) total
from emp
group by deptno;
创建后执行update后,看不到刷新效果,我只能手动刷新exec dbms_mview.refresh('scott_emp','?c');
不知道为什么?
先谢谢大家了^_^不胜感激!
with rowid,sequence(deptno,sal)
including new values;
create materialized view scott_emp
build immediate
refresh fast on commit
with primary key
as
select deptno,count(*) amount,sum(sal) total
from emp
group by deptno;
创建后执行update后,看不到刷新效果,我只能手动刷新exec dbms_mview.refresh('scott_emp','?c');
不知道为什么?
先谢谢大家了^_^不胜感激!
其中:如果指明了除COUNT之外的聚集函数,则COUNT(expr)也必须存在;
比如:包含SUM(a),则必须同时包含COUNT(a)。
所以修改查询语句后,正确:
create materialized view scott_emp
build immediate
refresh fast on commit
with rowid
as
select deptno,count(*) amount,count(sal) count_deptno,sum(sal) total
from emp
group by deptno;
select *
from scott_emp;
^_^
其中:如果指明了除COUNT之外的聚集函数,则COUNT(expr)也必须存在;
比如:包含SUM(a),则必须同时包含COUNT(a)。
所以修改查询语句后,正确:
create materialized view scott_emp
build immediate
refresh fast on commit
with rowid
as
select deptno,count(*) amount,count(sal) count_deptno,sum(sal) total
from emp
group by deptno;
select *
from scott_emp;
^_^