oracle物化视图

官方文档:

https://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#REPLN335

1.1 概念

物化视图^[1] （Materialized View）在9i以前的版本叫做快照（SNAPSHOT)，从9i开始改名叫做物化视图。它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

简单说,物化视图不仅存储了sql的定义,还存储了数据;它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。

物化视图可以查询表，视图和其它的物化视图。

特点：

(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到确认；

(2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；

(3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；

创建语句：create materialized view mv_name asselect * from table_name

因为物化视图由于是物理真实存在的，故可以创建索引。

字典:

--物化视图日志字典,可查看具体的日志表

select* from dba_MVIEW_LOGS;

--查看物化视图,可以查到定义的各种条件以及更新时间,以及查询语句等

select* from DBA_MVIEWS;

--物化视图跟源对象的对应关系

select * from all_mview_refresh_times aa whereaa.MASTER='EMP'

and aa.NAME='MY_EMP_VIEW';

1.1.1 语法

create materialized view view_name

]

AS 查询语句;

默认: build immediate,refresh force,on demand,disablequery rewrite

含义:

1创建时生成数据：

分为两种：build immediate 和 build deferred，

build immediate是在创建物化视图的时候就生成数据。

build deferred则在创建时不生成数据，以后根据需要在生成数据。

如果不指定，则默认为buildimmediate。

2刷新模式：

物化视图有二种刷新模式：

在创建时refresh mode是 ondemand 还是 on commit。

on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新;

如果选择on commit ，则在对主表应用上会造成速度，这是因为ORACLＥ在对主表操作提交后马上会进行刷新物化视图操作，这部分时间是也包括在提交时间中。

a) refresh complete on commit:完全更新,不用建日志表,缺点是提交时间长。

b) refreshfast on commit:必须先建立日志表,增删改都能同步 ,oracle11g经过测试得出;

c) refresh force oncommit:先走fast,不行走complete

on demand:手动刷新同步,调用过程DBMS_MVIEW.REFRESH,也可以建立job调用过程更新;

相对于commit,只不过手动调用而已;

a) refresh completeon DEMAND:全量刷新

b) refresh fast on DEMAND: 同commit必须先建立日志表

通过调用DBMS_MVIEW.REFRESH存储过程来进行数据的刷新同步;

c) refresh force on DEMAND: 先走fast,不行走complete

3细节如下:

完全刷新（COMPLETE）：会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。

快速刷新（FAST）：采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

关于快速刷新fast：Oracle物化视图的快速刷新机制是通过物化视图日志完成的。

Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。

快速更新必须要有物化视图日志表,不论是commit还是demand;

4查询重写（QueryRewrite）：

包括 enablequery rewrite 和 disable query rewrite 两种。

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

默认为disable queryrewrite。

5fast刷新的限制：

　　所有类型的快速刷新物化视图都必须满足的条件：

　　1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；

　　2.物化视图不能包含对LONG和LONG RAW数据类型的引用。

　　只包含连接的物化视图：

　　1.必须满足所有快速刷新物化视图都满足的条件；

　　2.不能包括GROUP BY语句或聚集操作；

　　3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上；

　　4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。

　　5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。

1.2 物化视图日志

Following are the types of materializedview logs:

Primary Key: The materialized view records changes to the master table or master materialized view based on the primary key of the affected rows.
Row ID: The materialized view records changes to the master table or master materialized view based on the rowid of the affected rows.
Object ID: The materialized view records changes to the master object table or master object materialized view based on the object identifier of the affected row objects.
Combination: The materialized view records changes to the master table or master materialized view based any combination of the three options. It is possible to record changes based on the primary key, the ROWID, and the object identifier of the affected rows. Such a materialized view log supports primary key, ROWID, and object materialized views, which is helpful for environments that have all three types of materialized views based on a master.

翻译：

主键：物化视图根据受影响行的主键记录对主表或主物化视图的更改。

行ID：物化视图根据受影响行的rowid记录对主表或主物化视图的更改。

Object ID：物化视图根据受影响行对象的对象标识符记录对主对象表或主对象物化视图的更改。

