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日志表名称的格式为MLOGOraclePUPD_的表。当你使用主键创建快速刷新MV时,Oracle会自动创建这个表。该表用于支持可更新MV功能。除非你要处理
可更新MV,否则无需考虑这个表。如果你不使用可以更新的MV功能,就可以忽略PUPD.OraclePKMVOracleROWIDMVOracleROWIDISNAP_。如果没有在主表上明确设置主键索引的名称,那么Oracle就会为MV表的主键索引赋予系统生成的名称。如SYS_XXXXXXXXX。

--MV中的数据
select sales_amt,to_char(sales_dtt,'yyyy-mm-dd') from sales_rep_mv order by 2;

--下面向主表SALES添加两条记录:
insert into sales values (6,99,20,sysdate-6);
insert into sales values (7,127,30,sysdate-7);

--此刻检查M$LOG表,通过这两条记录查看SALES表中数据更改的方式:
select * from mlog$_sales;

--刷新MV,通过F参数调用 DBMS_MVIEW软件包的REFRESH过程
exec dbms_mview.refresh('SALES_REP_MV','F')

--检查MV刷新结果
select sales_amt,to_char(sales_dtt,'yyyy-mm-dd') from sales_rep_mv order by 2;

--此外,MLOG$表中的行数也会变为0。MV的刷新操作完成后,这些记录不在有用:
select * from mlog$_sales;

--查询USER_MVIEWS视图可以验证第二种刷新方法:
select mview_name,last_refresh_type,last_refresh_date from user_mviews order by 1,3;

快速刷新MV的步骤:
1.用户创建事务
2.数据在主表中提交
3.主标上的内部触发器为MV日志表添加数据
4.通过DBMS_MVIEW软件包执行快速刷新
5.MV上一次刷新后创建的DML更改被应用到MV。从MV日志删除不在有用的行
6.用户可以通过MV查询数据,其中含有主表数据的快照。

--MV进阶
MV的功能非常多。许多功能与表的属性有关,如存储,索引,压缩和加密。还有一些功能与MV的类型和刷新方式有关。

每个MV都拥有基础表,此外,根据MV的不同类型,其索引可能会自动创建。在创建MV时,可以为基础表和索引设置表空间和属性。
--创建MV并为MV和索引设置表空间
create materialized view sales_mv tablespace users 
using index tablespace users 
refresh with primary key 
fast on demand as select sales_id,sales_amt,sales_dtt from sales;

还可以设置MV的存储属性。如:MV的主表极少更新数据,就适合将PCTUSED子句设置为较高的值:
create materialized view sales_mv pctused 95
pctfree 5 tablespace users 
using index tablespace users
refresh with primary key 
fast on demand as select sales_id,sales_amt,sales_dtt from sales;

–在MV上创建索引
MV将数据存储在普通表中。因此,可以在基础表上创建索引(就像在普通表上创建索引一样)。通常,应遵循在普通表上创建索引的指导原则,在MV表上创建索引。
注意,尽管索引能够大幅度提高查询操作的性能,但是在执行插入,更新和删除操作时,维护索引有系统开销。而且索引还会占用磁盘空间。

--在MV上创建索引和普通表上创建索引语法相同
create index sales_mv_idx1 on sales_mv(sales_dtt) tablespace users;

--查询user_indexes视图可以显示为MV创建的索引:
select a.table_name,a.index_name from user_indexes a,user_mviews b where a.table_name=b.MVIEW_NAME;

–分区MV
可以像处理不同表那样对MV应用分区功能。在处理大型MV时,需要使用分区功能提高大型表的可管理性和可维护性。在创建MV时可以使用PARTITION子句。

--创建分区MV
create materialized view sales_mv
partition by hash(sales_id)
partitions 4
refresh on demand complete with rowid 
as select sales_id,sales_amt,region_id,sales_dtt from sales;

上午命令的查询结果会存储到分区表中。可以查询USER_TAB_PARTITIONS和USER_PART_TABLES视图,查看分区细节。

--压缩MV
在创建MV时,Oracle会自动创建用于存储数据的基础表。因为这个表是普通表,索引可以对其应用压缩功能。
--创建压缩MV
create materialized view sales_mv 
compress 
as 
select sales_id,sales_amt from sales;

--查询压缩细节
select table_name,compression,compress_for from user_tables where table_name='SALES_MV';

--创建不含数据的MV
create materialized view sales_mv tablespace users
build deferred 
refresh complete on demand as 
select sales_id,sales_amt from sales;

