解读Oracle的day3

–COMPLETE 完全刷新
–FAST 增量更新
–FORCE 自动选择

–ON COMMIT 在基表做提交操作是刷新物化视图
–ON DEMAND 手动刷新

–创建手动刷新的物化视图
create materialized view mv_address1 as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

–查询物化视图
select * from mv_address1

–向基表插入数据
insert into T_ADDRESS VALUES(9,‘西四旗’,2,2);
commit;
–查询基表数据
select * from t_address

–执行下列语句来进行刷新
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS1’,‘C’);
end;

–创建自动刷新的物化视图 -基表发生commit操作，自动刷新物化视图
create materialized view mv_address2
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

–向基表插入数据
insert into T_ADDRESS VALUES(10,‘西5旗’,2,2);
commit;
–查询物化视图
select * from mv_address2

–创建时不生成数据的物化视图

create materialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

–查询物化视图
select * from mv_address3

insert into T_ADDRESS VALUES(12,‘西7旗’,2,2);
commit;
–第一次必须手动执行刷新，
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS3’,‘C’);
end;

–.创建增量刷新的物化视图

–前提是必须创建物化视图日志 ：记录基表发生了哪些变化，用这些记录去更新物化视图

create materialized view log on t_address with rowid
create materialized view log on t_area with rowid

–创建物化视图中的语句中，必须有基表的ROWID

create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid, ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

select * from mv_address4
–插入数据
insert into T_ADDRESS VALUES(14,‘西9旗’,2,2);
commit;
delete from t_address where id=13;
commit;
–手动刷新
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS4’,‘C’);
end;