1. 全栈程序员必看首页

Oracle物化视图与物化视图日志

全栈程序员-站长 • • 未分类 • 阅读 2

Oracle物化视图与物化视图日志文章目录物化视图物化视图与普通视图的区别创建一个存放 person 的表创建一个存放 person 的 address 的表初始化数据创建物化视图的语句 1 build immediate deferred 2 refresh fast complete force 视图刷新的方式 3 MV 数据刷新的时间 4 查询重写 QueryRewrite 演示 refreshcompl 演

文章目录

以下都是最近我通过网上学习和实践简单总结的一些学习成果，还有很多还是不了解。

物化视图

物化视图与普通视图的区别

物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询， Oracle 都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。

创建一个存放person的表

create table test_person(pid int,name varchar2(20)); alter table test_person add constraint pk_pid primary key(pid); --删除表 drop table test_person;

创建一个存放person的address的表

create table test_address(aid int,address varchar2(20),pid int); alter table test_address add constraint pk_aid primary key(aid); --删除表 drop table test_address;

初始化数据

insert into test_person values(1,'kevin'); insert into test_address values(1,'SHA',1); insert into test_person values(2,'vincent'); insert into test_address values(2,'HKG',2); COMMIT;

创建物化视图的语句

create materialized view [view_name] build [immediate|deferred] refresh [fast|complete|force] [ on [commit|demand] | start with (start_time) next (next_time) ] [enable | disable] query rewrite as {创建物化视图用的查询语句}

1.build [immediate|deferred]

创建方式（BuildMethods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。

  • BUILD IMMEDIATE是在创建物化视图的时候就生成数据。
  • BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE

2.refresh [fast|complete|force] 视图刷新的方式:

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

3.MV数据刷新的时间：

  • on demand:在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）
  • on commit:当主表中有数据提交的时候，立即刷新MV中的数据；
  • start ……：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；

4.查询重写（QueryRewrite)

包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。

分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。

感觉很少用，有兴趣的可以研究下：https://mp.weixin..com/s/5Lg8cSEb3R5WkQwVF9xfPQ

演示 refresh complete on demand

create materialized view my_mv_cd build immediate refresh complete on demand as select p.pid as id, p.name, a.address from test_person p,test_address a where p.pid = a.aid;

这里的build immediate表示创建物化视图的同时立即导入数据。
BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。

在这里插入图片描述

在demand模式下，修改基表，物化视图是不会跟着改变的，只能手动DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新

演示 build deferred

create materialized view my_mv_cd2 build deferred refresh complete on demand as select p.pid as id, p.name, a.address from test_person p,test_address a where p.pid = a.aid;

在这里插入图片描述

如何手动刷新？

BEGIN DBMS_MVIEW.REFRESH ( list => 'my_mv_cd2', Method =>'C', refresh_after_errors => True); END;

在这里插入图片描述

演示 refresh complete on commit

create materialized view my_mv_cc build immediate refresh complete on commit as select p.pid as id, p.name, a.address from test_person p,test_address a where p.pid = a.aid; 
insert into test_person values(3,'thomas'); insert into test_address values(3,'ZHA',3); COMMIT;

在这里插入图片描述

一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。缺点就是对基表的DML操作影响很大。

演示on commit情况下的物化视图更新出错

而且对基表的DML跟对物化视图的刷新应该是在同一个事务下的。所以如果物化视图更新出错，主表也无法提交事务。

我们可以做如下的一个实验。。

创建两个表，不设置主键。

create table test_person2(pid int,name varchar2(20)); create table test_address2(aid int,address varchar2(20),pid int);

创建物化视图，刷新时间模式设置为on commit

create materialized view my_mv_test refresh complete on commit as select p.pid as id, p.name, a.address from test_person2 p,test_address2 a where p.pid = a.aid;

然后我们给id加一个唯一索引。

create unique index u_test_index on my_mv_test(id);

现在开始插入数据。。我们插入相同id的数据

insert into test_person2 values(1,'kevin'); insert into test_address2 values(1,'SHA',1); insert into test_person2 values(1,'vincent'); insert into test_address2 values(1,'HKG',1); COMMIT;

在这里插入图片描述

然后我们可以发现两个基表也没有数据提交，说明是在同一个事务下进行了rollback。

所以使用commit需要注意的地方：

  1. 主表提交时基表也提交，系统开销增大。在繁忙的OLTP系统中会影响一部分性能。如果物化视图更新出错，主表也无法提交事务。
  2. 主表和物化视图必须在同一个数据库中
  3. 无法在基表上执行分布式事务
  4. 不支持含有对象类型或Oracle补充支持类型的物化视图

更多的参考：http://blog.itpub.net//viewspace-/

简单总结什么情况下不适合使用物化视图

1.不适合建立物化视图的情况： 基表有频繁的DML操作 / （on commit刷新时很费时，大大增加DML操作的时间）

2.多表连接的物化视图仅适合 refresh complete 定时刷新，且刷新时间不能小于30min，否则弊大于利（complete是删除原有数据重新连接多表数据）