--创建在提交时刷新的MV
某些情况要求当主表更改数据时,必须立刻反映到MV上。在这类情况下,可在创建MV时使用 ON COMMIT 子句。 只有在主标上创建了MV日志,该方法才会生效。
create materialized view log  on sales with primary key;

--创建MV
create materialized view sales_mv 
refresh on commit as 
select sales_id,sales_amt from sales;

在提交时刷新MV时应注意以下限制:
1.主表和MV必须位于同一数据库中
2.无法在基表上执行分布式事务
3.该方法不支持含有对象类型或Oracle补充支持类型的MV

--创建从不刷新的MV
有些MV永远都不需要刷新。NEVER REFRESH 子句可以创建这种MV:
create materialized view sales_mv 
never refresh 
as select sales_id,sales_amt from sales;

如果刷新从不刷新的MV,系统会显示下列提示信息:
ORA-23538:connot explicitly refresh a never refresh materialized view;

ALTER MATERIALIZED VIEW 语句可以将从不刷新的MV切换成可刷新的MV:
alter materialized view sales_mv refresh on demand complete;

--查询验证刷相信模式和刷新方法
select mview_name,refresh_mode,refresh_method from user_mviews;

–为查询重写创建MV
查询重写是优化器能够识别满足需求的MV,从而无需使用主表。如果用户经常自己编写查询命令,而且不知道可以使用MV,那么这个功能就可以大幅度提高性能。
启用查询重写功能需要满足3个条件:
1.Oracle企业版
2.将数据库初始化参数QUERY_REWRITE_ENABLED设置为TRUE (Oracle 10g及更高版本中的默认值)
3.MV是由ENABLE QUERY REWRITE 子句创建的。

--创建带有重写功能的MV 
create materialized view sales_daily_mv 
segment creation immediate
refresh 
complete
on commit
enable query rewrite
as 
select sum(sales_amt) sales_amt,
trunc(sales_dtt) sales_dtt from sales group by trunc(sales_dtt);

--查询autotrace使用程序的解释计划可以验证查询重写功能的使用情况:
SQL> set autotrace trace explain
--如果用户在不知道MV存在的情况下,运行了下面的查询命令:
select sum(sales_amt) sales_amt,trunc(sales_dtt)sales_dtt from sales group by trunc(sales_dtt);

