Oracle materizlized view Study (2)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
上一篇对oracle中物化视图有了一些基本了解，知道物化视图日志的创建方式。在这篇文章中，将重点讲解
物化视图的刷新方式，这也是一个比较难的知识点，和大家以前学习。因为很长一段时间不用mv，说真的，我
基本上都忘的差不多了，最近的项目涉及到物化视图的一个几个库（超过3T）的迁移，所以温习下。
我们通过查看试图dba_mviews的REFRESH_METHOD字段可以发现该自动有如下几种属性，换句话说也就是说
物化视图有如下几种刷新方式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
COMPLETE (C) - Materialized view is completely refreshed from the masters FORCE (?) - Oracle Database performs a fast refresh if possible, otherwise a complete refresh FAST (F) - Oracle Database performs an incremental refresh applying changes that correspond to changes in the masters since the last refresh NEVER (N) - User specified that the Oracle Database should not refresh this materialized view 简单在总结为如下几点: compelete: 完全刷新，在压力比较大的情况下，完全刷新可能会带来很多问题，特别是跨站点的情况，对网络要求很高。 force：强制刷新，竟可能的使用增量快速刷新，如果不能则使用完全刷新。默认值是force。 fast： 增量快速刷新。 never：从不刷新。 还有一个字段BUILD_MODE，这是表明创建mv的方式，说白了就是指在创建mv时是否生成数据。默认有如下几种： 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 the Oracle Database. |
简单总结为如下几点：
immediate：创建mv时就生成数据了，也就是在创建mv时就会进行一次完全刷新，同步数据。默认方式。
deferred: 在创建mv时不生成数据，后面跟你的操作实际需要才生成数据，换句话说，使用这种方式创建mv时，当你查询
mv时数据是空的，比如你手工刷新以后，才能查到数据。
prebuilt: 创建时需要先存在跟物化视图存在相同的对象，不然会报错ora-12059。
如下例子：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SQL> create table t1 as select * from sys.dba_objects where rownum < 10001; Table created. SQL> create table t2 as select * t1; Table created. SQL> SQL> alter table t1 add constraint pk_t1 primary key(object_id) ; Table altered. SQL> alter table t2 add constraint pk_t2 primary key(object_id) ; Table altered. SQL> create materialized view log on t1; Materialized view log created. SQL> create materialized view log on t2; Materialized view log created. SQL> create materialized view mv_tab1 refresh fast on commit as 2 select a.owner,b.object_name from t1 a,t2 b 3 where a.object_id=b.object_id; select a.owner,b.object_name from t1 a,t2 b * ERROR at line 2: ORA-12052: cannot fast refresh materialized view KILLDB.MV_TAB1 ---conn /as sysdba SQL> delete mv_capabilities_table; 92 rows deleted. SQL> commit; Commit complete. SQL> begin 2 dbms_mview.explain_mview('select a.owner,b.object_name from killdb.t1 a,killdb.t2 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 SQL> |
—conn killdb/killdb
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
SQL> create materialized view mv_tab1 refresh force on commit as 2 select a.owner,b.object_name from t1 a,t2 b 3 where a.object_id=b.object_id; Materialized view created. SQL> SQL> select owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE 2 from sys.dba_mviews where MVIEW_NAME='MV_TAB1'; OWNER MVIEW_NAME R REFRES REFRESH_ BUILD_MOD --------------- -------------------- - ------ -------- --------- KILLDB MV_TAB1 N COMMIT FORCE IMMEDIATE SQL> SQL> select count(*) from mv_tab1; COUNT(*) ---------- 9562 SQL> SQL> drop materialized view mv_tab1; Materialized view dropped. SQL> create materialized view mv_tab1 build deferred refresh force on commit as 2 select a.owner,b.object_name from t1 a,t2 b 3 where a.object_id=b.object_id; Materialized view created. SQL> select count(*) from mv_tab1; COUNT(*) ---------- 0 SQL> exec dbms_mview.refresh('MV_TAB1'); PL/SQL procedure successfully completed. SQL> select count(*) from mv_tab1; COUNT(*) ---------- 9562 SQL> SQL> drop materialized view mv_tab1; Materialized view dropped. SQL> create materialized view mv_tab1 on prebuilt table with reduced precision as 2 select a.owner,b.object_name from t1 a,t2 b 3 where a.object_id=b.object_id; create materialized view mv_tab1 on prebuilt table with reduced precision as * ERROR at line 1: ORA-12059: prebuilt table "KILLDB"."MV_TAB1" does not exist SQL> ! [ora10g@killdb ~]$ oerr ora 12059 12059, 00000, "prebuilt table \"%s\".\"%s\" does not exist" // *Cause: The specified prebuilt table did not exist. // *Action: Reissue the SQL command using BUILD IMMEDIATE, BUILD DEFERRED, or // ensure that the prebuilt table exists. // [ora10g@killdb ~]$ [ora10g@killdb ~]$ exit exit SQL> create materialized view mv_tab1 refresh force on commit as 2 select a.owner,b.object_name from t1 a,t2 b 3 where a.object_id=b.object_id; Materialized view created. SQL> create table mv_tab2 as select a.owner,b.object_name from t1 a,t2 b 2 where a.object_id=b.object_id; Table created. SQL> create materialized view mv_tab2 on prebuilt table with reduced precision as 2 select a.owner,b.object_name from t1 a,t2 b 3 where a.object_id=b.object_id; Materialized view created. SQL> SQL> select owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE 2 from sys.dba_mviews where owner='KILLDB'; OWNER MVIEW_NAME R REFRES REFRESH_ BUILD_MOD --------------- -------------------- - ------ -------- --------- KILLDB MV_TAB1 N COMMIT FORCE IMMEDIATE KILLDB MV_TAB2 N DEMAND FORCE PREBUILT SQL> select count(*) from mv_tab1; COUNT(*) ---------- 9562 SQL> select count(*) from mv_tab2; COUNT(*) ---------- 9562 SQL> delete from t1 where object_id < 101; 99 rows deleted. SQL> delete from t2 where object_id < 101; 99 rows deleted. SQL> commit; Commit complete. SQL> select count(*) from mv_tab1; COUNT(*) ---------- 9463 SQL> select count(*) from mv_tab2; COUNT(*) ---------- 9562 SQL> exec dbms_mview.refresh('MV_TAB2'); PL/SQL procedure successfully completed. SQL> select count(*) from mv_tab2; COUNT(*) ---------- 9463 |
由于通常是用快速刷新，所以这里重点描述下快速刷新。要创建快速刷新的物化视图，需要满足一些列条件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
所以快速刷新的物化视图必须满足下面2点： 1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM； 2.物化视图不能包含对LONG和LONG RAW数据类型的引用。 只包含连接的物化视图： 1.必须满足所有快速刷新物化视图都满足的条件； 2.不能包括GROUP BY语句或聚集操作； 3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上； 4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。 5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。 6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。 从我们前面的例子都可以看到，必须创建物化视图日志，且必须是基于rowid方式，不然就会报错。 |
如果是包含子查询的物化视图，就更复杂了。看下面的例子：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
SQL> drop materialized view mv_tab1; Materialized view dropped. SQL> drop materialized view mv_tab2; Materialized view dropped. SQL> drop materialized view mv_tab1; Materialized view dropped. SQL> drop materialized view mv_tab2; Materialized view dropped. SQL> create table t1 as select * from sys.dba_objects; Table created. SQL> alter table t1 add constraint pk_t1 primary key(object_id) ; Table altered. SQL> create materialized view log on t1 with primary key; Materialized view log created. SQL> create materialized view mv_tab1 REFRESH FAST FOR UPDATE AS 2 select * from t1 where object_id > 10000 and object_id < 30001; Materialized view created. SQL> select count(*) from mv_tab1; COUNT(*) ---------- 19890 SQL> delete from t1 where object_id > 20000 and object_id < 25000; 4999 rows deleted. SQL> commit; Commit complete. SQL> select count(*) from mv_tab1; COUNT(*) ---------- 19890 SQL> exec dbms_mview.refresh('MV_TAB1'); PL/SQL procedure successfully completed. SQL> select count(*) from mv_tab1; COUNT(*) ---------- 14891 |
关于物化视图快速刷新涉及子查询的情况，有一些限制，如下（来自官方文档）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
SQL> create materialized view mv_tab3 refresh fast on commit as 2 select * from t3 where EXISTS (select * from t4 where object_id like '2%') 3 and object_id > 10000 and object_id < 25000; and object_id > 10000 and object_id < 25000 * ERROR at line 3: ORA-12014: table 'T4' does not contain a primary key constraint SQL> create materialized view mv_tab3 refresh fast on commit as 2 select * from t3 where EXISTS (select * from t4 where object_id like '2%') 3 and object_id > 10000 and object_id < 25000; and object_id > 10000 and object_id < 25000 * ERROR at line 3: ORA-12014: table 'T4' does not contain a primary key constraint SQL> alter table t4 add constraint pk_t4 primary key(object_id) ; Table altered. SQL> create materialized view mv_tab3 refresh fast on commit as 2 select * from t3 where EXISTS (select * from t4 where object_id like '2%') 3 and object_id > 10000 and object_id < 25000; and object_id > 10000 and object_id < 25000 * ERROR at line 3: ORA-23413: table "KILLDB"."T4" does not have a materialized view log SQL> create materialized view log on t4 with primary key; Materialized view log created. SQL> create materialized view mv_tab3 refresh fast on commit as 2 select * from t3 where EXISTS (select * from t4 where object_id like '2%') 3 and object_id > 10000 and object_id < 25000; and object_id > 10000 and object_id < 25000 * ERROR at line 3: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view SQL> </pre 最后来讲讲关于刷新方面的一些操作，通常我们可以利用dbms_mview.refresh 包，如下： exec dbms_mview.refresh('mv_tab1'); --不指定参数，是根据你创建物化视图的REFRESH字句来进行的。 exec dbms_mview.refresh('mv_tab1','F'); --快速刷新 exec dbms_mview.refresh('mv_tab1','C'); --完全刷新 那么我们如何进行定时刷新？ 有几种方式，利用dbms_job，这里主要将另外一种方式，如下： <pre lang="sql"> SQL> create materialized view mv_tab4 refresh start with sysdate next sysdate+1/24 2 as select * from t4 where object_id < 30001; Materialized view created. SQL> select job,next_date,next_sec,interval,what from user_jobs; JOB NEXT_DATE NEXT_SEC INTERVAL WHAT ------ --------- ---------------- ---------------- --------------------------------------------- 21 22-AUG-12 04:13:38 sysdate+1/24 dbms_refresh.refresh('"KILLDB"."MV_TAB4"'); |
另外，关于快速刷新，还涉及到很多内容，如下内容来自老杨的博客：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
+++++++++ 关于包含聚合的物化视图 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的快速刷新。 |
关于物化视图的内容非常多，详细的东西参考官方文档，老杨的这部分内容其实都有些老了，部分是9i的。
另外还有MV GROUP等等，不多说了。
2 Responses to “Oracle materizlized view Study (2)”
very good
9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；
请问兼容性如何设置？
Leave a Reply
You must be logged in to post a comment.