Oracle 物化视图(MV)详解

物化视图(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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值