如何查看物化视图

SELECT * FROM user_mviews; SELECT * FROM all_mviews;

如何使用job去刷新物化视图

begin dbms_scheduler.create_job( job_name => 'sam_job', --job名 job_type => 'STORED_PROCEDURE', --job类型 job_action => 'pc_sam', --存储过程名 start_date => sysdate, --开始执行时间 repeat_interval => 'FREQ=MINUTELY;INTERVAL=5', --下次执行时间，每5分钟执行存储过程pc_sam comments => '测试存储过程', --注释 auto_drop => false, --job禁用后是否自动删除 enabled => true); end; /

1、job_name： 任务名称

2、job_type：有三种类型，PL/SQL Block、Stored procedure、Executable

3、job_action：根据job_type的不同，有不同的含义

  • 如果job_type指定的是存储过程，就需要指定存储过程的名字;
  • 如果job_type指定的是PL/SQL块，就需要输入完整的PL/SQL代码;
  • 如果job_type指定的外部程序，就需要输入script的名称或者操作系统的指令名

4、start_date：开始时间

5、repeat_interval：运行的时间间隔，上面例子是每天23点运行一次

6、end_date：到期时间

7、enabled：创建后自动激活

8、auto_drop：默认true,即当job执行完毕都到期是否直接删除job

9、comments：备注

关于schedule时间设置可以查看官网:https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235

关于可以repeat_interval设置可以参考：https://blog.csdn.net/zq/article/details/

删除schedule job

BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => 'my_mv_cd_job' ); END; /

演示如何创建一个schedule job来刷新物化视图

首先我们创建一个存储过程，用来刷新我们的物化视图

create or replace procedure my_mv_cd_procedure_job is begin dbms_mview.refresh('my_mv_cd'); end my_mv_cd_procedure_job; /

然后我们可以通过如下的sql来查询我们的存储过程。。

SELECT * FROM all_source where type = 'PROCEDURE'; SELECT * FROM user_source where type = 'PROCEDURE';

然后我们创建一个schedule job来trigger我们的刚刚定义的存储过程。。

BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my_mv_cd_job', job_type => 'STORED_PROCEDURE', --指定执行的是存储过程 job_action => 'my_mv_cd_procedure_job', --对应的存储过程名称 repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', --1分钟trigger一次 enabled => true ); END; /

然后我们可以通过如下sql查询我们创建的schedule job

--查询 select * from ALL_SCHEDULER_JOBS; select * from USER_SCHEDULER_JOBS;

![image]

我们可以看到执行时间是相隔了1分钟

我们可以试着插入数据

insert into test_person values(4,'justin'); insert into test_address values(4,'HKG',4); commit;

1分钟之后就会发现物化视图trigger刷新了。

在这里插入图片描述

如何删除物化视图

drop materialized view {物化视图名称};

物化视图日志

物化视图的Fast刷新

[67000][439] ORA-00439: feature not enabled: Advanced replication

create materialized view log on test_kevin with primary key including new values;

当我在本地尝试创建物化视图日志的时候发生了如下错误：

[67000][439] ORA-00439: feature not enabled: Advanced replication

我们可以通过如下sql查看我们db开启的功能：

select * from v$option where parameter = 'Advanced replication';

通过查看我们可以知道我们的高级复制功能是没有被开启的。不知道是当时安装有问题还是可能这是一个加钱才会有的功能吧。

创建物化视图日志

如果我们想要创建fast模式刷新的物化视图就一定要对关联的基表都需要创建物化视图日志。

Oracle 的物化视图的快速刷新要求必须建立物化视图日志，通过物化视图日志可以实现增量刷新功能。

  • 物化视图日志在建立时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECTID几种类型，同时还可以指定SEQUENCE或明确指定列名。不过上面这些情况产生的物化视图日志的结构都不相同。
  • 当发生DML 操作时，内部的触发器会把变化记录到物化视图日志里，也就是说物化视图不支持DDL的同步，所以在物化视图的编写过程中不可使用select * from 的形式，因为这样当基表发生变化时，物化视图就会失效。

物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。

任何物化视图都会包括的4列：

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

演示创建物化视图with primary key

我们首先创建一张表

create table test_person(pid int,name varchar2(20)); alter table test_person add constraint test_pk_pid primary key(pid);

然后我们创建物化视图日志，指定primary key

create materialized view log on test_person with primary key;

然后我们可以向基表test_person加入一条数据。

insert into test_person values(1,'kevin');

然后在update一条数据

update test_person set name='kevin cai' where pid = 1;

然后我们通过如下sql可以查看物化视图日志的内容。

select * from mlog$_test_person;

在这里插入图片描述

including new values

我们创建物化视图日志的时候还可以带上including new values

create materialized view log on test_person with primary key including new values;

NEW VALUES子句允许Oracle数据库将新旧值都保存在物化视图日志中，以便更新DML操作。

更多参考：

http://www.itpub.net/forum.php?mod=viewthread&tid=2052180&highlight=

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6003.htm#i2119924

