1.简单使用materialized view
给user赋予create materialized view 权限
SQL> grant create materialized view to test;
Grant succeeded.
创建测试table mv_master
SQL> create table mv_master as select * from all_objects;
Table created.
创建materialized view mv
SQL> create materialized view mv as select * from mv_master;
Materialized view created.
SQL> select count(*) from mv_master;
COUNT(*)
----------
11201
SQL> select count(*) from mv;
COUNT(*)
----------
11201
SQL> delete from mv_master where object_id<100;
97 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from mv_master;
COUNT(*)
----------
11104
SQL> select count(*) from mv;
COUNT(*)
----------
11201
SQL> select owner,mview_name,refresh_mode,refresh_method,build_mode,fast_refreshable from user_mviews;
OWNER MVIEW_NAME REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE
---------- ---------- ------------ ---------------- ------------------ ------------------------------------
TEST MV DEMAND FORCE IMMEDIATE NO
refresh_mode提示需要手工fresh.
需要手动刷新materialized view,同步两边的数据。
SQL> exec dbms_mview.refresh('mv')
PL/SQL procedure successfully completed.
SQL> select count(*) from mv;
COUNT(*)
----------
11104
上面简单的使用，可以适用于同步数据。
refresh_mode:
DEMAND - Oracle Database refreshes this materialized view
whenever an appropriate refresh procedure is called(mview_refresh)
COMMIT - Oracle Database refreshes this materialized view
when a transaction on one of the materialized view's masters commits
NEVER - Oracle Database never refreshes this materialized view
===============================================================================
2.refresh force on commit;
SQL> create materialized view mv refresh force on commit as select * from mv_master;
create materialized view mv refresh force on commit as select * from mv_master
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
SQL> ed
Wrote file afiedt.buf
1 begin
2 dbms_mview.explain_mview('select * from test.mv_master');
3* end;
SQL> /
begin
*
ERROR at line 1:
ORA-30377: table TEST.MV_CAPABILITIES_TABLE not found
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XRWMV", line 22
ORA-06512: at "SYS.DBMS_XRWMV", line 42
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3319
ORA-06512: at line 2
需要调用如下包，
SQL> @?/rdbms/admin/utlxmv.sql
Table created.
SQL> select msgtxt from mv_capabilities_table where capability_name='REFRESH_FAST_AFTER_INSERT' ;
MSGTXT
--------------------------------------------------------------------------------
the detail table does not have a
materialized view log
根据提示，mv_master上，没有materialized view log
SQL> create materialized view log on mv_master;
create materialized view log on mv_master
*
ERROR at line 1:
ORA-12014: table 'MV_MASTER' does not contain a primary key constraint
提示说明mv_master上面没有primary key
SQL> alter table mv_master add primary key (object_id);
Table altered.
(1) 没有create materialized view log;
SQL> create materialized view mv refresh force on commit as select * from mv_master;
Materialized view created.
SQL> delete from mv_master where id<10000;
9999 rows deleted.
Elapsed: 00:00:00.29
SQL> commit;
Commit complete.
Elapsed: 00:00:13.54
commit时候，消耗的时间比较多。
(2)create materialized view log;
SQL> create materialized view log on mv_master;
Materialized view log created.
SQL> create materialized view mv refresh force on commit as select * from mv_master;
Materialized view created.
SQL> delete from mv_master where id<100000;
99999 rows deleted.
Elapsed: 00:00:03.38
SQL> commit;
Commit complete.
Elapsed: 00:00:06.56
似乎是要快一下。在相同的情况下。
==========================================================================
3.refresh force on commit with rowid(很少使用)
SQL> create table tt_master as select * from all_objects;
Table created.
SQL> create materialized view log on tt_master with rowid;
Materialized view log created.
SQL> create index tt_master_index on tt_master(object_id);
Index created.
SQL> alter table tt_master add primary key(object_id) using index;
Table altered.
测试的时候，没有加上primary key，不能创建materialized view
SQL> create materialized view tt_mv refresh force on commit as select * from tt_master;
Materialized view created.
SQL> select count(*) from tt_master;
COUNT(*)
----------
11198
SQL> select count(*) from tt_mv;
COUNT(*)
----------
11198
SQL> delete from tt_master where object_id<1000;
852 rows deleted.
SQL> commit; ---其中commit有点慢，测试的时候。
Commit complete.
SQL> select count(*) from tt_master;
COUNT(*)
----------
10346
SQL> select count(*) from tt_mv;
COUNT(*)
----------
10346
(1)alter table tt_master move;
SQL> alter table tt_master move;
Table altered.
SQL> delete from tt_master where object_id<4000;
delete from tt_master where object_id<4000
*
ERROR at line 1:
ORA-01502: index 'TEST.TT_MASTER_INDEX' or partition of such index is in
unusable state
SQL> alter index tt_master_index rebuild;
Index altered.
Elapsed: 00:00:02.07
SQL> delete from tt_master where object_id<4000;
1000 rows deleted.
Elapsed: 00:00:00.08
SQL> commit;
Commit complete.
(2)alter table tt_master shrink; ---不能shrink space
SQL> alter table tt_master shrink space;
alter table tt_master shrink space
*
ERROR at lin
e 1:
ORA-10663: Object has rowid based materialized views
========================================================================
refresh method
COMPLETE - Materialized view is completely refreshed from the masters
FORCE - Oracle Database performs a fast refresh if possible, otherwise a complete refresh
FAST - Oracle Database performs an incremental refresh applying changes that correspond to
changes in the masters since the last refresh
NEVER - User specified that Oracle Database should not refresh this materialized view
build mode
IMMEDIATE - Populated from the masters during creation(默认设置)
DEFERRED - Not populated during creation. Must be explicitly populated later by the user.
PREBUILT - Populated with an existing table during creation. The relationship of the contents of
this prebuilt table to the materialized view's masters is unknown to Oracle Database.
=========================================================================
4.refresh 测试
(1) refresh fast on commit
SQL> create materialized view mv refresh fast on commit
2 as
3 select a.owner,a.object_name from tt a,tt1 b where a.object_id=b.object_id;
select a.owner,a.object_name from tt a,tt1 b where a.object_id=b.object_id
*
ERROR at line 3:
ORA-12052: cannot fast refresh materialized view TEST.MV
SQL> begin
2 dbms_mview.explain_mview('select a.owner,a.object_name from test.tt a,test.tt1 b where a.object_id=b.object_id');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select msgtxt from mv_capabilities_table where capability_name='REFRESH_FAST_AFTER_INSERT' ;
MSGTXT
--------------------------------------------------------------------------------
the SELECT list does not have the rowids of all the detail tables
mv log must have ROWID
mv log must have ROWID
(2) refresh force on commit [build immediate]
SQL> create materialized view mv refresh force on commit
2 as
3 select a.owner,a.object_name from test.tt a,test.tt1 b where a.object_id=b.object_id
4 /
Materialized view created.
SQL> select owner,mview_name,rewrite_enabled,refresh_mode,refresh_method,build_mode from user_mviews;
OWNER MVIEW_NAME REW REFRESH_MODE REFRESH_METHOD BUILD_MODE
---------- ---------- --- ------------------ ------------------------ ---------------------------
TEST MV N COMMIT FORCE IMMEDIATE
SQL> select count(*) from mv;
COUNT(*)
----------
999
SQL> delete from tt where object_id<200;
182 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from mv;
COUNT(*)
----------
817
(3)refresh force on commit [build deferred]
SQL> create materialized view mv1 build deferred refresh force on commit
2 as
3 select a.owner,a.object_name from test.tt a,test.tt1 b where a.object_id=b.object_i
d
4 /
Materialized view created.
SQL> select count(*) from mv1;
COUNT(*)
----------
0
SQL> exec dbms_mview.refresh('mv1')
PL/SQL procedure successfully completed.
SQL> select count(*) from mv1;
COUNT(*)
----------
817
SQL> select owner,mview_name,rewrite_enabled,refresh_mode,refresh_method,build_mode from user_mviews;
OWNER MVIEW_NAME REW REFRESH_MODE REFRESH_METHOD BUILD_MODE
---------- ---------- --- ------------------ ------------------------ ---------------------------
TEST MV1 N COMMIT FORCE DEFERRED
TEST MV N COMMIT FORCE IMMEDIATE
(4)refresh force on commit [prebuilt]
SQL> create materialized view mv2 on prebuilt table with reduced precision as
2 select a.owner,a.object_name from test.tt a,test.tt1 b where a.object_id=b.object_id;
create materialized view mv2 on prebuilt table with reduced precision as
*
ERROR at line 1:
ORA-12059: prebuilt table "TEST"."MV2" does not exist
SQL> create table mv2 as
2 select a.owner,a.object_name from tt a,tt1 b where a.object_id=b.object_id;
Table created.
SQL> create materialized view mv2 on prebuilt table with reduced precision as
2 select a.owner,a.object_name from tt a,tt1 b where a.object_id=b.object_id;
Materialized view created.
SQL> select owner,mview_name,rewrite_enabled,refresh_mode,refresh_method,build_mode from user_mviews;
OWNER MVIEW_NAME REW REFRESH_MODE REFRESH_METHOD BUILD_MODE
---------- ---------- --- ------------------ ------------------------ ---------------------------
TEST MV N COMMIT FORCE IMMEDIATE
TEST MV2 N DEMAND FORCE PREBUILT
SQL> select count(*) from mv2;
COUNT(*)
----------
817
SQL> exec dbms_mview.refresh('mv2')
PL/SQL procedure successfully completed.
SQL> select count(*) from mv2;
COUNT(*)
----------
730
=======================================================================
5.
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'
2 ;
Session altered.
SQL> select * from mlog$_tt where rownum<5;
OBJECT_ID SNAPTIME$$ DML OLD CHANGE_VEC XID$$
---------- ------------------- --- --- ---------- -----------------
300 2012/08/24 16:46:28 D O 0000 2251920072769838
301 2012/08/24 16:46:28 D O 0000 2251920072769838
302 2012/08/24 16:46:28 D O 0000 2251920072769838
303 2012/08/24 16:46:28 D O 0000 2251920072769838
object_id是tt table的primary key.
XID$$是事务标志
mlog$_tt 随着日志的不断增加，mlog$_tt会一直增加，期间空间不会缩减。
SQL> alter table mlog$_tt move;
Table altered.
SQL> select table_name from dict where tabl
e_name like 'DBA_VIEWS%';
TABLE_NAME
------------------------------------------------------------
DBA_VIEWS
DBA_VIEWS_AE
===============================================================
创建materialized view的一些限制(引用https://www.doczj.com/doc/e612834884.html,/2012/08/22/oracle-materizlized-view-study-1.html)。
1)Materialized views must be primary key materialized views.
---基表必须存在主键
2)The master's materialized view log must include certain columns referenced in the subquery. For information about
which columns must be included, see "Logging Columns in the Materialized View Log".
---基表的物化视图日志必须包含子查询涉及的列。
3) If the subquery is many to many or one to many, join columns that are not part of a primary key must be included
in the materialized view log of the master. This restriction does not apply to many to one subqueries.
---对于多对多或一对多的子查询，join 列可以不是主键的一部分，但是必须是基表物化视图日志的一部分。多对一的情况不存在这个限制。
4) The subquery must be a positive subquery. For example, you can use the EXISTS condition, but not the NOT EXISTS condition.
---子查询必须是积极的，例如你的条件可以使用exists，但是不能使用not exists。
5) The subquery must use EXISTS to connect each nested level (IN is not allowed).
---对于嵌套的子查询，必须使用exists，不能使用in。
6) Each table can be in only one EXISTS expression.
---每个表只能被exists引用1次。
7) The join expression must use exact match or equality comparisons (that is, equi-joins).
--连接表达式必须使用精确匹配或"=" 进行操作。
8) Each table can be joined only once within the subquery.
---每个表在子查询中只能被join 1次。
9 A primary key must exist for each table at each nested level.
---对于嵌套的字句每一层涉及的表都必须存在主键。
10) Each nested level can only reference the table in the level above it.
---每个嵌套层只能引用其上层嵌套的表。
11) Subqueries can include AND conditions, but each OR condition can only reference columns contained within one row.
Multiple OR conditions within a subquery can be connected with an AND condition.
---子查询中可以包含and条件，如果存在or，那么每个条件只能引用一个列。对于多个or存在的情况，可以和and 条件
进行关联。
12) All tables referenced in a subquery must reside in the same master site or master materialized view site.
---子查询中所有引用的表在master站点或master MV站点中都必须是存在的。
=============================================
关于包含聚合的物化视图
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)。
++++++++ 包含UNION ALL的物化视图：
1.UNION ALL操作必须在查询的顶层。可以有一种情况例外：UNION ALL在第二层，而第一层的查询语句为SELECT * FROM；
2.被UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件；
3.SELECT列表中必须包含一列维护列，叫做UNION ALL标识符，每个UNION ALL分支的标识符列应包含不同的常量值；
4.不支持外连接、远端数据库表和包括只允许插
入的聚集物化视图定义查询；
5.不支持基于分区改变跟踪（PCT）的刷新；
6.兼容性设置应设置为9.2.0。
++++++++ 嵌套物化视图：
1. 嵌套物化视图的每层都必须满足快速刷新的限制条件；
2. 对于同时包含聚集和连接的嵌套物化视图，不支持ON COMMIT的快速刷新。