create table sales(s_id int, time_id date, cust_id int, channel_id int, price int);
insert into sales select mod(rownum,100),sysdate-mod(rownum,365),mod(rownum,500), mod(rownum,20),rownum from dba_objects;
commit;

create table times(time_id date, time_desc varchar(20));
insert into times(time_id) select time_id from sales group by time_id order by time_id;
update times set time_desc=’one’ where time_id > sysdate-273+91+91;
update times set time_desc=’two’ where time_id > sysdate-273 and time_id<=sysdate-273+91; update times set time_desc='three' where time_id>sysdate-273+91 and time_id<=sysdate-273+91+91; update times set time_desc='four' where time_id> sysdate-273+91+91;
commit;

create table customers(cust_id int, cust_desc varchar(20));
insert into customers(cust_id) select cust_id from sales group by cust_id order by cust_id;
update customers set cust_desc=’BJ’ where cust_id>=0 and cust_id < 50; update customers set cust_desc='SH' where cust_id>=51 and cust_id <100; update customers set cust_desc='ZJ' where cust_id>=101 and cust_id > 300;
update customers set cust_desc=’GD’ where cust_id>=301 and cust_id<400; update customers set cust_desc='HongGang' where cust_id>=401 and cust_id<500; commit; select c.cust_desc,t.time_desc,sum(s.price) sum_price, avg(s.price) avg_price from sales s, times t, customers c where s.time_id = t.time_id and s.cust_id = c.cust_id group by c.cust_desc,t.time_desc; create materialized view my_sales build immediate enable query rewrite as select c.cust_desc,t.time_desc,sum(s.price) sum_price, avg(s.price) avg_price from sales s, times t, customers c where s.time_id = t.time_id and s.cust_id = c.cust_id group by c.cust_desc,t.time_desc; 创建物化视图日志，以用来激活快速刷新 CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE(price, time_id, cust_id) /*标准的*/ INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON times /*无法快速刷新的*/ WITH ROWID (time_id, time_desc) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON customers /*无法快速刷新的*/ WITH ROWID (cust_id, cust_desc); --后面用DBMS_MVIEW.EXPLAIN_MVIEW检测无法快速刷新的原因 insert into sales select * from sales where rownum=1; alter materialized view my_sales refresh fast on demand; SQL> exec DBMS_MVIEW.REFRESH(‘my_sales’,’f’);
BEGIN DBMS_MVIEW.REFRESH(‘my_sales’,’f’); END;

*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view “TEST”.”MY_SALES”
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2558
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2771
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2740
ORA-06512: at line 1

@?/rdbms/admin/utlxrw.sql

truncate table MV_CAPABILITIES_TABLE;

exec DBMS_MVIEW.EXPLAIN_MVIEW(‘MY_SALES’);

select CAPABILITY_NAME,POSSIBLE,MSGTXT,RELATED_TEXT from MV_CAPABILITIES_TABLE;

drop materialized view my_sales;

create materialized view my_sales
build immediate
refresh fast on demand
enable query rewrite
as
select c.cust_desc,t.time_desc,sum(s.price) sum_price, avg(s.price) avg_price,count(s.price)
from sales s, times t, customers c
where s.time_id = t.time_id and
s.cust_id = c.cust_id
group by c.cust_desc,t.time_desc;

drop MATERIALIZED VIEW LOG ON customers;

CREATE MATERIALIZED VIEW LOG ON customers
WITH ROWID (cust_id, cust_desc)
including new values;

select CAPABILITY_NAME,POSSIBLE,MSGTXT,RELATED_TEXT from MV_CAPABILITIES_TABLE;

drop materialized view my_sales;

create materialized view my_sales
build immediate
refresh fast on demand
enable query rewrite
as
select c.cust_desc,t.time_desc,sum(s.price) sum_price, avg(s.price) avg_price,count(s.price),count(*) cnt
from sales s, times t, customers c
where s.time_id = t.time_id and
s.cust_id = c.cust_id
group by c.cust_desc,t.time_desc;

select CAPABILITY_NAME,POSSIBLE,MSGTXT,RELATED_TEXT from MV_CAPABILITIES_TABLE;

drop MATERIALIZED VIEW LOG ON times;

CREATE MATERIALIZED VIEW LOG ON times
WITH ROWID, SEQUENCE(time_id, time_desc)
INCLUDING NEW VALUES;

drop MATERIALIZED VIEW LOG ON customers;

CREATE MATERIALIZED VIEW LOG ON customers
WITH ROWID, SEQUENCE(cust_id, cust_desc)
including new values;