组合：物化视图记录基于这三个选项的任意组合对主表或主物化视图的更改。可以根据主键、ROWID和受影响行的对象标识符记录更改。这样的物化视图日志支持主键、ROWID和对象物化视图，这对于基于主目录拥有所有三种类型物化视图的环境很有帮助。

物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

底层应该还是用到了类似的行级触发器;

如:

create materialized view log on emp

--可以指定表空间tablespacetest_space -- 日志空间

with primary key;

--查看物化视图的刷新情况

select *from USER_MVIEW_LOGS;

物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，

当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。

通过这个字典可以查找到具体的日志表;

通过日志表就可以知道更新情况;

--查看日志表

select *from MLOG$_EMP ;

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

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

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

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

当刷新完成后MLOG$_EMP相应的日志记录,会被清空;

在刷新(同步)物化视图之前你可以做各种DML操作;

在你提交之前就会记录到日志表;

1.3 例子

都是自已亲测的,有什么不对的地方还望指出,谢谢

1.3.1 demand方式

--授权

grant creatematerialized viewto SCOTT;

--创建

create materializedview my_emp_view

build immediate

refresh force

on demand

disable query rewrite

select *from emp;

--等价下面的创建

create materializedview my_emp_view

select *from emp;

--可以发现,如果原表emp有主键,那么在创建物化视图的时候,会自动创建基于主键的索引在MY_EMP_VIEW中;

select *from all_indexes a where a.TABLE_NAMEin('EMP','MY_EMP_VIEW');

--建立非主键的索引,建立的索引跟源表没有关系

create index mater_idn_enameon my_emp_view(ename);

--查询物化视图信息

select *from all_tables aa where aa.TABLE_NAME='MY_EMP_VIEW';

--插入一条数据

Insert into emp(empno,ename,sal)values(1112,'无话2',9999);

select *from emp;

--此时查询发现物化视图没有主动更新

select *from my_emp_view;

--手动刷新

--'?' use fastrefresh when possible

--'F' use fastrefresh or raise an error if not possible

--'C' perform acomplete refresh, copying the entire snapshot from

-- the master

begin

--如果要快速刷新,那么必须建立日志表

-- dbms_mview.refresh('MY_EMP_VIEW','F');

--如果全量更新,不需要建立日志表

-- dbms_mview.refresh('MY_EMP_VIEW','C');

dbms_mview.refresh('MY_EMP_VIEW');--等价?

end;

此时如果执行会报错;没有创建日志表

--创建日志表

create materializedview log on emp

--可以指定表空间tablespace test_space --日志空间

with primary key;

再插入一条数据:

Insert into emp(empno,ename,sal)values(1114,'无话4',9999);

begin

dbms_mview.refresh('MY_EMP_VIEW','F');

end;

又报错了,应该是日志表后建造成的;

我们全量更新下就可以了;

begin

dbms_mview.refresh('MY_EMP_VIEW','C');

end;

--查看物化视图的更新等信息

select * from all_mviewsaa where aa.MVIEW_NAME='MY_EMP_VIEW';

再插条数据:

Insert into emp(empno,ename,sal)values(1115,'无话5',5555);

begin

dbms_mview.refresh('MY_EMP_VIEW','F');

end;

你会发现同步成功了;

查看日志表:是空的,同步完成会清空记录

select * from MLOG$_EMP

这种demand相对于commit有什么优势吗?人家刷新前,你可以做各种各种的DML操作,最后可以手动一次性提交,效率肯定比commit高;

--删除物化视图

drop materializedview my_emp_view;

--删除物化视图的日志表

drop materializedview log on 源表名;

小结一下正确的创建流程:

1先创建物化视图

create materializedview my_emp_view

select *from emp;

2.建立物化视图的索引

3.如果要使用增量更新,建立日志表

create materializedview log on emp

with primary key;

4.需要的时候调用过程更新;

begin

dbms_mview.refresh('MY_EMP_VIEW','F');

--dbms_mview.refresh('MY_EMP_VIEW');

end;

1.3.2 commit方式

drop materializedview my_emp_view;

drop materializedview log on emp;

第一种方式:

