Oracle materizlized view Study (1)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
++++++ Oracle 物化视图学习笔记之一 ++++++++
首先描述几个术语，如下：
基表：也就是我们常说的master table和master materialized view，这里说明一下，基表并不是说
这只有一个表，它是你创建MV时所需要用到的表（可能有多个)或相关的上一级的MV。
MV： 也就是物化视图，英文名materizlized view。
源端(master 站点,master MV size)： 都是指基表所在的一端(基表所在数据库)
MV端(mv站点,mv site): 物化视图所在地一端(mv所在数据库)
需要注意的是，MV跟普通的view不同，它是有segment存在的，不像view那样是虚拟存在的。你可以再dba_semgents中
查到它的对象大小，当然它的好处就是，我们在利用MV进行查询时，不再需要去访问基表了，只需通过访问MV对应的
结果集就行了。当然，这里涉及一个刷新机制，MV的数据和基表的数据是定期刷新来完成同步的。后面会讲到涉及的
集中mv的刷新机制。
下面是一个简单的创建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 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
SQL> show user USER is "KILLDB" SQL> create table mv_master as select * from sys.dba_objects; Table created. SQL> SQL> create materialized view mv_tab1 as select * from mv_master; Materialized view created. SQL> SQL> select count(*) from mv_master; COUNT(*) ---------- 50901 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50901 SQL> delete from mv_master where rownum < 101; 100 rows deleted. SQL> commit; Commit complete. SQL> select count(*) from mv_master 2 ; COUNT(*) ---------- 50801 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50901 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50901 SQL> 为什么MV没有同步呢？因为默认的mv刷新模式的DEMAND 如下： SQL> select OWNER,MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE,FAST_REFRESHABLE 2 from sys.dba_mviews where owner='KILLDB'; OWNER MVIEW_NAME REFRES REFRESH_ BUILD_MOD FAST_REFRESHABLE --------------- -------------------- ------ -------- --------- ------------------ KILLDB MV_TAB1 DEMAND FORCE IMMEDIATE NO 下面我们进行手工刷新： SQL> show user USER is "KILLDB" SQL> exec dbms_mview.refresh('MV_TAB1'); PL/SQL procedure successfully completed. SQL> select count(*) from mv_master; COUNT(*) ---------- 50801 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50801 ---可以看到，数据同步了。 |
我们来看看官方文档中对于mv 刷新模式的解释，有3种方式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DEMAND - Oracle Database refreshes this materialized view whenever an appropriate refresh procedure is called 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 demand: 当调用dbms_mview时才进行刷新。 commit: 当基表有事务commit后就进行刷新同步。 never： 不刷新MV。 下面我们来创建基于commit的mv看看是怎么样的： SQL> drop materialized view mv_tab1; Materialized view dropped. SQL> create materialized view mv_tab1 refresh force on commit as select * from mv_master; create materialized view mv_tab1 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 |
|
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 |
发现报错，怎么办呢？关于mv方面的错误，oracle提供了一个脚本utlxmv.sql，我们借助这个sql 来进行诊断，如下操作： SQL> conn /as sysdba Connected. SQL> begin 2 dbms_mview.explain_mview('select * from killdb.mv_master'); 3 end; 4 / PL/SQL procedure successfully completed. 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 SQL> 很明显，提示我们需要为改表创建物化视图日志。 SQL> conn killdb/killdb Connected. 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 SQL> |
我们可以来看看这个错误的具体解释：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[oracle@10gasm ~]$ [oracle@10gasm ~]$ oerr ora 12014 12014, 00000, "table '%s' does not contain a primary key constraint" // *Cause: The CREATE MATERIALIZED VIEW LOG command was issued with the // WITH PRIMARY KEY option and the master table did not contain // a primary key constraint or the constraint was disabled. // *Action: Reissue the command using only the WITH ROWID option, create a // primary key constraint on the master table, or enable an existing // primary key constraint. [oracle@10gasm ~]$ 这里又涉及一个问题，那就是创建物化视图日志的方式有如下几种： 1) 基于主键 2) 基于rowid 4) 基于object id 4) 基于sequence+rowid+(属性列) |
下面来分别描述这4种情况下的创建情况：
1. 基于主键
|
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 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
SQL> alter table mv_master add constraint pk_mv_master primary key (object_id); alter table mv_master add constraint pk_mv_master primary key (object_id) * ERROR at line 1: ORA-01449: column contains NULL values; cannot alter to NOT NULL SQL> select count(*) from mv_master where object_id is null; COUNT(*) ---------- 1 SQL> delete from mv_master where object_id is null; 1 row deleted. SQL> commit; Commit complete. SQL> alter table mv_master add constraint pk_mv_master primary key (object_id); Table altered. SQL> create materialized view mv_tab1 refresh force on commit as select * from mv_master; Materialized view created. SQL> SQL> delete from mv_master where rownum < 101; 100 rows deleted. SQL> commit; Commit complete. SQL> set timing on SQL> delete from mv_master where rownum < 101; 100 rows deleted. Elapsed: 00:00:00.03 SQL> commit; Commit complete. Elapsed: 00:00:11.68 SQL> set autot traceonly SQL> set lines 150 SQL> select count(*) from mv_tab1; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1259844547 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| PK_MV_MASTER1 | 46897 | 33 (0)| 00:00:01 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> analyze table mv_tab1 compute statistics; Table analyzed. Elapsed: 00:00:00.66 SQL> select count(*) from mv_tab1; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1259844547 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| PK_MV_MASTER1 | 50600 | 30 (0)| 00:00:01 | ------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 112 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> 从上面可以看到，mv_tab1在基表进行commit时就会进行同步，同时问题也来了，你可以看到上面 commit的时候非常的慢。后面会详细描述mv刷新的几种方式。 这里需要注意一点的是：由于物化视图也是实在存在的对象，所以它也是有统计信息的，从上面的 执行计划你可以可发现，不对物化视图进行统计信息的收集，其统计信息是不准确的。 当你创建物化视图日志以后，你会发现速度on commit速度是比较快的： SQL> drop materialized view mv_tab1; Materialized view dropped. Elapsed: 00:00:00.10 SQL> create materialized view log on mv_master; Materialized view log created. Elapsed: 00:00:00.16 SQL> create materialized view mv_tab1 refresh force on commit as select * from mv_master; Materialized view created. Elapsed: 00:00:00.63 SQL> analyze table mv_tab1 compute statistics; Table analyzed. Elapsed: 00:00:00.53 SQL> select count(*) from mv_master; COUNT(*) ---------- 50600 Elapsed: 00:00:00.01 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50600 Elapsed: 00:00:00.01 SQL> delete from mv_master where rownum < 101; 100 rows deleted. Elapsed: 00:00:00.06 SQL> commit; Commit complete. Elapsed: 00:00:00.10 SQL> select count (*) from mv_master; COUNT(*) ---------- 50500 Elapsed: 00:00:00.01 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50500 Elapsed: 00:00:00.01 SQL> |
2) 基于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 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 |
SQL> drop materialized view log on mv_master; Materialized view log dropped. Elapsed: 00:00:00.05 SQL> drop materialized view mv_tab1; Materialized view dropped. Elapsed: 00:00:00.06 SQL> SQL> create materialized view log on mv_master with rowid; Materialized view log created. Elapsed: 00:00:00.07 SQL> create materialized view mv_tab1 refresh force on commit as select * from mv_master; Materialized view created. Elapsed: 00:00:02.26 SQL> analyze table mv_tab1 compute statistics; Table analyzed. Elapsed: 00:00:00.45 SQL> select count(*) from mv_master; COUNT(*) ---------- 50500 Elapsed: 00:00:00.02 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50500 Elapsed: 00:00:00.01 SQL> delete from mv_master where rownum < 101; 100 rows deleted. Elapsed: 00:00:00.04 SQL> commit; Commit complete. Elapsed: 00:00:11.84 SQL> select count(*) from mv_master; COUNT(*) ---------- 50400 Elapsed: 00:00:00.01 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50400 Elapsed: 00:00:00.02 SQL> 可以看到基于rowid的方式，基表进行commit时是非常慢的，还有个需要注意的是，当你基表进行move或shrink等操作 后，rowid会发现变化的。 SQL> alter table mv_master move; Table altered. Elapsed: 00:00:02.17 SQL> select count(*) from mv_master; COUNT(*) ---------- 50300 Elapsed: 00:00:00.01 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50300 Elapsed: 00:00:00.02 SQL> delete from mv_master where rownum < 101; delete from mv_master where rownum < 101 * ERROR at line 1: ORA-01502: index 'KILLDB.PK_MV_MASTER' or partition of such index is in unusable state Elapsed: 00:00:00.02 SQL> alter index PK_MV_MASTER rebuild; Index altered. Elapsed: 00:00:11.54 SQL> delete from mv_master where rownum < 101; 100 rows deleted. Elapsed: 00:00:00.04 SQL> commit; Commit complete. Elapsed: 00:00:11.98 SQL> select count(*) from mv_master; COUNT(*) ---------- 50200 Elapsed: 00:00:00.01 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50200 Elapsed: 00:00:00.01 |
3) 基于object id
|
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 |
SQL> drop materialized view log on mv_master; Materialized view log dropped. Elapsed: 00:00:00.02 SQL> drop materialized view mv_tab1; Materialized view dropped. Elapsed: 00:00:00.34 SQL> SQL> drop table mv_master; Table dropped. Elapsed: 00:00:00.28 SQL> set timing off SQL> create type mv1 as object(id number,owner varchar2(30)) 2 / Type created. SQL> create table mv_master of mv1; Table created. SQL> create materialized view log on mv_master with object id; Materialized view log created. SQL> create materialized view mv_tab1 as select * from mv_master; Materialized view created. SQL> insert into mv_master select object_id,owner from sys.dba_objects; 50908 rows created. SQL> commit; Commit complete. SQL> select count(*) from mv_master; COUNT(*) ---------- 50908 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(*) ---------- 50908 SQL> |
4) 基于rowid+sequence+column
|
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 |
SQL> drop materialized view mv_tab1; Materialized view dropped. SQL> drop materialized view log on mv_master; Materialized view log dropped. SQL> create materialized view log on mv_master with rowid,sequence (id, owner) including new values 2 / Materialized view log created. SQL> create materialized view mv_tab1 as select * from mv_master; Materialized view created. SQL> select count(*) from mv_master; COUNT(*) ---------- 50908 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50908 SQL> delete from mv_master where rownum < 10001; 10000 rows deleted. SQL> commit; Commit complete. SQL> SQL> select count(*) from mv_master; COUNT(*) ---------- 40908 SQL> select count(*) from mv_tab1; COUNT(*) ---------- 50908 SQL> exec dbms_mview.refresh('MV_TAB1'); PL/SQL procedure successfully completed. SQL> select count(*) from mv_tab1; COUNT(*) ---------- 40908 |
简单的总结一下，基于rowid的方式其实都是向后兼容的，从8i以后基本上就不用基于rowid的方式了，
当然也不排除一些特殊的场景仍然可以使用。
补充：
关于表mv_capabilities_table的说明，在10g中是没有，只能去参考utlxmv.sql脚本，在11g中官方文档就已经进行明确说明了
可以参考如下链接http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG8223
4 Responses to “Oracle materizlized view Study (1)”
[…] Oracle materizlized view Study (1) 作者：lovewifelovelife 发表于2012-8-22 14:51:42 原文链接 阅读：17 评论：0 […]
研究的东西还真广耶
good !
sku,studing
Leave a Reply
You must be logged in to post a comment.