oracle 物化视图介绍及测试

 

原创szrsu2022-12-22

783

物化视图和普通视图类似，反映的是某个查询的结果，但是和普通视图仅保存SQL定义不同， 物化视图本身会存储数据。

1、物化视图刷新机制
刷新（Refresh）机制：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
*** on demand
COMPLETE 完全刷新：每次更新物化视图中所有数据。
FAST增量刷新，需要基表建立物化视图日志；通过物化视图日志判断基表数据变化。
FORCE优先使用 FAST,不满足 FAST 条件使用 COMPLIETE
*** on commit
基表视图提交时，刷新物化视图，用户 FAST 快速刷新
不支持远程
影响基表 commit 速度
*** never
不刷新。

refresh [fast|complete|force] 视图刷新的方式:
fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化，建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on （主表名）。
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。

2、MV数据刷新的时间：
刷新（Refresh）机制：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
on demand:在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）
on commit:当主表中有数据提交的时候，立即刷新MV中的数据；
start ……：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；

3、物化视图日志
3.1、基本概念
<<<用来记录基表的数据变化，以便依赖于基表的物化视图可以增量刷新。
<<<每个物化视图日志分配给单独的基表。
<<<如果使用快速刷新，需要物化视图日志。
<<<物化视图日志定义必须包括WITH WORID。
<<<为了连接和聚集的物化视图能够在INSERT语句后被快速刷新，物化视图日志必须包括：
（1）物化视图中提及的每一列。
（2）INCLUDING NEW VALUES选项
<<<基表有INSERT,UPDATE,DELETE复合的操作，要求物化视图日志包括SEQUENCE选项，它提供了相关的顺序信息。

3.2、物化视图日志的作用
为 fast（增量刷新）提供数据。物化视图日志是用来记录基表更细操作的一种特殊日志表，物化视图的快速刷新要求基本必须建立物化视图日志。
物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。

基表中的主键或 rowid 可以为 materialized log 唯一标识表行记录，这是 fast 刷新的必要条件。
如果基表有主键，可以创建基于主键的物化视图日志。
如果基表无主键，可以建立基于 rowid 的物化视图，通过 rowid 标识每一行记录。

3.3、建立物化视图日志要点
一个基表只能建立一个 materialized log
一个 materialized log 可以为多个物化视图提供数据
一个 materialized log 可以选择基表中的某些列，包含这些列的物化视图才能支持 fast 刷新
没有 materialized log 的基表只能支持物化视图的 comlete 刷新
可以建立为 RowID 或 Primary Key 类型的
还可以选择是否包括 Sequence、Including New Values 以及指定列的列表

任何物化视图都会包括的4列：
SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$： 用于表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE。
OLD_NEW$$： 用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。
CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。
如果WITH后面跟了ROWID， 则物化视图日志中会包含：M_ROW$$：用来存储发生变化的记录的ROWID。
如果WITH后面跟了PRIMARY KEY，则物化视图日志中会包含主键列。
如果WITH后面跟了OBJECT ID， 则物化视图日志中会包含：SYS_NC_OID：用来记录每个变化对象的对象ID。 如果WITH后面跟了SEQUENCE， 则物化视图日子中会包含：SEQUENCE$：给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
如果WITH后面跟了一个或多个COLUMN名称，则物化视图日志中会包含这些列。

–查看数据库中所有的物化视图日志
select * from all_mview_logs;

1、创建两个测试表
create table songtest001
(
id001 number,
name1 varchar2(5),
name2 varchar2(5)
);
create table songtest002
(
id002 number,
name1 varchar2(5),
name2 varchar2(5)
);
ALTER TABLE SONGTEST001 ADD CONSTRAINT PK_SONGTEST001 PRIMARY KEY (ID001);
ALTER TABLE SONGTEST002 ADD CONSTRAINT PK_SONGTEST002 PRIMARY KEY (ID002);

要有主键，否则无法添加物化视图的log。

2、创建物化视图日志表

create materialized view log on songtest001;
create materialized view log on songtest002;
alter MATERIALIZED VIEW LOG ON songtest001 add rowid;
alter MATERIALIZED VIEW LOG ON songtest002 add rowid;

