物化视图可以查询表，视图和其它的物化视图。
通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。
对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。
对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。
本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。
在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。
1.主键物化视图：
下面的语法在远程数据库表emp上创建主键物化视图
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
注意：当用FAST选项创建物化视图，必须创建基于主表的视图日志,如下:
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
2.Rowid物化视图
下面的语法在远程数据库表emp上创建Rowid物化视图
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;
Materialized view log created.
3.子查询物化视图
下面的语法在远程数据库表emp上创建基于emp和dept表的子查询物化视图
SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)
Materialized view log created.
REFRESH 子句
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
Refresh选项说明:
a. oracle用刷新方法在物化视图中刷新数据.
b. 是基于主键还是基于rowid的物化视图
c. 物化视图的刷新时间和间隔刷新时间
Refresh方法-FAST子句
增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对主表创建物化视图日志
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
Refresh方法- COMPLETE子句
完全刷新重新生成整个视图，如果请求完全刷新，oracle会完成 完全刷新即使增量刷新可用。
Refresh Method – FORCE 子句
当指定FORCE子句，如果增量刷新可用Oracle将完成增量刷新，否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or FORCE),Force选项是默认选项
主键和ROWD子句
WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句). PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图.
主键物化视图允许识别物化视图主表而不影响物化视图增量刷新的可用性。
Rowid物化视图只有一个单一的主表，不能包括下面任何一项:
Distinct 或者聚合函数.Group by，子查询，连接和SET操作
刷新时间
START WITH子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的间隔时间.
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
在上面的例子中，物化视图数据的第一个副本在创建时生成，以后每两天刷新一次.
总结
物化视图提供了可伸缩的基于主键或ROWID的视图,指定了刷新方法和自动刷新的时间。
====================
我的创建实例：
create materialized view third_curve_sectable
refresh start with sysdate
next sysdate+1/1440
with rowid
as select * from third_curve_sectable@cnironerp.us.oracle.com;
下面描述包含聚集的物化视图的快速刷新的限制，并包含了正确设置快速刷新的例子。
包含聚集的物化视图：
1.必须满足所有快速刷新物化视图都满足的条件；
2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
(2)必须指明ROWID和INCLUDING NEW VALUES；
(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。
3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；
4.必须指定COUNT(*)；
5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在；
比如：包含SUM(a)，则必须同时包含COUNT(a)。
6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；
Oracle推荐同时包括SUM(expr*expr)。
7.SELECT列表中必须包括所有的GROUP BY列；
8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；
物化视图包含MIN或MAX聚集函数；
物化视图包含SUM(expr)，但是没有包括COUNT(expr)；
物化视图没有包含COUNT(*)。
注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，否则物化视图至此以后都不再自动刷新，且不会报任何错误。
9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；
10.如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。
11.如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。
12.对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件：
SELECT语句列表中应该包含GROUPING标识符：可以是GROUP BY表达式中所有列的GROUPING_ID函数，也可以是GROUP BY表达式中每一列的GROUPING函数；
例如：GROUP BY语句为：GROUP BY CUBE(a, b)，则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。
GROUP BY不能产生重复的GROUPING。
比如：GROUP BY a, ROLLUP(a, b)则不支持快速刷新，因为包含了重复的GROUPING：(a), (a, b), (a)。
首先，建立一个只包含聚集操作的满足快速刷新条件的例子，然后建立一个同时包括聚集和连接操作的例子。
最后依次违反上述11个条件，分别测试，查看会出现什么错误。（第一种约束条件是所有物化视图都必须满足的条件，在文章物化视图的快速刷新（一）中已经进行了说明，这里就不再重复描述了）。
首先，创建测试环境：
SQL> create table dim_a (id number primary key, name varchar2(30));
表已创建。
SQL> create table dim_b (id number primary key, name varchar2(30));
表已创建。
SQL> create table fact (id number, aid number, bid number, num number);
表已创建。
SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);
表已更改。
SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id);
表已更改。
SQL> insert into dim_a select rownum, 'a'||rownum from user_objects;
已创建48行。
SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;
已创建48行。
SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2
2 from user_objects;
已创建48行。
SQL> commit;
提交完成。
建立可以成功快速刷新的例子，这个物化视图只包含聚集：
SQL> create materialized view log on fact with rowid, sequence (aid, num)
2 including new values;
实体化视图日志已创建。
SQL> create materialized view mv_fact refresh fast on commit as
2 select aid, count(*) count, count(num) count_num, sum(num) sum_num
3 from fact group by aid;
实体化视图已创建。
建立快速刷新的例子，物化视图包含聚集和连接：
SQL> drop materialized view mv_fact;
实体化视图已删除。
SQL> drop materialized view log on fact;
实体化视图日志已删除。
SQL> create materialized view log on fact with rowid, sequence (aid, num)
2 including new values;
实体化视图日志已创建。
SQL> create materialized view log on dim_a with rowid, sequence (id, name)
2 including new values;
实体化视图日志已创建。
SQL> create materialized view mv_fact refresh fast on commit as
2 select name, count(*) count, count(num) count_num, sum(num) sum_num
3 from fact, dim_a where aid = dim_a.id group by name;
create materialized view mv_entrance_partition
partition by range(DAY_id)
SUBPARTITION BY HASH (province_ID) SUBPARTITIONS 32
(
partition PART_200805 values less than ('20080600'),
partition PART_200806 values less than ('20080700'),
PARTITION PART_20080701 VALUES less than ('20080702'),
PARTITION PART_20080702 VALUES less than ('20080703'),
PARTITION PART_20080703 VALUES less than ('20080704'),
PARTITION PART_20080704 VALUES less than ('20080705'),
PARTITION PART_20080705 VALUES less than ('20080706'),
PARTITION PART_20080706 VALUES less than ('20080707'),
PARTITION PART_20080707 VALUES less than ('20080708'),
PARTITION PART_20080708 VALUES less than ('20080709'),
PARTITION PART_20080709 VALUES less than ('20080710'),
PARTITION PART_20080710 VALUES less than ('20080711'),
PARTITION PART_20080711 VALUES less than ('20080712'),
PARTITION PART_20080712 VALUES less than ('20080713'),
PARTITION PART_20080713 VALUES less than ('20080714'),
PARTITION PART_20080714 VALUES less than ('20080715'),
PARTITION PART_20080715 VALUES less than ('20080716'),
PARTITION PART_20080716 VALUES less than ('20080717'),
PARTITION PART_20080717 VALUES less than ('20080718'),
PARTITION PART_20080718 VALUES less than ('20080719'),
PARTITION PART_20080719 VALUES less than ('20080720'),
PARTITION PART_20080720 VALUES less than ('20080721'),
PARTITION PART_20080721 VALUES less than ('20080722'),
PARTITION PART_20080722 VALUES less than ('20080723'),
PARTITION PART_20080723 VALUES less than ('20080724'),
PARTITION PART_20080724 VALUES less than ('20080725'),
PARTITION PART_20080725 VALUES less than ('20080726'),
PARTITION PART_20080726 VALUES less than ('20080727'),
PARTITION PART_20080727 VALUES less than ('20080728'),
PARTITION PART_20080728 VALUES less than ('20080729'),
PARTITION PART_20080729 VALUES less than ('20080730'),
PARTITION PART_20080730 VALUES less than ('20080731'),
PARTITION PART_20080731 VALUES less than ('20080801'),
PARTITION PART_DEFAULT VALUES LESS THAN(maxvalue)
)
tablespace doms_mv
nologging
build immediate
refresh force
on demand
enable query rewrite
as
SELECT
DMS_TIME.DAY_ID,
DMS_TIME.DAY_NAME,
DMS_TIME.Month_ID,
DMS_AREA.PROVINCE_ID,
DMS_AREA.PROVINCE_NAME,
DMS_AREA.CITY_ID,
DMS_AREA.ALL_NAME,
DMS_TERMINAL.BRAND_ID,
DMS_TERMINAL.TERMINAL_ID,
DMS_ENTRANCE.ENTRANCE_ID,
DTL_LOGIN.LOGIN_FLAG,
DTL_LOGIN.LOGIN_NEW_FLAG,
DTL_LOGIN.MSISDN
FROM
DMS_TIME,
DMS_AREA,
DTL_LOGIN,
DMS_TERMINAL,
DMS_ENTRANCE
WHERE
DMS_ENTRANCE.ENTRANCE_ID=DTL_LOGIN.ENTRANCE_ID
AND DMS_AREA.CITY_ID=DTL_LOGIN.CITY_ID
AND DMS_TIME.DAY_ID=DTL_LOGIN.DAY_ID
AND DMS_TERMINAL.TERMINAL_ID=DTL_LOGIN.TERMINAL_ID;
( DMS_ENTRANCE.ENTRANCE_ID=DTL_LOGIN.ENTRANCE_ID )
AND ( DMS_AREA.CITY_ID=DTL_LOGIN.CITY_ID )
AND ( DMS_TIME.DAY_ID=DTL_LOGIN.DAY_ID )
AND ( DMS_TERMINAL.TERMINAL_ID=DTL_LOGIN.TERMINAL_ID )
这个限制其实按照道理不应该存在，但是确实有这个问题，一个可更新的分区物化视图无法刷新会引起分区间记录转移的改动。这句话比较拗口，那么用例子说明一下。
创建一个分区表，注意要enable row movement，这样后面才能去更新分区键的字段值。
CREATE TABLE PARTITION_BY_RANGE
( FIRST_NAME VARCHAR2(10),
MIDDLE_INIT VARCHAR2(1),
LAST_NAME VARCHAR2(10),
BIRTH_MM INT NOT NULL,
BIRTH_DD INT NOT NULL,
BIRTH_YYYY INT NOT NULL)
PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
(PARTITION DOBS_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01),
PARTITION DOBS_IN_1972 VALUES LESS THAN (1973, 01 ,01),
PARTITION DOBS_IN_1973 VALUES LESS THAN (1974, 01 ,01),
PARTITION DOBS_IN_1974 VALUES LESS THAN (1975, 01 ,01),
PARTITION DOBS_IN_1975_OR_LATER VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
ENABLE ROW MOVEMENT;
插入分区数据。
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1969', 'A', 'SMITH_1969', 09, 20, 1969);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1970', 'A', 'SMITH_1970', 09, 20, 1970);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1971', 'A', 'SMITH_1971', 09, 20, 1971);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1972', 'A', 'SMITH_1972', 09, 20, 1972);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1973', 'A', 'SMITH_1973', 09, 20, 1973);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1974', 'A', 'SMITH_1974', 09, 20, 1974);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1975', 'A', 'SMITH_1975', 09, 20, 1975);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1976', 'A', 'SMITH_1976', 09, 20, 1976);
COMMIT;
创建物化视图日志。
create MATERIALIZED VIEW LOG ON PARTITION_BY_RANGE WITH PRIMARY KEY;
创建分区可更新物化视图。不是分区视图不会有问题，不是可更新视图也不会问题。
create MATERIALIZED VIEW mv_pr
PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
(PARTITION MV_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01),
PARTITION MV_IN_1972 VALUES LESS THAN (1973, 01 ,01),
PARTITION MV_IN_1973 VALUES LESS THAN (1974, 01 ,01),
PARTITION MV_IN_1974 VALUES LESS THAN (1975, 01 ,01),
PARTITION MV_IN_1975_OR_LATER VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
refresh fast with primary key for update
as
select * from PARTITION_BY_RANGE;
随便修改基表的几条数据，目的是引起分区记录的转移，比如将DOBS_IN_1972分区内的记录全部更新到DOBS_IN_1973中。
然后，尝试做物化视图的手动刷新，将会报错。
SQL> exec dbms_mview.refresh(list => 'MV_PR');
begin dbms_mview.refresh(list => 'MV_PR'); end;
ORA-12008: error in materialized view refresh path
ORA-14402: updating partition key column would cause a partition change
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 2
目前没有什么可以解决的方法，只能是修改为非分区的或者只读的物化视图。
create materialized view log on t with rowid, sequence (id, name) including new values
SQL> desc mlog$_t名称 是否为空? 类型
---------------------------------------- -------- ------------
ID NUMBER
NAME VARCHAR2(30)
M_ROW$$ VARCHAR2(255)
SEQUENCE$$ NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255) ID和NAME是建立物化视图日志时指定的基表中的列，它们记录每次DML操作对应的ID和NAME的值。 M_ROW$$保存基表的ROWID信息，根据M_ROW$$中的信息可以定位到发生DML操作的记录。 SEQUENCE$$根据DML操作发生的顺序记录序列的编号，当刷新时，根据SEQUENCE$$中的顺序就可以和基表中的执行顺序保持一致。 SNAPTIME$$列记录了刷新操作的时间。 DMLTYPE$$的记录值I、U和D，表示操作是INSERT、UPDATE还是DELETE。 OLD_NEW$$表示物化视图日志中保存的信息是DML操作之前的值（旧值）还是DML操作之后的值（新值）。除了O和N这两种类型外，对于UPDATE操作，还可能表示为U。 CHANGE_VECTOR$$记录DML操作发生在那个或那几个字段上。
最后，简单总结一下：
物化视图在刷新时，会刷新所有SNAPTIME$$大于本物化视图上次刷新时间的记录，并将所有是4000-01-01 00:00:00的记录更新为当前刷新时间。对于其他大于上次刷新时间的记录，只刷新不更改。这样，当刷新执行完以后，数据字典中记录当前物化视图的上次刷新时间为当前时刻，这保证了物化视图日志中目前所有的记录都小于或等于刷新时间。因此，每个物化视图只要刷新大于上次刷新时间的记录，且保证每次刷新后，所有记录的时间都小于等于上次刷新时间，那么无论有多少个物化视图，就可以互不影响的使用同一个物化视图日志进行快速刷新了。当物化视图刷新完之后，会清除那些SNAPTIME$$列小于所有物化视图的上次刷新时间的记录，而这些记录已经被所有的物化视图都刷新过了，保存在物化视图日志中已经没有意义了。
刷新物化视图例子：
exec dbms_mview.refresh('视图名称');
二、相关视图与基表信息 dba_mviews记录了远程站点上mv的数目与属性，需要在创建MV的站点上查询。 sys.mlog$则记录了主站点上的mv的log数目，如果一个master对应到多个站点，也只有一条记录，对应到dba_mview_logs视图，需要在主站点查询。 sys.slog$记录了主站点上已经注册成功的主表信息，如果一个主表被复制到多个站点，则对应多条记录，在主站点查询。 dba_snapshot_logs存放了mv的log日志，如果对应到多个站点，则每个站点都对应一条记录，因为远程站点的snapshot_id是不一样的。其实sys.mlog$与sys.slog$的关联就是组成dba_snapshot_logs的一个部分，通过查询dba_views可以看到其脚本。 dba_registered_snapshots记录了远程站点的注册信息，只记录注册成功的远程站点，通过snapshot_id可以与dba_snapshot_logs关联。如 SQL>select t.log_owner,t.master,t.log_table,t.current_snapshots, r.owner,r.name,r.snapshot_site from dba_snapshot_logs t,dba_registered_snapshots r where t.snapshot_id= r.snapshot_id(+); 删除mv的时候，需要先删除mv，再删除mv日志 远程MV站点： SQL>drop materialized view table_name; 主站点： SQL>drop materialized view log on table_name; 注意，删除mv的时候，如果主站点需要分发到多个远程站点，只有当所有远程站点的MV删除完成后，才可以删除MV日志。 删除远程站点的MV的时候，要保证与主站点的通信顺畅，如果网络不通，则主站点无法正常Unregister MV，而主站点的mv log又因为不知道这个站点已经没有MV，而不删除mv日志，将可能引起主站点mlog表的膨胀。这个时候，可以手工强行解除注册。 SQL>exec DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (snapshot_id); 三、故障处理与维护 如果mlog因为日志曾经很多而变得很大的时候，对mlog的操作如日志删除将变得很慢，因为mlog上没有任何索引，走的都是全表扫描，这个时候，我们可以move该日志表或者是等日志表没有记录的时候truncate该表。 SQL>alter table mlog$_table_name move; 如果要对有mv复制的表增加字段，最好在停机情况下进行，因为这样不会产生mv log，而且最好采用prebuild模式，因为这种模式下，删除mv的定义将保留表（也就是不删除表与相关数据）。相关步骤： 停机模式，并且在使用prebuild的情况下： 1、远程站点： SQL>drop materialized view mv_table; --这里删除mv，保留原表 2、主站点： SQL>alter table master_table add new_field number; 3、远程站点： SQL>alter table mv_table add new_field number; 4、远程站点： SQL>create materialized view mv_table on prebuilt table refresh fast as select * from master_table@lnk_db; 5、建立快速刷新的作业，这个时候不需要全刷新了，因为数据都在 为什么要停机操作，是因为创建快速刷新的mv的时候，将删除主站点上创建mv时间点之前的相关log，所以在删除mv与重新创建mv之前，不能对主表有任何dml产生新的日志，否则将可能引发数据的不一致。
修改物化视图刷新时间：
alter MATERIALIZED VIEW mv_grjbxx_gz refresh complete start with trunc(sysdate)+24/24 next TRUNC(SYSDATE)+24/24 +1
alter MATERIALIZED VIEW mv_zdssbdqkcx refresh complete start with to_date(to_char(trunc(sysdate+1),'dd-mm-yyyy')||' 10:00:00','DD-MM-YYYY HH24:MI:SS')
next sysdate+24/24
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25516289/viewspace-710302/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/25516289/viewspace-710302/
6156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
