drop 很慢 物化视图_【ORACLE】导致物化视图快速刷新变慢的原因和解决

ORACLE物化视图(MATERIALIZED VIEW)有自动提交刷新和手动刷新两种刷新方式，每种刷新方式下有增量刷新(FAST)、全量刷新(COMPLETE)、系统自动判断(FORCE)三种刷新方法。一般而言，增量刷新理论上是比全量刷新要快一些的，但是我发现某些情况下增量刷新将导致基表提交后延迟严重，下面将做一些测试。

测试环境操作系统：Red Hat Enterprise Linux Server release 5.8 虚拟机

处理器：Intel(R) Core(TM) i7-4700MQ CPU @ 2.40GHz

内存大小：4G

数据库：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

准备测试数据

创建三个表结构相同的测试表，id为自增长主键，这三个表作为物化视图的基表。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17create table t1 (

id INT,

c1 INT,

c2 INT,

constraint pk1 primary key(id));

create table t2 (

id INT,

c1 INT,

c2 INT,

constraint pk2 primary key(id));

create table t3 (

id INT,

c1 INT,

c2 INT,

constraint pk3 primary key(id));

每个表插入30000行数据，并给三个基表创建物化视图日志(MATERIALIZED VIEW LOG)：

1

2

3

4

5CREATE MATERIALIZED VIEW LOG ON t1 WITH ROWID,SEQUENCE(id,c1,c2) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON t2 WITH ROWID,SEQUENCE(id,c1,c2) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON t3 WITH ROWID,SEQUENCE(id,c1,c2) INCLUDING NEW VALUES;

物化视图中不包含列计算

首先创建的物化视图如下：

1

2

3

4

5

6

7

8

9

10CREATE MATERIALIZED VIEW mv1

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

WITH ROWID

AS

SELECT a.id aid,a.c1 ac1,a.c2 ac2,b.c1 bc1,b.c2 bc2,

a.rowid rida,b.rowid ridb

FROM t1 a,t2 b

WHERE a.id=b.id(+)

AND a.c1=b.c1(+);

该物化视图采用的是基表提交(commit)触发快速刷新(FAST)的方式刷新数据，物化视图为两个基表t1,t2的外联接，SELECT后面的列只是简单列出t1,t2表中的列而已。

更新外联接主表数据1select * from mv1 where aid = 9;

1update t1 set c2=3 where id = 9;

可见提交耗时很短，再查看物化视图数据，已经更新。

使用如下语句查询物化视图的刷新方式，刷新时间等信息。

1

2

3

4

5

6

7

8

9SELECT MVIEW_NAME,

REFRESH_MODE,

REFRESH_METHOD,

FAST_REFRESHABLE,

LAST_REFRESH_TYPE,

LAST_REFRESH_DATE,

STALENESS

FROM USER_MVIEWS

WHERE MVIEW_NAME = 'MV1';

更新外联接非主表数据

更新非主表t2的同一行的数据，提交后耗时也很短。

1update t2 set c2=13 where id = 9;

所以这种情况下，物化视图的增量刷新是没有多少延迟的。

物化视图中包含列计算

将MV1物化视图删除，重新创建一个。

1

2

3

4

5

6

7

8

9

10

11

12DROP MATERIALIZED VIEW mv1;

CREATE MATERIALIZED VIEW mv1

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

WITH ROWID

AS

SELECT a.id aid,a.c1 ac1,a.c2 ac2,b.c1 bc1,b.c2 bc2,

a.c2+b.c2 as d,

a.rowid rida,b.rowid ridb

FROM t1 a,t2 b

WHERE a.id=b.id(+)

AND a.c1=b.c1(+);

在之前的基础上增加了d列，这列是t1和t2基表中各一列的计算。

更新外联接主表数据

测试更新主表数据，查看提交耗时。

1select * from mv1 where aid = 9;

1update t1 set c2=6 where id = 9;

基表数据提交时延迟很小。

更新外联接非主表数据

测试更新t2表数据，查看提交耗时。

1update t2 set c2=1 where id = 9;

基表更新数据提交时，显示20多秒才完成，显然，这种情况会导致物化视图增量刷新延迟严重。

将MV1删除后重建为下列情况，相加的两列是同一个表的两列，这时再更新t2表的一行数据，发现提交没有延迟。

1

2

3

4

5

6

7

8

9

10

11DROP MATERIALIZED VIEW mv1;

CREATE MATERIALIZED VIEW mv1

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

WITH ROWID

AS

SELECT a.id aid,a.c1 ac1,a.c2 ac2,b.c1 bc1,b.c2 bc2,

b.c1+b.c2 as d,

a.rowid rida,b.rowid ridb

FROM t1 a,t2 b

WHERE a.id=b.id;

一种解决方案

既然含有外联接的物化视图多表列运算有问题，可以在MV1中将运算列都SELECT出来，再创建一个嵌套物化视图MV2，将MV1中的列进行运算处理。