上述语句合成下列：
create materialized view log on songtest001 with rowid;
create materialized view log on songtest002 with rowid;

注意：必须添加rowid，基于多个表的物化视图，需要日志表添加列，同时物化视图的创建语句也要添加上rowid。

–drop materialized view log on songtest001;
–drop materialized view log on songtest002;

3、创建物化视图

我们此次创建的是每天10点定时刷新的物化视图，那么每次刷新之间的数据，即基表变化的数据都记录在了日志表中。

create materialized view M_songtest
REFRESH FAST 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 s1.rowid rowid1,s1.id001,s1.name1 name11,s1.name2 name12,s2.rowid rowid2,s2.id002,s2.name1 name21,s2.name2 name22 FROM SONGTEST001 s1,songtest002 s2 where s1.id001=s2.id002;

4、验证

验证：
insert into songtest001 values (1,‘n1’,‘nn1’);
insert into songtest001 values (2,‘n1’,‘nn1’);
insert into songtest001 values (3,‘n1’,‘nn1’);
insert into songtest001 values (4,‘n1’,‘nn1’);
insert into songtest001 values (5,‘n1’,‘nn1’);
insert into songtest001 values (6,‘n1’,‘nn1’);

insert into songtest002 values (1,‘n2’,‘nn2’);
insert into songtest002 values (2,‘n2’,‘nn2’);
insert into songtest002 values (3,‘n2’,‘nn2’);
insert into songtest002 values (4,‘n2’,‘nn2’);
insert into songtest002 values (5,‘n2’,‘nn2’);
insert into songtest002 values (6,‘n2’,‘nn2’);

提交的和未提交的数据：如果不提交，回滚，则查询不到物化视图的log。
select * from mlog𝑠𝑜𝑛𝑔𝑡𝑒𝑠𝑡001;𝑠𝑒𝑙𝑒𝑐𝑡∗𝑓𝑟𝑜𝑚𝑚𝑙𝑜𝑔s​ongtest001;select∗frommlog_songtest002;

日志表的表名就是上面的格式，是oracle自动维护的。

delete from songtest001 where id001=4;
delete from songtest002 where id002=4;

update songtest001 set name1=‘n13’ where id001=3;
update songtest002 set name1=‘n23’ where id002=3;
commit;

ID001 SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$ M_ROW$$
1 4 4000/1/1 D O 00 2.81484425645398E15 AAAKq1AAEAAAHW7AAD
2 3 4000/1/1 U U 04 2.2518556482692E15 AAAKq1AAEAAAHW7AAC

ID002 SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$ M_ROW$$
1 4 4000/1/1 D O 00 2.81484425645398E15 AAAKq2AAEAAAHXDAAD
2 3 4000/1/1 U U 04 2.2518556482692E15 AAAKq2AAEAAAHXDAAC

手动刷新物化视图：
begin
dbms_mview.refresh(TAB=>‘M_SONGTEST’,
METHOD=>‘COMPLETE’,
PARALLELISM=>8); --PARALLELISM并行控制参数
end;
/
或
exec dbms_mview.refresh(‘M_SONGTEST’,‘F’);

select * from M_songtest;

ROWID1 ID001 NAME1 NAME1 ROWID2 ID002 NAME2 NAME2

---

AAAKq1AAEAAAHW7AAA 1 n1 nn1 AAAKq2AAEAAAHXDAAA 1 n2 nn2
AAAKq1AAEAAAHW7AAB 2 n1 nn1 AAAKq2AAEAAAHXDAAB 2 n2 nn2
AAAKq1AAEAAAHW7AAC 3 n1 nn1 AAAKq2AAEAAAHXDAAC 3 n2 nn2

刷新物化视图会清空物化视图日志表：物化视图的日志清除一般是由Oracle自动完成的。Oracle会根据物化视图基表上的注册信息和物化视图的刷新来确定何时删除物化视图日志。

删除物化视图
drop materialized view M_songtest;

注意：没有删除日志表。日志表也要单独删除哦。
drop materialized view log on 基表;