-----------------------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     6 |    72 |      3    (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_DAILY_MV |     6 |    72 |      3  (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

如上述输出结果所示,即使用户直接从SALES表中选择数据,优化器也能确定访问MV执行查询操作的效率更高。

查询USER_MVIEWS视图的REWRITE_ENABLED 列可以查看启用了查询重写功能的MV:
select mview_name,rewrite_enabled,rewrite_capability from user_mviews where mview_name='SALES_DAILY_MV';

–使用复杂查询操作创建快速刷新MV
当使用连接多个表的查询操作创建MV时,可能会犹豫MV叫复杂而只能进行完全刷新。然而,在某些情况下,可以使用连接两个表的MV查询命令,创建快速刷新的MV。

--定义两个主表:
create table region(
       region_id number,
       reg_desc varchar2(30),
       constraint region_pk primary key(region_id));

create table sales (
       sales_id number,
       sales_amt number,
       region_id number,
       sales_dtt date,
       constraint sales_pk primary key(sales_id),
       constraint sales_fk1 foreign key (region_id) references region(region_id));

--创建MV日志
create materialized view log on region with primary key;
create materialized view log on sales with primary key;

--插入测试数据
insert into region values (10,'East');
insert into region values (20,'West');
insert into region values (30,'South');
insert into region values (40,'North');
commit;

insert into sales values (1,100,10,sysdate);
insert into sales values (2,200,20,sysdate-20);
insert into sales values (3,300,30,sysdate-30);
commit;

--将主表REGION和SALES连接起来创建MV
create materialized view sales_mv 
as 
select a.sales_id,b.reg_desc from sales a,region b where a.region_id=b.region_id;

--快速刷新这个MV
exec dbms_mview.refresh('SALES_MV','F')
系统会提示以下错误:
ORA-12032:cannot use rowid column from materialized view log ...
ORA-12032: 不能使用 "SCOTT"."REGION" 上实体化视图日志中的 rowid 列

这个错误提示表名MV有问题,无法进行快速刷新。要确定这个MV是否可以变为快速刷新的MV,可以使用DBMS_MVIEW软件包的EXPLAIN_MVIEW过程。该过程要求先

创建一个MV_CAPABILITIES_TABLE表。Oracle提供了创建这个表的脚本。
SQL> @?/rdbms/admin/utlxmv.sql;

--创建了这个表以后,可以运行EXPLAIN_MVIEW过程为其加载数据:
exec dbms_mview.explain_mview('SALES_MV');

select m.capability_name,
       m.possible,
       m.related_text,
       m.msgtxt from mv_capabilities_table m
       where m.capability_name like 'REFRESH_FAST_AFTER%'
       order by 1 ;

POSSIBLE 表示快速刷新的可能性,N代表不能快速刷新
MSGTXT列指明了问题:需要在ROWID裂伤创建MV日志,而且这些表的ROWID列必须包含在SELECT 子句中。因此,应先删除MV,然后砽ROWID(而不是主键)重建MV日志。

--删除MV日志
drop materialized view log on region;
drop materialized view log on sales;

--根据ROWID创建MV日志
create materialized view log on region with rowid;
create materialized view log on sales with rowid;

--删除视图
drop materialized view sales_mv;

--重新创建MV
create materialized view sales_mv 
as 
select a.rowid sales_rowid,b.rowid region_rowid,a.sales_id,b.reg_desc from sales a,region b where a.region_id=b.region_id;

--重置 MV_CAPABLILTIES_TABLE 表,使用EXPLAIN_MVIEW过程为其重新加载数据:
truncate table mv_capabilities_table;
exec dbms_mview.explain_mview('SALES_MV');

--验证快速刷新操作是否生效 
SQL> exec dbms_mview.refresh('SALES_MV','F'); 
PL/SQL procedure successfully completed

–查看MV的DDL代码
要快速查看用于创建MV的SQL查询代码,可查询DBA/ALL/USER_MVIEWS视图的QUERY列。如果使用的SQL*Plus,应先将LONG变量的值设置的足够大,以显示LONG类型的全部内容:

set long 20000
select query  from dba_mviews where mview_name=upper('&mview_name');

使用DBMS_METADATA 软件包可以查看重建MV得DDL代码
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','SALES_MV') from dual;


--删除MV
drop materialized view sales_mv;

删除MV时,MV定义,基础表和相应的索引也会被删除。删除MV不会影响任何MV日志,MV日志仅依赖主表。
还可以设置保留基础表。在需要解决问题和需要删除MV定义却要保留MV表的数据情况下,可以这样做:
drop materialized view sales_mv preserve table;

–修改MV
修改主表的DDL代码并使MV反应这些更改
常见的MV维护任务包括想主表添加列或从主表删除列(根据业务需求)。想主表添加了列或从主表删除了列后,需要使这些DDL更改反应到所有依赖主表的MV上。

使下列方法可以将主表列的更改反应到MV上。
1.删除MV然后使用新得列定义重新创建MV。
2.删除MV,但保留基础表。修改主表,然后使用 ON PREBUILT TABLE子句重建MV。
3.如果MV最初使用 ON PREBUILT TABLE子句创建的,可以删除MV,修改主表,然后使用ON PREBUILT TABLE子句重建MV。

--重建反应主表更改的MV
create materialized view log on sales with primary key ;

--创建MV
create materialized view sales_mv
refresh with primary key 
fast on demand as 
select sales_id,sales_amt,sales_dtt from sales;

--向主表添加一个列
alter table sales add (sales_loc varchar2(30));
 --删除并重建MV,以反应主表的更改
drop materialized view sales_mv;

--
create materialized view sales_mv 
refresh with primary key 
fast on demand as 
select sales_id,sales_amt,sales_dtt,sales_loc from sales;

如果主表中含有大量数据,该方法就会花很长时间。在重建MV期间,MV会处于停运状态。如果你处理的是数据仓库环境,因为完全刷新MV需要花费很多时间,所以最好
采用不删除基础表的方法。

–删除MV但保留基础表
再删除MV时,可以保留基础表及其数据。在数据仓库环境中处理大型MV时,该方法的优势显而易见。
–具体步骤
1.更改主表
2.删除MV,但保留基础表
3.修改基础表
4.使用ON PREBUILT TABLE 子句重建MV

--向主表添加列
alter table sales add (sales_loc varchar2(30));

--删除MV,但是保留他的基础表
drop materialized view sales_mv preserve table;

--修改基础表
alter table sales_mv add (sales_loc varchar2(30));

--使用ON PREBUILT TABLE 子句创建MV
create materialized view sales_mv 
on prebuilt table 
refresh with primary key 
complete on demand as 
select sales_id,sales_amt,sales_dtt,sales_loc from sales;

这可以在无需删除MV和完全刷新其中数据的情况下,重新定义MV。
注意,如果在重建MV得过程中,主表上运行了DML操作,那么无法通过刷新MV反应这些事务。在数据仓库环境中,通常会按时间加载主表的数据,从而能够在不执行事务
的维护时间执行更改MV操作。

–更改预建表创建的MV
1.更改主表
2.删除MV。这样做不会删除预建表创建的MV基础表。
3.更改预建表
4.在预建表上重建MV

 --向主表添加列
alter table sales add (sales_loc varchar2(30));

--删除MV,这样做不会删除预建表创建的MV基础表,金辉删除MV定义
drop materialized view sales_mv;

--修改基础表
alter table sales_mv add (sales_loc varchar2(30));

--使用新增列的预建表重建MV
create materialized view sales_mv 
on prebuilt table 
refresh with primary key 
complete on demand as 
select sales_id,sales_amt,sales_dtt,sales_loc from sales;    
该方法的有点事可以在无需删除基础表的情况下修改MV的定义。必须删除MV,更改基础表,然后使用新定义的MV。如果基础表含有大量数据,该方法可以防止出现长时间

停运的情况。

--触发MV上的重做日志功能
默认情况下,在穿件MV时重做日志功能就会启用。可以设置在刷新MV时不生成重做日志。要启用不记录日志功能,可在创建MV时使用NOLOGGING选项:
create materialized view sales_mv 
nologging 
refresh with primary key 
fast on demand as 
select sales_id,sales_amt,sales_dtt from sales;

--可以将现存的MV切换为不记录日志模式:
alter materialized view sales_mv nologging;

--重新启用日志功能
alter materialized view sales_mv logging;

--查询USER_TABLES,查看MV的日志模式
select a.table_name,a.logging from user_tables a ,user_mviews b where a.table_name=b.mview_name;

--更改并行机制
有时候可以使用较高的并行度创建MV,提高创建过程的速度:
create materialized view sales_mv parallel 4
refresh with primary key 
fast on demand as 
select sales_id,sales_amt,sales_dtt from sales;

创建了MV后,无需对基础表应用相同并行度的机制。这一点很重要,因为查询MV的命令也能启动并行线程执行操作。
--更改MV的并行机制:
alter materialized view sales_mv parallel 1;

--通过USER_TABLES视图可以查询并行度
select table_name,degree from user_tables where table_name=upper('&MV_NAME');

--移动MV
当操作环境发生改变时,可能需要将MV从一个表空间移动到另一个表空间。ALTER MATERIALIZED VIEW ... MOVE TABLESPACE 语句可以实现
alter materialized view sales_mv move tablespace users;

如果MV的基础表有索引,改移动操作会使他们都失效。检查索引状态:
select a.table_name,a.index_Name,a.status from user_indexes a ,user_mviews b where a.table_name=b.MVIEW_NAME;

在移动了MV的基础表后,必须重建所有相关索引:
alter index SYS_C_SNAP$_368SALES_PK rebuild;

–管理MV日志
快速刷新MV时需要使用MV日志。MV日志时存储主表DML信息的表。MV日志会在存储主表的数据库和拥有主表的用户中创建。要创建MV日志,需要使用拥有 CREATE TABLE
权限的用户账号。
MV日志通过Oracle内部触发器加载数据。主表执行了INSERT,UPDATE或DELETE命令后,这个内部触发器就会向MV日志中插入一行。查询BDA/ALL/USER_INTERNAL_TRIGGERS
视图可以查看触发器的使用情况。
MV日志只能与一个表关联,而每个主表也只能拥有一个MV日志。可以在表上创建MV日志,也可以在MV上创建MV日志。多个快速刷新的MV可以共用一个MV日志。
MV执行了快速刷新后,MV日志中的所有记录都不在有用并且会被删除。有多个快速刷新MV使用一个MV日志的情况中,只有当所有快速刷新MV都不需要MV日志中的记录时,
这些记录才会被清除。

--MV日志术语
     术   语                                  意   义
物化视图(MV)日志         跟踪MV主表DML更改的数据库对象,用于快速刷新操作
主键MV日志                 通过主表的主键跟踪DML更改的MV日志
ROWID MV日志               通过主表的ROWID列耿总DML更改的MV日志
提交SCN MV日志             通过提交SCN(不是时间戳)增加记录的MV日志,Oracle Database 11g Release 2和更高的版本提供了该功能
对象ID                     用于跟踪DML更改的对象标识符
过滤列                     MV子查询命令引用的非主键列,某些快速刷新操作需要使用它们
连接列                     在子查询命令的WHERE子句中定义连接的非主键列,某些快速刷新操作需要使用它们
序列                       某些快速刷新操作需要使用序列值
新值                       用于区分MV日志中的新旧记录,适合快速刷新的单一表聚合视图需要使用他们

--创建MV日志
快速刷新MV需要在主表上创建MV日志。CREATE MATERIALIZED VIEW LOG 命令可以创建日志。
--根据主键创建日志:
create materialized view log on sales with primary key;
--也可以设置存储信息,如表空间的名称:
create materialized view log on sales pctfree 5 tablespace users with primary key ;

在表上创建MV日志时,Oracle会创建用于存储主表上次刷新后所做更改的表。MV日志表的名称格式为 MLOG$_。

–索引MV日志列
快速刷新MV时可能需要提高性能。实现该该目标的方法之一,是在MV日志表上创建索引。在Oracle使用SNAPTIME

createindexmlog$salesidx1onmlog$sales(snaptime
);
create index mlogsalesidx2onmlog_sales(sales_id);

--查看MV日志占用的空间
select segment_name,tablespace_name,bytes/1024/1024 meg_bytes,extents from dba_segments where segment_name like 'MLOG%' order by meg_bytes;

--缩小MV日志占用的空间
如果MV日志无法成功删除他的记录,那么它就会变大。解决了这个问题后,MV日志中的记录会被删除,但是会将MV日志表的高水位先标记推到较高的值。这回引发

性能和空耗磁盘空间的问题。此时应该收缩MV日志占用的表空间。
–在MV日志表上启用行移动功能:

    alter table mlog$_sales enable row movement;
--然后使用 alter materialized view log on ... shrink 命令  
--注意ON后面跟的是主表的名称
alter materialized view log on sales shrink space;
--执行后禁用行移动功能
alter table mlog$_sales disable row movement;

--移动MV日志
alter materialized view log on sales move tablespace users;

--检查与该表关联的索引状态
select a.table_name,a.index_name,a.status from user_indexes a, user_mview_logs b where a.table_name=b.log_table;

--移动了表之后重建失效索引
alter index mlog$_sales_idx1 rebuild;

–删除MV日志
删除MV日志的理由:
1.创建了MV日志后业务需求发生改变,不在需要它;
2.MV日志的增长导致了性能问题,需要通过删除MV日志重置尺寸。
在删除MV日志之前,应先查明它的所有者、主表和MV日志表:
select log_owner,master,log_table from user_mview_logs;

--删除MV日志
drop materialized view log on sales;

--如果使用的用户账号不是主表的所有者,但拥有操作该表的权限,就需要在删除MV日志时设置方案的名称:
drop materialized view log on scott.sales;

–刷新MV
通常MV需要定期刷新。常用的刷新方式:
1.通过SQL*Plus手动刷新MV;
2.使用Shell脚本和调度实用程序,自动刷新MV
3.使用Oracle内置的作业调度程序,自动刷新MV

--利用SQL*Plus手动刷新MV
MV的名称为SALES_MV,刷新方法为F(代表快速刷新):
exec dbms_mview.refresh('SALES_MV','F');

--此外,还可以使用问号(?)调用强制刷新方法。这会使sOracle尽可能执行快速刷新。在无法执行快速刷新时,Oracle才会执行完全刷新操作:
exec dbms_mview.refresh('SALES_MV','?');
--也可以使用C(代表完全刷新),设置Oracle执行完全刷新操作:
exec dbms_mview.refresh('SALES_MV','C');

–使用SHELL脚本和调度实用程序执行自动刷新
许多MV比如每天刷新。要做到这一点,可以使用Linux/Unix实用程序调用SHELL脚本刷新MV。该方具有以下好处:
1.易于实现和维护;
2.易于创建审计使用的日常日志文件;
3.在作业出现问题和数据库不可用时,可以发送电子邮件。

–使用Oracle内置的作业和调度程序自动刷新MV
创建了MV后,可以使用START WITH和NEXT子句设置内部数据库作业,利用DBMS_JOB软件包定期刷新MV。如果没有使用START WITH和NEXT子句,Oracle就不会设置
作业,就必须使用其他刷新方法。

START WITH参数可以设置第一次刷新MV得日期,NEXT参数可以设置用于计算刷新间隔时间的日期表达式。例如,下面的MV在创建完成的一分钟后(sysdate+1/24/60)

执行第一次刷新操作,之后每一分钟(sysdate+1/24/60)都执行刷新操作:
    create materialized view sales_mv 
    refresh 
    with primary key 
    fast on demand 
    start with sysdate+1/24/60
    next sysdate+1/24/60
    as 
    select sales_id,sales_amt,sales_dtt from sales;
--通过USER_JOBS视图,可以查看调度作业的细节:
select job,schema_user,to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date,
interval,broken from user_jobs;

--也可以查询USER)REFRESH视图,查看作业信息:
select rowner,rname,job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date ,interval,broken from user_refresh;

/******************************************************************************************
在做自动刷新的时候,查看 job_queue_processes 参数,当参数值为0时,不能调用JOB。自动刷新无效,
alter system job_queue_processes=1000 scope=spfile;
******************************************************************************************/


--在删除MV时,相关的作业也会删除。如果想要手动删除作业,可以使用DBMS_JOB数据包的REMOVE过程。下面删除了1号作业:
exec dbms_job.remove(1);

–高效执行完全刷新操作
当执行完全书信MV操作时,默认情况下DELETE语句会从MV基础表中删除所有记录。删除操作完成后,从主标选中的数据回呗插入到MV基础表中。Oracle会将
删除和插入操作作为一个事务执行。这意味着在执行刷新操作的过程中,从MV选择数据会得到执行DELETE语句前的数据。在提交了INSERT语句后访问MV,可以得
到刷新的数据。
某些情况下,需要修改这种设置。如果需要刷新大量数据,执行DELETE语句就会花费很长时间。使用ATOMIC_REFRESH参数,可以使Oracle尽可能提高删除数
据的效率。当将该参数设置为FALSE时,Oracle会在执行完全刷新时使用TRUNCATE语句(而不是DELETE语句)。
exec dbms_mview.refresh(‘SALES_MV’,method=>’C’,atomic_refresh=>FALSE);

--处理ORA-12034错误
当快速刷新MV时,有时会出现ORA-12034错误:
exec dbms_mview.refresh('SALES_MV','F');
--错误信息
ORA-12034:materialized view log on "SCOTT"."SALES_MV" younger than last refresh

--要解决这个问题,可对MV执行完全刷新操作。
exec dbms_mview.refresh('SALES_MV','C');
--完全刷新操作完成后,就可以执行快速刷新操作,而系统也不会报错了:
exec dbms_mview.refresh('SALES_MV','F');
当Oracle确定MV日志实在MV的上次刷新操作后创建时,他就会显示ORA-12034错误提示。换言之,MV日志比MV的上次刷新内容新。下面时出现这种情况的几种原因:
1.MV被删除并重建了;
2.MV日志被清空了;
3.修改了主标;
4.删除了主表;
5.上次刷新操作失败了

--监控MV的刷新操作

--查看上次刷新时间
在解决MV的问题时,通常需要检查的第一个项目就是DBA/ALL/USER_MVIEWS 视图中的LAST_REFRESH_DATE列。查看这些信息后,才能了解MV是否根据计划进行刷新。
select mview_name,to_char(last_refresh_date,'yyyy-mm-dd hh24:mi:ss') last_date,refresh_mode,refresh_method from user_mviews;
DBA/ALL/USER_MVIEWS视图的LAST_REFRESH_DATE列含有MV上次成功刷新的日期和时间。如果MV从来都没有成功刷新过,LAST_REFRESH_DATE列的值会为NULL。

--确定刷新作业师傅哦正在进行
select sid,serial#,currmvowner ,currmvname from v$mvrefresh;

--监控刷新操作得进度
在处理大型MV时,使用下面的语句可以查看刷新操作的进度。
select currmvowner_knstmvr||'.'||currmvname_knstmvr "VIEW BEGIN REFRESHED",
decode(reftype_knstmvr,1,'FAST',2,'COMPLETE','UNKNOWN')reftype,
decode(groupstate_knstmvr,1,'SETUP',2,'INSTANTIATE',3,'WRAPUP','UNKNOWN') state,
total_inserts_knstmvr inserts,
total_updates_knstmvr updates,
total_deletes_knstmvr deletes from x$knstmvr x where type_knst = 6
and exists(select 1 from v$session s where s.sid=x.sid_knst 
and s.serial#=x.serial_knst);

--当MV开始执行刷新操作时:
VIEW BEGIN REFRESHED     REFTYPE   STATE             INSERTS    UPDATES    DELETES
--------------------     -------   ----------        -------    -------    ------- 
SCOTT.SALES_MV           UNKNOWN   SETUP             0          0          0

--几秒钟之后这个MV会进入INSTANTIATE(实例化)状态
SCOTT.SALES_MV           FAST      INSTANTIATE       0          0          0

--MV正在执行刷新操作时,INSERTS,UPDATES和DELETES列的值都会相应更新:
SCOTT.SALES_MV           FAST      INSTANTIATE       680        274        0

--MV的刷新操作接近完成时,他会进入WARPUP(结束)状态
SCOTT.SALES_MV           FAST      WRAPUP            5284       1518        0

--MV的刷新操作完成后,查询结汇会显示没有符合条件的数据
no rows selected

–创建远程MV
可以使用查询远程表,MV和视图的结果创建MV。这可以快速搞笑的复制数据。执行下面的步骤可以通过远程对象创建MV。
1.确保目的数据库与资源数据库通过Oracle Net 连接。如果这个连接不存在,就无法创建远程MV。
2.货的房源元数据库中表,MV和视图的权限。
3.创建快速刷新MV时,应在主表上创建MV日志。只应在需要执行快速刷新操作的情况下这样做。
4.在目的数据库中,创建指定元数据库中主表的数据库连接。
5.在目的数据库中创建MV,使用步骤(4)创建的数据库连接访问元数据库中的主表。

--连接源数据库
sqlplus scott/tiger@ora11g 
--连接了源数据库后,还应确定访问其中主表的权限。
select * from sales;

--在目的数据库上创建一个数据库连接。该数据库连接指向源数据库中的用户SCOTT
create database link ora11g connect to scott identified by tiger using 'ora11g';

--创建访问主表的SALES的MV:
create materialized view sales_mv 
refresh complete on demand 
as select sales_id,sales_amt from sales@ora11g;

--查看MV主表的信息
在诊断MV问题时,查看MV和它的远程主表可以获得有用的信息。
select owner mv_owner,name mv_name,master_owner mast_owner,master mast_table from dba_mview_refresh_times order by 1,2;

--确定MV日志被多少个MV引用
如果MV已经被删除并无法从中心MV日志注销,那么中心MV日志中的记录就会一直增加。要解决这个问题,需要查明哪些MV使用哪些MV日志。
--查看主表所有者信息和依赖主表的所有MV的SNAPID(MV的ID);
select mowner,master base_table ,snapid,snaptime from sys.slog$;

create materialized view sales_nmv 
refresh with primary key 
as select sales_id from sales;


create table test_x (a number ,constraint pk_x primary key (a));

create materialized view log on test_xs with primary key ;
create materialized view test_xsmv 
refresh with primary key 
as select a from test_x;

--查询某个MV日志的所有MV
select a.log_table,
       a.log_owner,
       b.master     mast_tab,
       c.owner      mv_owner,
       c.name       mview_name,
       c.mview_site,
       c.mview_id
  from dba_mview_logs        a,
       dba_base_table_mviews b,
       dba_registered_mviews c
 where b.mview_id = c.mview_id
   and b.owner = a.log_owner
   and b.master = a.master
 order by a.log_table;

在删除远程MV时,应从源数据库的MV日志中注销它。然而,这个目标并不总能实现。目的数据库可能会被删除(如短期的开发数据库),而没有机会注销(使用

DROP MATERIALIZED VIEW 命令)其中的MV。在这种情况下,源数据库中的MV日志不知道依赖他的MV已经被删除,因此会一直为它保留记录。
要从MV日志清空不需要的MV信息,可在源数据库执行DBMS_MVIEW软件包的PURGE_MVIEW_FROM_LOG过程。
调用过程的时候传递参数为MV的ID;
SQL> exec dbms_mview.purge_mview_from_log(424);
这个SQL回更新数据字典并从内部表SLOG$和DBA_REGISTERED_MVIEWS删除信息。如果要删除的MV时MV日志中最旧的MV,那么相关的旧纪录也会从MV日志中删除。

如果远程MV不在可用但却没有从MV日志注销,可以在源数据库上手动注销它。DBMS_MVIEW软件包的UNREGISTER_MVIEW过程可以注销远程MV。要做到这一点,你需要

知道远程MV的所有者,MV的源数据库的名称:
SQL> exec dbms_mview.unregister_mview(‘SCOTT’,’TEST_XSMV’,’ORCL’);

--管理MV组
使用MV组功能可以通过一致的事务时间点,批量刷新MV。在刷新通过拥有父表-字表关系的主键创建的MV时,很可能需要使用刷新组功能。这个方法可以确保刷新的

MV组中不存在独立的字表记录。

--创建MV组
DBMS_RERESH软件包的MAKE过程可以创建MV组。在创建MV组时,必须设置MV组的名称,以逗号分隔MV列表。执行下次刷新操作的日期和用于计算下次刷新时间的时间间隔。
--创建含有两个MV的MV组:
begin 
  dbms_refresh.make(
     name      =>'SALES_GROUP',
     list      =>'SALES_NMV,SALES_DAILY_MV',
     next_date =>sysdate-100,
     interval  =>'sysdate+1'
  );
  end ;
  /

  --在创建MV组时,Oracle会自动创建管理组刷新的数据库作业。查询DBA/ALL/USER_REFRESH视图,可以查看MV组的细节
  select rname,job,next_date,interval from user_refresh;

  --更改MV组
  可以更改MV刷新组的属性,如刷新日期和时间间隔。如果你将数据库作业用作刷新机制,有时需要调整刷新属性。DBMS_REFRESH软件包的CHANGE函数可以做到这一点。

  --更改了时间间隔的表达式:
  exec dbms_refresh.change(name=>'SALES_GROUP',interval=>'SYSDATE+2');

  只有在使用内部数据库作业执行MV组刷新操作时,才需要更改刷新间隔。
  --查询MV刷新组间隔时间和作业信息。
  select a.job,a.BROKEN,b.rowner,b.rname,b.interval from dba_jobs a,dba_refresh b where a.job=b.JOB order by a.job;

  --刷新MV组
  exec dbms_refresh.refresh('SALES_GROUP');

  --DBMS_MVIEW和DBMS_REFRESH
  可以使用 DBMS_MVIEW软件包刷新MV组。例如,使用DBMS_MVIEW软件包可以刷新列表中的一批MV:
  exec dbms_mview.refresh(list=>'SALES_MV,SALES_DAILY_MV');

  该方法可以使用一个事务刷新列表中的所有MV。这与使用MV组功能的效果相同。然而,在使用DBMS_MVIEW软件包时,还可以选择将ATOMIC_REFRESH参数设置为TRUE(默认值)

或FALSE。
–将ATOMIC_REFRESH参数设置为FALSE:
exec dbms_mview.refresh(list=>’SALES_MV,SALES_DAILY_MV’,atomic_refresh => false);

  DBMS_REFRESH软件包用于设置和维护MV组。与DBMS_MVIEW软件包相比,DBMS_REFRESH永远都会以一致得方式处理刷新MV组得事务。
  如果需要以一致得方式刷新MV组,应使用DBMS_REFRESH软件包。如果需要一些灵活性(各行其是得刷新列表中得MV),应使用DBMS_MVIEW软件包。

  --确定MV组中的MV
  在检查MV组总得问题时,查明MV组含有哪些MV就是一个很好得起点。通过DBA_RGROUP和DBA_RCHILD可以查看MV组:
  select a.owner,a.name mv_group ,b.name mv_name from dba_rgroup a ,dba_rchild b where a.refgroup=b.refgroup 
  and a.owner=b.owner order by a.owner,a.name,b.name;

  --向MV组中添加MV
  当业务需求更改时,可能需要向MV祖中添加MV。DBMS_REFRESH软件包得ADD过程可以完成这个任务:
  SQL> exec dbms_refresh.add(name=>'SALES_GROUP',list=>'TEST_XSMV,TEST_XMV');

  --从MV组中删除MV
  有时需要从MV组中删除MV。可以使用DBMS_REFRESH软件包的SUBTRACT函数。下面的例子从MV组中删除了一个MV:
  exec dbms_refresh.subtract(name=>'SALES_GROUP',list=>'TEST_XSMV');

  --删除MV组
  当要删除MV组时,可以使用DBMS_REFRESH软件包的DESTROY过程。
  SQL> exec dbms_refresh.destroy('SALES_GROUP');
  该方法金辉删除MV组对象,不会删除MV对象。如果要删除MV,可以使用DROP MATERIALIZED VIEW语句。
阅读更多

没有更多推荐了,返回首页