Oracle 基础（物化视图）

目录

一、什么是物化视图

二、创建物化视图的语法

三、案例知识点

1.创建手动刷新的物化视图

2.创建字段刷新的物化视图，和上例一样的结果集

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

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

5.创建全量刷新的物化视图

---

一、什么是物化视图

视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。

而对一个物化视图查询的执行效率与查询一个表是一样的。

二、创建物化视图的语法

create materialized view view_name
[build immediate  |  build deferred ]
refresh [fast|complete|force]
[
on  [commit  |  demand ]  |  start  with  (start_time)  next
(next_time)
]
as
subquery

build immediate    是在创建物化视图的时候就生成数据
build deferred    则在创建时不生成数据，以后根据需要再生成数据。
默认为 build immediate。

刷新（ refresh ）：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
refresh 后跟着指定的刷新方法有三种：fast、complete、force。
fast 刷新采用增量刷新，只刷新自上次刷新以后进行的修改。
complete 刷新对整 个物化视图进行完全的刷新。
如果选择 force 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，
如果可以则采用 fast 方式，否则采用 complete的方式

force 是默认的方式。

刷新的模式有两种：on demand 和 on commit 。
on demand 指需要 手动刷新物化视图（默认）。 
on commit 指在基表发生 commit 操作时自动刷新。

三、案例知识点

1.创建手动刷新的物化视图

需求：查询地址 ID,地址名称和所属区域名称, 结果如下：

语句

create materialized view mv_address
as
select ad.id, ad.name adname, ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id

执行上边的语句后查询

select * from mv_address

查询结果如下：

这时，我们向地址表（ T_ADDRESS）中插入一条新记录，

insert into t_address values(8,'宏福苑小区',1,1);

再次执行上边的语句进行查询，会发现新插入的语句并没有出现在物化视图中。

我们需要通过下面的语句（PL/SQL），手动刷新物化视图：

begin
  dbms_mview.refresh('mv_address','C');
end;

2.创建字段刷新的物化视图，和上例一样的结果集

需求: 当 T_ADDRESS 表发生变化时，物化视图 自动跟着改变。

语句如下：

create materialized view mv_address2
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name
from t_address ad, t_area ar
where ad.areaid=ar.id

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

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

创建后执行下列语句查询物化视图

select * from mv_address3

查询结果：

执行下列语句生成数据

begin
  dbms_mview.refresh('MV_ADDRESS3','C');
end;

再次查询，得到结果：

由于我们创建时指定的 on commit ,所以在修改数据后能立刻看到最新数据，无须再次执行 refresh

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

如果创建增量刷新的物化视图，必须首先创建物化视图日志

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

创建的物化视图日志名称为 MLOG$_表名称

创建物化视图

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

注意：创建增量刷新的物化视图，必须：

1.  创建物化视图中涉及表的物化视图日志。

2.  在查询语句中，必须包含所有表的 rowid (  以 rowid 方式建立物化视图日志 )

当我们向地址表插入数据后，物化视图日志的内容：

insert into t_address values(11, '天通苑北4区', 4, 5)

SNAPTIME$$：用于表示刷新时间。

DMLTYPE$$：用于表示 DML 操作类型，I 表示 INSERT ，D 表示 DELETE ，U 表示 UPDATE。

OLD_NEW$$：用于表示这个值是新值还是旧值。 N（EW）表示新值，O（ LD ） 表示旧值，U 表示 UPDATE 操作。

CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。

此列是 RAW 类型，其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。

插入操作显示为：FE,  删除显示为：OO 更新操作则根据更新字段的位置而显示不同的值。

当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。

begin
    DBMS_MVIEW.refresh('MV_ADDRESS4','C');
end;

案例

-- todo 1 增加物化视图日志
drop materialized view log on T_ADDRESS;
create materialized view log on T_ADDRESS with rowid;
create materialized view log on T_AREA with rowid;

-- todo 2 创建增量物化视图: 地址id 地址名称 区域名称
create materialized view mv_addr_5
    refresh fast
as
select
    t1.ROWID as addr_rowid,
    t2.ROWID as area_rowid,
    t1.id,
    t1.name addr_name,
    t2.name area_name
from T_ADDRESS t1, T_AREA t2
where t1.AREAID=t2.id
;

-- todo 3 添加一个新的地址
insert into T_ADDRESS values(10, '天通苑4区', 2, 1);
commit;

delete from T_ADDRESS where id=8;
delete from T_ADDRESS where id=9;
commit;

-- todo 4 查看日志
-- todo 5 刷新
begin
    DBMS_MVIEW.refresh('mv_addr_5','C');
end;

-- todo 6 验证
select * from mv_addr_5;

5.创建全量刷新的物化视图

-- todo 1 创建全量物化视图: 统计每个区域的地址数量
create materialized view mv_addr_7
    refresh
    on commit
as
select
    areaid,
    count(1) as cnt
from t_address
group by areaid
;

-- todo 2 添加数据 测试
insert into T_ADDRESS values(11, '天通苑5区', 2, 1);
commit;


-- 3 验证
select * from mv_addr_6;