物化视图:
物化视图是一种特殊的物理表,用于预先计算并保存表连接或聚集等耗时较多的操作的结果,在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。“物化”(Materialized)视图是相对普通视图而言的,普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。
物化视图的特点:
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证;
(2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;
(3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证;
物化视图可以分为以下三种类型:包含聚集的物化视图;只包含连接的物化视图;嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别,而对于其他方面则区别不大。
物化视图创建的选项:
1、创建方式(Build Methods)
包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据,而BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。
2、查询重写(Query Rewrite)
包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断 能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。
3、刷新(Refresh)方式
指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。
刷新的模式有两种:ON DEMAND和ON COMMIT。 默认值是ON DEMAND
ON DEMAND->指物化视图在用户需要的时候进行刷新,可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新,也可以通过JOB定时进行刷新。
ON COMMIT->指出物化视图在对基表的DML操作提交的同时进行刷新。
刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER。默认值是FORCE。
FAST ->刷新采用增量刷新,只刷新自上次刷新以后进行的修改。建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on (主表名)。
COMPLETE ->刷新对整个物化视图进行完全的刷新。
FORCE ->Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。
NEVER ->物化视图不进行任何刷新。
刷新方式:
1、手工刷新:begin dbms_mvview.refresh('物化视图名称'); end;
2、自动刷新:start with (start_time) next (next_time)会自动创建一个JOB,然后由JOB自动去刷新物化视图.,这个job中的执行内容是:dbms_refresh.refresh('"WANGXIAOQI"."T1_MV"'); 而不是
我们普通手动刷新MV时用的 dbms_mview.refresh 。这个包是用于产生一个刷新组以方便MV一组为单位统一刷新的。而当MV被制定刷新策略的方式指定时,会自动创建一个刷新组,并将该MV添加至
这个刷新组中,所以job可以使用dbms_refresh.refresh来进行刷新.可以通过查询数据字典all_refresh 、all_refresh_children 来查看。 所以当物化视图刷新脚本自动执行时,刷新的是你所创建的MV的名
字命名的刷新组,而不是单纯得刷新这个MV。
物化视图的优缺点:
优点:
1,物化视图的最大的优势是可以提高性能:Oracle的物化视图提供了强大的功能,可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。
2, 物化视图有很多方面和索引很相似
3,通过预先计算好答案存储起来,可以大大地减少机器的负载
A,更少的物理读--扫描更少的数据
B,更少的写--不用经常排序和聚集
C。减少CPU的消耗--不用对数据进行聚集计算和函数调用
D,显著地加快响应时间--在使用物化视图查询数据时(与主表相反),将会很快的返回查询结果
缺点:
1,物化视图用于只读或者“精读”环境下工作最好 ,不用于联机事务处理系统(OLTP)环境, 在事实表等更新时会导致物化视图行锁,从而影响系统并发性。
2,物化视图有出现无法快速刷新,导致查询数据不准确的现象
3,Rowid物化视图(创建的物化视图通常情况下有主键,rowid,和子查询视图)只有一个单一的主表,不能包括下面任何一项:
A,Distinct 或者聚合函数.
B,Group by,子查询,连接和SET操作
4,物化视图会增加对磁盘资源的需求,即需要永久分配的硬盘空间给物化视图来存储数据
5,物化视图的工作原理受一些可能的约束,比如主键,外键等等
创建语法:
--给表建实体化视图日志(FAST方式刷新的物化视图需要)
create {materialized view | snapshot} log on <tab>
[tablespace <ts>]
[storage (…)]
[pctfree <10>]
[pctused <40>]
[initrans <1>]
[maxtrans <n>]
[logging | nologging]
[cache | nocache]
[noparallel | parallel [<n>]]
[partition…]
[lob…]
[using index…]
[with [primary key] [, rowid] [(<col> [, …])] ]
[{including | excluding} new values];
alter {materialized view | snapshot} log on <tab> [add [primary key] [, rowid] [(<col> [, …])] ] […];
drop {materialized view | snapshot} log on <tab>;
--新建实体化视图(snapshot是快照,这里不解释)
create {materialized view | snapshot} <mview>
[tablespace <ts>] --表空间
[storage (…)] --段
[pctfree <10>]
[pctused <40>]
[initrans <1>]
[maxtrans <n>]
[logging | nologging]
[cache | nocache]
[noparallel | parallel [<n>]]
[cluster <clust> (<col> [, …])]
[lob…]
[partition…]
[build {immediate | deferred}] --创建方式 immediate:创建的时候生成数据 deferred:延迟生成数据
[on prebuilt table [{with | without} reduced precision]]
[using index…]
[refresh [fast | complete | force] --刷新方式:fast:快速刷新(需要给物化的表建物化视图日志) complete:完全刷新 force:
[on commit | on demand] --刷新的模式 commit:基表数据提交时候刷新视图 demand:指定方式刷新,在用户需要的时候进行刷新
[start with ‘<date>’] [next ‘<date>’] --第一次刷新的时间点以及下次刷新的时间 其实质是新建一个DBMS_JOB
[with {primary key | rowid}] --快速刷新时候唯一标示一条记录
[using [default]
[master | local]
rollback segment [<rbs>]
[never refresh ]
[for update]
[{enable | disable} query rewrite]--查询重写:
as <query>;--视图的查询语句
alter {materialized view | snapshot} <mview> … [compile];
drop {materialized view | snapshot} <mview>;
快速刷新条件
1.物化视图不能包含对不重复表达式的引用,如SYSDATE和ROWNUM;
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。
(以下是多表关联的查询的快速物化需要增加的条件)
3.不能包括GROUP BY语句或聚集操作;
4.如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上;
5.如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作。
6.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
7.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。
物化视图日志结构(dba_mview_logs)
物化视图的快速刷新要求基本必须建立物化视图日志,这篇文章简单描述一下物化视图日志中各个字段的含义和用途。
物化视图日志的名称为MLOG$_后面跟基表的名称,如果表名的长度超过20位,则只取前20位,当截短后出现名称重复时,Oracle会自动在物化视图日志名称后面加上数字作为序号。
物化视图日志在建立时有多种选项:可以指定为ROWID、PRIMARY KEY和OBJECT ID几种类型,同时还可以指定SEQUENCE或明确指定列名。上面这些情况产生的物化视图日志的结构都不相同。
任何物化视图都会包括的列:
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名称,则物化视图日志中会包含这些列。