物化视图MV1创建如下：

1

2

3

4

5

6

7

8

9

10

11

12CREATE MATERIALIZED VIEW mv1

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

WITH ROWID

AS

SELECT a.id aid,a.c1 ac1,a.c2 ac2,b.c1 bc1,b.c2 bc2,

a.rowid rida,b.rowid ridb

FROM t1 a,t2 b

WHERE a.id=b.id(+)

AND a.c1=b.c1(+);

--因为MV1将作为MV2的基本表，所以要建物化视图日志

create materialized view log on mv1 with rowid,sequence(aid) including new values;

物化视图MV2创建如下：

1

2

3

4

5

6

7

8

9

10create materialized view mv2

build immediate

refresh fast on commit

with rowid

as

select aid,ac1,ac2,bc1,bc2,

ac2+bc2 as d, --原本在MV1中计算的列在这里计算

a.rowid rida,b.rowid ridb

from mv1 a,t3 b --这里注意，在这个ORACLE版本中，创建嵌套物化视图必须要有表关联或者聚合计算，单独from mv1创建不了MV2.

where a.aid=b.id(+);

查看初始数据select * from mv2 where aid = 99;

更新t2表数据update t2 set c2=37 where id = 99;，查看提交耗时

查看MV2结果

查看物化视图刷新情况

可见，两个物化视图在基表数据更新后提交不会耗时很久。用这种方式将一个运算分开两步完成，虽然可行，但是代价也很明显，要多创建嵌套物化视图，不敢保证复杂情况下，嵌套物化视图会有其他问题。

另两种解决方案1.将含有多表列运算的外联接快速刷新物化视图改为定时全量刷新。

2.将外联接重写为内连接。

最终解决

经测试，除以上方法外，有一个方法比较完美的解决延迟问题的方案，就是在物化视图上的ROWID列创建索引。测试如下：

这里做了一个相对复杂的嵌套物化视图测试。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31drop MATERIALIZED VIEW mv1;

CREATE MATERIALIZED VIEW mv1

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

WITH ROWID

AS

SELECT a.id aid,a.c1 ac1,a.c2 ac2,b.c1 bc1,b.c2 bc2,

a.c2+b.c2 as d,

(a.c2+b.c2)*b.c1 as e,

a.rowid rida,b.rowid ridb

FROM t1 a,t2 b

WHERE a.id=b.id(+)

AND a.c1=b.c1(+);

--在mv1上所有rowid创建索引

create index idx1 on mv1(rida,ridb);

--创建mv1物化视图日志

create materialized view log on mv1 with rowid(aid,ac1,ac2,bc1,bc2) including new values;

--mv2引用mv1

CREATE MATERIALIZED VIEW mv2

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

WITH ROWID

AS

select a.aid,a.ac1,a.ac2,b.c1 bc1,b.c2 bc2,a.e,

a.e+b.c2 as d,

a.rowid rida,b.rowid ridb

from mv1 a,t2 b

where a.aid=b.id(+)

and a.ac1=b.c1(+);

--在mv2上所有rowid创建索引

create index idx2 on mv2(rida,ridb);

以上脚本，创建了两个物化视图mv1,mv2，每个物化视图都含有外联接，且select列中含有多个表列结算，mv1嵌套在mv2中，基表t2被两个物化视图引用，且t2的字段在两个物化视图中都参与计算，这样的情况应该算比较复杂了，注意每个物化视图都建立索引。

查询mv2初始值:

select * from mv2 where aid = 509;

修改基表t2的数据：

update t2 set c2=85 where id = 509;

查看结果：

select * from mv2 where aid = 509;

查看物化视图状态：

可见物化视图为增量刷新，且没有之前发生的严重延迟的情况，需要注意的是，物化视图都要建立索引，且索引列都是包含rowid列的复合索引，如果是其他不是rowid列索引，可能没有任何效果。经测试，对基表增删改都是有效果的。

总结1.创建含有外联接且刷新方式为REFRESH FAST ON COMMIT的物化视图时，如果select列里含有多个表中取某些列做运算，注意了，这时更新非主表的数据(增删改)，那么基表提交数据后将有可能导致延迟阻塞，这个时间比物化视图全量刷新还要慢。

2.除上述情况，例如物化视图都是内连接，有外联接但是没有多个表列做运算等，增量刷新都没问题。

3.至于多表列运算的外联接物化视图增量刷新很慢的原因，与ORACLE物化视图的刷新机制有关，官方文档也有提及这种情况会影响效率，建议最好改为内连接。

4.解决方法是改为定时全量刷新或者改外联接为内连接或者利用嵌套物化视图，或者还有其他方法，总之就是为了避开这种情况。

3.比较靠谱的解决方案是在多表列运算的外联接物化视图上创建包含rowid列的索引，创建与不创建索引在这个问题上显现出天壤之别。