Oracle物化视图案例

create table B(clsid int primary key,name VARCHAR2(10));
create table A(
id int primary key,
name VARCHAR2(10),
clsid int,
constraints fk_cls_id foreign key(clsid) references B(clsid)
)

insert into b values(1,'一班');
insert into b values(2,'二班');
insert into b values(3,'三班');

insert into a values(1,'张三',1);
insert into a values(2,'李四',1);
insert into a values(3,'王五',2);

--创建物化视图 实时更新
create materialized view mv_name refresh force on commit as select a.id,a.name,
b.clsid,b.name as bname from a,b
where a.clsid= b.clsid;

--查看所有表
select * from user_tables;
--查看物化视图中的数据
select * from mv_name

--删除物化视图
drop materialized view mv_name
--更新基础表
update a set name='沙海'

--创建物化视图 定时更新
create materialized view mv_name refresh force on demand start with sysdate next
to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
as select a.id,a.name,
b.clsid,b.name as bname from a,b
where a.clsid= b.clsid;

--查看所有表
select * from user_tables;
--查看物化视图中的数据
select * from mv_name

--删除物化视图
drop materialized view mv_name
--更新基础表
update a set name='水滴'

--快速刷新
create materialized view log on A with rowid;
create materialized view log on B with rowid;
--创建物化视图 定时更新 快速更新(在查询基础表的时候，需要将两个表的rowid分别查询出来，快速刷新才会起作用)
create materialized view mv_name refresh force on demand start with sysdate next
to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
as select a.ROWID as AROWID,b.ROWID as BROWID,a.id,a.name,
b.clsid,b.name as bname from a,b
where a.clsid= b.clsid;

--查看所有表
select * from user_tables;
--查看物化视图中的数据
select * from mv_name

--删除物化视图
drop materialized view mv_name
--更新基础表
update a set name='水滴'