演示创建物化视图with rowid

CREATE MATERIALIZED VIEW LOG on test_person with rowid INCLUDING NEW VALUES;

在这里插入图片描述

使用场景，具体不是很了解，可以参考：https://blog.csdn.net/demonson/article/details/

实现物化视图的增量刷新

我们先创建两张表

create table test_person(pid int,name varchar2(20)); alter table test_person add constraint test_pk_pid primary key(pid); create table test_address(aid int,address varchar2(20),pid int); alter table test_address add constraint test_pk_aid primary key(aid); 
CREATE MATERIALIZED VIEW LOG on test_person with rowid INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG on test_address with rowid INCLUDING NEW VALUES;

然后我们就可以创建fast刷新模式的物化视图。。

create materialized view my_test_mv refresh fast WITH ROWID as select p.pid as p_id, p.name, a.address, p.ROWID as p_ROWID,a.ROWID as a_ROWID from test_person p, test_address a where p.pid = a.pid;

而且建MV时一定要加上with rowid，因为没有聚合函数的MV默认是with primary key

更多参考：https://blog.csdn.net/minwang593/article/details/

这时候我们可以尝试往基表插入数据

insert into test_person values(1,'kevin'); insert into test_address values(1,'SHA',1); insert into test_person values(2,'vincent'); insert into test_address values(2,'HKG',2);

我们可以看到物化视图日志中都有数据

select * from mlog$_test_person; select * from mlog$_test_address;

在这里插入图片描述

但是物化视图中还没有数据。我们可以通过手动方式刷新物化视图中的数据

BEGIN DBMS_MVIEW.REFRESH ( list => 'my_test_mv', Method =>'C', refresh_after_errors => True); END;

而且一旦物化视图刷新，关联基表的物化视图日志也会被清空。。

我们可以通过如下sql删除物化视图和物化视图日志：

--删除物化视图 drop materialized view my_test_mv; --删除物化视图日志 drop materialized view log on test_person; drop materialized view log on test_address;

然后我们可以试试以外连接的语法来创建物化视图

create materialized view my_test_mv refresh fast WITH ROWID as select p.pid as p_id, p.name, a.address, p.ROWID as p_ROWID,a.ROWID as a_ROWID from test_person p left join test_address a on p.pid = a.pid;

然后我们发现我们创建物化视图的时候报错如下：

[72000][12015] ORA-12015: cannot create a fast refresh materialized view from a complex query

这是因为物化视图快速刷新不支持标准外联接写法，必须要改写sql

select * from a,b where a.id(+)=b.id;--b为主表,a为补充 select * from a,b where a.id=b.id(+);--a为主表,b为补充

所以我们将我们的sql改写为

create materialized view my_test_mv refresh fast WITH ROWID as select p.pid as p_id, p.name, a.address, p.ROWID as p_ROWID,a.ROWID as a_ROWID from test_person p, test_address a where p.pid = a.pid(+);

更多参考：https://www.cnblogs.com/tracy/archive/2011/09/01/2162080.html

总结一下创建fast模式物化视图条件：

多表连接的物化视图想要使用fast刷新时,

首先,select 语句中包含到的每一个表都需要创建视图日志;

其次,视图日志中需要指定在select语句和where条件中用到的该表的字段;

第三,在select中必须包含所涉及到的所有表的rowid,

第四,外连接不能使用标志sql语法,应使用(+)这种oracle固有语法.

当然还有很多其他的限制条件，可以参考：https://blog.csdn.net/aiyocxuan/article/details/

使用物化视图增量刷新的优缺点

我个人觉得使用增量模式刷新物化视图的优点就是不需要对整个物化视图进行刷新，当物化视图的数据特别大的时候可以减少刷新的时间，其他的优点没有暂时还没感受到。

缺点：

2.然后对基表的DML和对物化视图日志的更新应该是在同一个事务里的，因为rollback的时候是会一起rollback的，所以如果物化视图日志一旦失败可能导致基表的DML操作失败，对业务上可能会影响。

oracle通过v$sql视图查询sql的平均执行时间

SELECT SQL_TEXT,EXECUTIONS "总执行次数",ELAPSED_TIME/1000 "总耗时(秒)", ELAPSED_TIME/nvl(EXECUTIONS,1)/1000 "平均耗时（秒）", PARSE_CALLS "硬解析次数",DISK_READS "物理读次数",BUFFER_GETS "读缓存区次数" FROM v$SQL WHERE SQL_TEXT LIKE '%select * from t1%';

更多参考：https://blog.csdn.net/yh_zeng2/article/details/

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/200348.html原文链接：https://javaforall.net

赞 (0)
0 0

关于作者

全栈程序员-站长的头像

全栈程序员-站长

133.5K 文章
3 粉丝
本网站汇聚当前互联网主流语音，持续更新，欢迎关注公众号“全栈程序员社区”
上一篇 2026年3月20日 上午11:00
下一篇 2026年3月20日 上午11:01


相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注全栈程序员社区公众号