物化视图(MV)是在Oracle Database 7中引入的技术。改功能原来称为快照,现在术语“快照”用于描述一下数据字典结构。MV可以执行实时的SQL查询操作,并将操作
结果存储到表(本地或远程数据库)中。创建了MV后,可以在将来重新执行MV查询操作,并将刷新的结果存储到基础表中。MV主要有两种用法:
1.复制数据,是其他报告数据库分担查询负载;
2.定期计算和存储发咋的和聚合数据,提高查询操作的性能,使用户得到实时结果。
MV可以使查询表,视图和其他MV的结果。在创建MV时,Oracle会在内部创建一个表(名称与MV相同)和一个MV对象(可以用DBA/ALL/USER_OBJECTS视图查看)。
--MV的术语
术 语 意 义
物化视图(MV) 用于复制数据和提高查询操作性能的数据库对象
MV SQL语句 用来定义在MV基础表中存储那些数据的SQL查询语句
MV基础表 名称与MV相同的数据库表,用于存储MV SQL查询操作的结果
主表 在MV SQL语句的FROM子句中引用的表
完全刷新 将MV中的内容彻底删除,使用MV SQL语句刷新结果的过程
快速刷新 对MV执行了最后一次刷新后,仅进行DML更改(对基础表)的过程
MV日志 用于跟踪MV基础表DML更改的数据库对象。进行快速刷新时需要使用MV日志。MV日志可以根据主键,ROWID或对象ID创建。
简单MV 用简单查询操作创建的,能够快速刷新的MV
复杂MV 用复杂查询操作创建的,不能快速刷新的MV
创建模式 MV生成数据的模式,立刻或延迟生成数据
刷新模式 MV的刷新模式,根据需要刷新,提交后刷新或不刷新
刷新方法 MV的刷新方法,完全刷新或增量刷新
查询重写 使优化器能够使用MV(而不是主表)满足查询需求的功能(包括在查询操作无法直接饮用MV的情况)
本地MV MV与基础表存储在同一数据库中
远程MV MV与基础表存储在不同数据库中
刷新组 在相同事物处理点刷新的MV集合
--MV的数据字典视图
数据字典视图 内 容
DBA/ALL/USER_MVIEWS 含有MV的信息,如所有者,基础查询语句,上一次刷新时间等。
DBA/ALL/USER_MVIEW_REFRESH_TIMES MV的上一次刷新时间,MV的名称,MV的主表和主表的所有者。
DBA/ALL/USER_REGISTERED_MVIEWS 所有已注册的MV,可帮助辨别那些MV使用哪些MV日志
DBA/ALL/USER_MVIEW_LOGS MV日志
DBA/ALL/USER_BASE_TABLE_MVIEWS 基础表名称和MV日志表的上一次刷新日期
DBA/ALL/USER_MVEW_AGGREGATES 在SELECCT子句中用于处理MV的聚合函数
DBA/ALL/USER_MVIEW_ANALYSIS MV的信息。Oracle公司建议使用DBA/ALL/USER_MVIEWS视图代替这些三视图
DBA/ALL/USER_MVIEW_COMMENTS MV的所有注释
DBA/ALL/USER_MVIEW_DETAIL_PARTITION 分区和刷新信息
DBA/ALL/USER_MVIEW_DETAIL_SUBPARTITION 子分区和刷新信息
DBA/ALL/USER_MVIEW_DETAIL_RELATIONS MV用作基础的本地表和其他MV
DBA/ALL/USER_MVIEW_JOINS MV定义中WHERE子句中的列连接条件
DBA/ALL/USER_MVIEW_KEYS MV中定义SELECT子句中的列或表达式
DBA/ALL/USER_TUNE_MVIEW 执行DBMS_ADVISOR.TUNE_MVIEW过程的结果
V$MVREFRESH MV当前刷新的信息
DBA/ALL/USER_REFRESH MV当前组的细节
DBA_RGROUP MV组的刷新信息
DBA_RCHILD MV刷新组中的成员
--创建基本物化视图
--创建测试表
create table sales (
sales_id number,
sales_amt number,
region_id number,
sales_dtt date,
constraint sales_pk primary key (sales_id));
--插入测试数据
insert into sales values (1,101,10,sysdate-10);
insert into sales values (2,511,20,sysdate-20);
insert into sales values (3,11,30,sysdate-30);
--创建完全刷新MV
create materialized view sales_daily_mv
segment creation immediate
refresh
complete
on demand
as
select sum(sales_amt) sales_amt,trunc(sales_dtt) sales_dtt from sales group by trunc (sales_dtt);
Oracle Database 11g Release 2 和更高的版本都支持 SEGMENT CREATION IMMEDIATE 子句。该子句会使Oracle 在你创建MB时创建段和区。这是较早的Oracle版本会执行的操作。如果你不想立刻创建段,可使用SEGMENT CREATION DEFERRED 子句。
--通过USER_MVIEWS 数据字典视图验证MV创建操作:
select mview_name,refresh_method,refresh_mode,build_mode,fast_refreshable from user_mviews where mview_name = 'SALES_DAILY_MV';
--可以通过USER_OBJECT和USER_SEGMENT视图,查看创建了那些MV.
select object_name,object_type from user_objects where object_name='SALES_DAILY_MV' order by object_name;
--MV是存储普通表中数据的逻辑容器。查询USER_SEGMENTS视图可以查看MV的基础表,主键索引以及通过MV查询语句创建的存储数据的表:
select segment_name,segment_type from user_segments where segment_name like '%SALES%' order by segment_name;
结果中I_SNAP$_SALES_DAILY_MV是MV的唯一索引,它是Oracle为提高刷新性能而自动创建的。
--查看MV含有的数据
select sales_amt,to_char(sales_dtt,'yyyy-mm-dd') from sales_daily_mv;
--向主表插入一些数据
insert into sales values (4,99,200,sysdate);
insert into sales values (5,127,300,sysdate);
--DBMS_MVIEW数据包的REFRESH过程可以快速刷新MV。REFRESH过程攒送了两个参数:MV的名称和刷新方法
--传递参数为F 代表快速刷新
exec dbms_mview.refresh('SALES_DAILY_MV','F');
因为这个MV不带MV日志,所以无法执行快速刷新。系统显示了下列错误提示:
ORA-23413: 表 "SCOTT"."SALES" 不带实体化视图日志
ORA-23413: table "SCOTT"."SALES" does not have a materialized view log
--传递参数为C 代表完全刷新
exec dbms_mview.refresh('SALES_DAILY_MV','C');
--输出结果
PL/SQL procedure successfully completed
从MV选择数据的结果表明其中增加了数据:
select sales_amt,to_char(sales_dtt,'yyyy-mm-dd') from sales_daily_mv;
完全刷新并不难以理解。完全刷新流程:
1.用户/应用创建事务。
2.数据在主表中提交
3.完全刷新操作是通过DBMS_MVIEW软件包手动执行
4.MV中的数据会被删除并通过主表的内容完全刷新
5.用户可以通过MV查询数据,其中含有主表的实时快照
–快速刷新MV
在创建快速刷新的MV时,应先将MV查询语句的所有结果装入MV表。将最初的结构存储好后,MV仅会应用数据更改(主表中的)。换言之,MV仅反应主表的更新,插入
和删除操作。该功能适用于某段时间内,主表仅会进行少量更改的情况。
–步骤
1.创建主表
2.在主表上创建MV日志
3.创建快速刷新的MV
快速刷新的MV需要主表上有MV日志。当进行快速更新时,MV必须以唯一方式标识哪些记录被更改了,从而需要刷新,可以通过两种方法做到这一点。一种方法在创建MV日志时设置 primary key 子句,另一种方法是设置 ROWID子句。如果比主表拥有主键,就可以使用基于主键的MV日志。如果主表没有主键,就必须使用ROWID创建MV日志。
在大多数情况下,主表都会有主键。然而,显示是系统的设计比较差或由于其他原因,有些表没有主键。
--主表上定义了一个主键,一遍使用PRIMARY KEY 子句创建MV日志:
create materialized view log on sales with primary key;
--如果表上没有主键,在创建MV日志时,系统会显示错误:
ORA-12014:table does not conttain a primary key constraint;
--如果没有主键,而且又无法为其添加主键/那么在创建MV日志时就必须设置ROWID:
create table sales_tmp as select * from sales;
create materialized view log on sales_tmp with primary key;
--因没有主键,会报错 用ROWID创建
create materialized view log on sales_tmp with rowid;
在使用基于主键的快速刷新MV时,必须在快速刷新MV的SELECT语句中包含主表的主键列(列组合)。在创建快速刷新MV时,还需注意特定的聚合限制。
这种MV通常用于在不同环境之间复制数据:
create materialized view sales_rep_mv
segment creation immediate
refresh
with primary key
fast
on demand
as select sales_id,sales_amt,trunc(sales_dtt) sales_dtt from sales;
--查询与MV有关的对象:
select object_name,object_type from user_objects where object_name like '%SALES%' order by object_name;
输出的部分对象:
MLOG$_SALES
RUPD$_SALES
SALES_PK
在创建MV日志时,Oracle会自动创建用于存储主表中已改变的行和他们的改变方式(插入,更新和删除)的表。MV日志表名称的格式为MLOG<BASETABLENAME>。Oracle还会创建名称格式为PUPD_<BASE TABLE NAME>。 Oracle还会创建名称格式为PUPD<BASETABLENAME>。Oracle还会创建名称格式为