--commit式更新物化视图,且更新方式为force

create materializedview my_emp_view

refresh on commit

select *from emp;

--此时提交会自动更新

update emp e set e.ename='你好3'where e.empno=1113;

--此时也会自动更新

delete from emp ewhere e.empno=1115;

select * from my_emp_view;

他其实底层走的都是全量更新;因为没建日志表;

第二种方式:

drop materializedview my_emp_view;

--commit式更新物化视图,且更新方式为fast

create materializedview my_emp_view

refresh fast on commit

select * from emp;

会直接报错:也就是说:快速更新必须要有物化视图日志表,不论是commit还是demand;

所以呢,我们创建一个日志表:

create materializedview log on emp

with primary key;

再创建物化视图就可以了;

--此时提交后,可以看到物化视图自动刷新,如果没猜错,是commit的时候,应该自动调用了刷新过程;

update emp e set e.ename='你好3' where e.empno=1113;

--再看看删除

delete from emp e where e.empno=1113;

select *from MLOG$_EMP;--查看日志表

提交后可以看到,1113已经被删除了;

小结:在使用commit的时候,在建立有日志表的情况下,

使用

create materializedview my_emp_view

refresh fast on commit

select * from emp;

考虑到fast的场景限制,可以使用force,这样更新万无一失;

create materializedview my_emp_view

refresh on commit

select * from emp;

1.3.3定时调用

方式:

1.物化视图本身可以设置自动调用时间

2.也可用定时job调用过程;

drop materialized view my_emp_view;

drop materialized view log on emp ;

--1.先建立日志表:

create materializedview log on emp

with primarykey;

--2.创建物化视图 demand

--比如设置每2分钟更新,这里没有指定开始执行时间,默认是当前时间

create materializedview my_emp_view

refresh fast on demand

next sysdate+2/(24*60)as

select *from emp;

--测试

select *from emp;

update emp e set e.ename='77777'where e.empno=1111;

select *from my_emp_view;

--物化视图日志字典.会更新日志时间

select b.MASTER,b.LOG_TABLE,b.ROWIDS,b.PRIMARY_KEY,b.LAST_PURGE_DATE from USER_MVIEW_LOGS b;

--查看物化视图 LAST_REFRESH_DATE更新时间

select aa.*from DBA_MVIEWS aa where aa.MVIEW_NAME='MY_EMP_VIEW';

select * from MLOG$_EMP ;

--指定每天的20:02执行更新

create materializedview my_emp_view

refresh fast on demand

start withsysdate --设置开始时间

next (TRUNC(SYSDATE+1)+（20*60+2）/(24*60))

select *from emp;

--如果你想现在就测试的话:

--设置每天的20:02分钟执行

create materializedview my_emp_view

refresh fast on demand

start with (trunc(sysdate)-1) --设置开始时间,当前时间减一天

next (TRUNC(SYSDATE)+（20*60+2）/(24*60))

select *from emp;

关于时间的设置:跟dbms_job的interval参数类似;

--如果设置成commit呢,行不行,不行啊,commit本身就是提交的时候自动更新,这个时候,你用定时提交,就没有意义了;

create materializedview my_emp_view

refresh fast on commit

next sysdate+2/(24*60)as

select *from emp;

1.4 修改物化视图

ALTER MATERIALIZED VIEW emp_data

   REFRESH  [fast|complete|force] 
[ 
on [commit|demand] | 
start with (start_time) next (next_time) 
]

另外,物化视图支持分区创建;

create materialized view mv_t3

partition by range(day_date)

interval(numtoyminterval(1, 'month'))

(

PARTITION P1 VALUES LESS THAN (TO_DATE('20170101', 'YYYYMMDD'))

) tablespace users

nologging

build immediate refresh FAST

WITH PRIMARY KEY

on demand

enable query rewrite

as SELECT id,day_date from t3;

参考链接:

https://blog.****.net/qq_26941173/article/details/78529041

https://blog.****.net/rebirth_moring/article/details/1733972

1.1 概念

1.1.1 语法

1.2 物化视图日志

1.3 例子

1.4 修改物化视图

相关推荐