- 浏览: 836336 次
文章分类
- 全部博客 (365)
- java (124)
- spring mvc (21)
- spring (22)
- struts2 (6)
- jquery (27)
- javascript (24)
- mybatis/ibatis (8)
- hibernate (7)
- compass (11)
- lucene (26)
- flex (0)
- actionscript (0)
- webservice (8)
- rabbitMQ/Socket (15)
- jsp/freemaker (5)
- 数据库 (27)
- 应用服务器 (21)
- Hadoop (1)
- PowerDesigner (3)
- EJB (0)
- JPA (0)
- PHP (2)
- C# (0)
- .NET (0)
- html (2)
- xml (5)
- android (7)
- flume (1)
- zookeeper (0)
- 证书加密 (2)
- maven (1)
- redis (2)
- cas (11)
最新评论
-
zuxianghuang:
通过pom上传报错 Artifact upload faile ...
nexus上传了jar包.通过maven引用当前jar,不能取得jar的依赖 -
流年末年:
百度网盘的挂了吧???
SSO单点登录系列3:cas-server端配置认证方式实践(数据源+自定义java类认证) -
953434367:
UfgovDBUtil 是什么类
Java发HTTP POST请求(内容为xml格式) -
smilease:
帮大忙了,非常感谢
freemaker自动生成源代码 -
syd505:
十分感谢作者无私的分享,仔细阅读后很多地方得以解惑。
Nginx 反向代理、负载均衡、页面缓存、URL重写及读写分离详解
物化视图是Oracle令人激赏的功能之一,在OLAP和OLTP系统都有广泛应用。本系列文章对其进行由浅入深的案例讲解。本文侧重在最简单的ON DEMAND和ON COMMIT物化视图的讨论。
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。
Oracle最早在OLAP系统中引入了物化视图的概念。但后来很多大型OLTP系统中,发现类似统计的查询是无可避免,而这些查询操作如果很频繁,对整体数据库性能是很致命的。于是Oracle开始不断的改进物化视图,使得其也开始合适OLTP系统。从Oracle 8i到现在,功能已经相对比较完备了。
本文是Oracle物化视图系列文章的第一篇,有两个主要目的,来体验一下创建ON DEMAND和ON COMMIT物化视图的方法。ON DEMAND和ON COMMIT物化视图的区别在于其刷新方法的不同,ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。
--获取数据库rdbms版本信息
- SQL> select * from v$version;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
- PL/SQL Release 11.1.0.6.0 - Production
- CORE 11.1.0.6.0 Production
- TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
- NLSRTL Version 11.1.0.6.0 – Production
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 – Production
创建ON DEMAND物化视图
下面创建一个最简单的物化视图,这个物化视图的定义很类似于普通视图的创建语句,只是多了一个materialized,但就是这个单词,造成了物化视图和普通视图(虚拟表)的天壤之别,也引申出后面很多的事情,呵呵。
本例中需要特别注意的是,Oracle给物化视图的重要定义参数的默认值处理,在下面的例子中会有特别说明。因为物化视图的创建本身是很复杂和需要优化参数设置的,特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的,类似于普通视图的办法来做,所以不可避免的会涉及到默认值问题。
像我们这样,创建物化视图时未作指定,则Oracle按ON DEMAND模式来创建。
从下例中可以看出:
1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证;
2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;
3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证。
--创建物化视图
- SQL> create materialized view mv_testcf
- 2 as
- 3 select * from xiaotg.testcf;
- Materialized view created
SQL> create materialized view mv_testcf 2 as 3 select * from xiaotg.testcf; Materialized view created
--分析物化视图,以获得统计信息
SQL> analyze table xiaotg.mv_testcf compute statistics; Table analyzed
--查看物化视图的行数,发现和master表(TESTCF)一样
- SQL> select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'TESTCF', 'MV_TESTCF' );
- TABLE_NAME NUM_ROWS
- ------------------------------ ----------
- MV_TESTCF 80000
- TESTCF 80000
SQL> select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'TESTCF', 'MV_TESTCF' ); TABLE_NAME NUM_ROWS ------------------------------ ---------- MV_TESTCF 80000 TESTCF 80000
--查看物化视图的存储参数
- SQL> col segment_name for a24
- SQL> select sg.segment_name, sg.bytes, sg.blocks from user_segments sg where sg.segment_name = 'MV_TESTCF';
- SEGMENT_NAME BYTES BLOCKS
- ------------------------ ---------- ----------
- MV_TESTCF 9437184 1152
SQL> col segment_name for a24 SQL> select sg.segment_name, sg.bytes, sg.blocks from user_segments sg where sg.segment_name = 'MV_TESTCF'; SEGMENT_NAME BYTES BLOCKS ------------------------ ---------- ---------- MV_TESTCF 9437184 1152
查看物化视图关键定义
--查看物化视图的定义设置,请关注蓝色字体部分。
--这表明,默认情况下,如果没指定刷新方法和刷新模式,则Oracle默认为FORCE和DEMAND。
--其他的集中刷新方法和刷新模式以后将分别予以介绍。
- SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'MV_TESTCF';
- OWNERXIAOTG
- MVIEW_NAMEMV_TESTCF
- CONTAINER_NAMEMV_TESTCF
- QUERY
- QUERY_LEN80
- UPDATABLEN
- UPDATE_LOG
- MASTER_ROLLBACK_SEG
- MASTER_LINK
- REWRITE_ENABLEDN
- REWRITE_CAPABILITYGENERAL
- REFRESH_MODEDEMAND
- REFRESH_METHODFORCE
- BUILD_MODEIMMEDIATE
- FAST_REFRESHABLEDML
- LAST_REFRESH_TYPECOMPLETE
- LAST_REFRESH_DATE2008-9-9 15:02
- STALENESSFRESH
- AFTER_FAST_REFRESHFRESH
- UNKNOWN_PREBUILTN
- UNKNOWN_PLSQL_FUNCN
- UNKNOWN_EXTERNAL_TABLEN
- UNKNOWN_CONSIDER_FRESHN
- UNKNOWN_IMPORTN
- UNKNOWN_TRUSTED_FDN
- COMPILE_STATEVALID
- USE_NO_INDEXN
- STALE_SINCE
- NUM_PCT_TABLES0
- NUM_FRESH_PCT_REGIONS
- NUM_STALE_PCT_REGIONS
SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'MV_TESTCF'; OWNERXIAOTG MVIEW_NAMEMV_TESTCF CONTAINER_NAMEMV_TESTCF QUERY QUERY_LEN80 UPDATABLEN UPDATE_LOG MASTER_ROLLBACK_SEG MASTER_LINK REWRITE_ENABLEDN REWRITE_CAPABILITYGENERAL REFRESH_MODEDEMAND REFRESH_METHODFORCE BUILD_MODEIMMEDIATE FAST_REFRESHABLEDML LAST_REFRESH_TYPECOMPLETE LAST_REFRESH_DATE2008-9-9 15:02 STALENESSFRESH AFTER_FAST_REFRESHFRESH UNKNOWN_PREBUILTN UNKNOWN_PLSQL_FUNCN UNKNOWN_EXTERNAL_TABLEN UNKNOWN_CONSIDER_FRESHN UNKNOWN_IMPORTN UNKNOWN_TRUSTED_FDN COMPILE_STATEVALID USE_NO_INDEXN STALE_SINCE NUM_PCT_TABLES0 NUM_FRESH_PCT_REGIONS NUM_STALE_PCT_REGIONS
测试ON DEMAND物化视图的更新特性
物化视图最重要的功能和特性之一,就是其数据会随着基表(或称主表,master表,本例中为TESTCF)的变化而变,基表数据增了,物化视图数据会变多;基表数据删了,物化视图数据也会变少。
但怎么更新?或者说物化视图的数据怎么随着基表而更新?Oracle提供了两种方式,手工刷新和自动刷新,像我们这种,在物化视图定义时,未作任何指定,那当然是默认的手工刷新了。也就是说,通过我们手工的执行某个Oracle提供的系统级存储过程或包,来保证物化视图与基表数据一致性。
这是最基本的刷新办法了。但所谓的自动刷新,其实也就是Oracle会建立一个job,通过这个job来调用相同的存储过程或包,加以实现,这在本系列文章的第2篇会将以详细阐述。
下面将测试INSERT,UPDATE和DELETE的测试方法类似,大家有兴趣的话,可以自己试一试。
需要注意的是,下面暂不讨论如何刷新ON DEMAND物化视图,这是下一篇文章的内容。下面仅仅关注ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别,即前者不刷新(手工或自动)就不更新物化视图,而后者不刷新也会更新物化视图,——只要基表发生了COMMIT。
- 在基表插入测试数据
基表数据插入后,会发现,物化视图并不会随之更新。
--检查基表和物化视图是否有80001这一行记录。
- SQL> col id for a10;
- SQL> col name for a30;
- SQL> select * from xiaotg.testcf t where t.id = 80001 ;
- ID NAME
- ---------- ------------------------------
- SQL> select * from xiaotg.mv_testcf t where t.id = 80001 ;
- ID NAME
- ---------- ------------------------------
SQL> col id for a10; SQL> col name for a30; SQL> select * from xiaotg.testcf t where t.id = 80001 ; ID NAME ---------- ------------------------------ SQL> select * from xiaotg.mv_testcf t where t.id = 80001 ; ID NAME ---------- ------------------------------
--插入测试数据80001
--这时发现,基表有数据,但物化视图并没有
- SQL> insert into xiaotg.testcf
- 2 values ( 80001, 'xiaotg he he');
- 1 row inserted
- SQL> commit;
- Commit complete
SQL> insert into xiaotg.testcf 2 values ( 80001, 'xiaotg he he'); 1 row inserted SQL> commit; Commit complete
1.2.2 测试物化视图数据是否更新
从下面的实验可以看出,物化视图数据不会更新,即使等上1分钟、1小时、或者1天。
- SQL> select * from xiaotg.testcf t where t.id = 80001 ;
- ID NAME
- ---------- ------------------------------
- 80001 xiaotg he he
- SQL> select * from xiaotg.mv_testcf t where t.id = 80001 ;
- ID NAME
- ---------- ------------------------------
SQL> select * from xiaotg.testcf t where t.id = 80001 ; ID NAME ---------- ------------------------------ 80001 xiaotg he he SQL> select * from xiaotg.mv_testcf t where t.id = 80001 ; ID NAME ---------- ------------------------------
第一个ON COMMIT物化视图
最简单的ON COMMIT物化视图的创建,和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的,所以ON COMMIT物化视图,需要再增加个参数即可。
创建ON COMMIT物化视图
需要注意的是,无法在定义时仅指定ON COMMIT,还得附带个参数才行,本例中附带refresh force,关于这个参数的意思,以后将加以阐述。
--创建ON COMMIT物化视图
- SQL> create materialized view mv_testcf2
- 2 refresh force on commit
- 3 as
- 4 select * from xiaotg.testcf;
- Materialized view created
SQL> create materialized view mv_testcf2 2 refresh force on commit 3 as 4 select * from xiaotg.testcf; Materialized view created
--分析物化视图和基表
- SQL> analyze table xiaotg.mv_testcf2 compute statistics;
- Table analyzed
- SQL> analyze table xiaotg.testcf compute statistics;
- Table analyzed
SQL> analyze table xiaotg.mv_testcf2 compute statistics; Table analyzed SQL> analyze table xiaotg.testcf compute statistics; Table analyzed
--查看当前基表和物化视图的行数
- SQL> select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'TESTCF', 'MV_TESTCF2' );
- TABLE_NAME NUM_ROWS
- ------------------------------ ----------
- MV_TESTCF2 80000
- TESTCF 80000
SQL> select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'TESTCF', 'MV_TESTCF2' ); TABLE_NAME NUM_ROWS ------------------------------ ---------- MV_TESTCF2 80000 TESTCF 80000
查看物化视图关键定义
可以从DBA_MVIEWS中看出,刷新模式为COMMIT,这也是它和上面ON DEMAND物化视图的唯一区别。
- SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'MV_TESTCF';
- REFRESH_MODECOMMIT
- REFRESH_METHODFORCE
- BUILD_MODEIMMEDIATE
SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'MV_TESTCF'; REFRESH_MODECOMMIT REFRESH_METHODFORCE BUILD_MODEIMMEDIATE
测试ON COMMIT物化视图的更新特性
ON COMMIT物化视图会在基表一旦提交时,就会立刻更新物化视图本身,而且一般仅在物化视图数据也被更新后,基表数据才会事实的提交。
这意味着,这种模式可能会导致延迟基表数据的提交。。这点在下面的实验中体现得很清楚。
实验中,对基表TESTCF,平常的COMMIT在0.01秒内可以完成,但在有了ON COMMIT视图MV_TESTCF2后,居然要6秒。速度减低了很多倍。ON COMMIT视图对基表的影响可见一斑。
- 在基表中插入数据
- SQL> set timing on;
- SQL> insert into xiaotg.testcf ( id, name ) values ( 80002, ' xiaotg again he he ');
- 1 row inserted
- Executed in 0.015 seconds
- SQL> commit;
- Commit complete
- Executed in 6.985 seconds
- SQL> select * from xiaotg.testcf where id = 80002;
- ID NAME
- --------------------------------- --------------------------------------------------------------------------------
- 80002 xiaotg again he he
- Executed in 0 seconds
- SQL> col id for a10;
- SQL> col name for a40;
- SQL> select * from xiaotg.testcf where id = 80002;
- ID NAME
- ---------- ----------------------------------------
- 80002 xiaotg again he he
- Executed in 0.016 seconds
- SQL> select * from xiaotg.mv_testcf2 where id = 80002;
- ID NAME
- ---------- ----------------------------------------
- 80002 xiaotg again he he
- Executed in 0.031 seconds
SQL> set timing on; SQL> insert into xiaotg.testcf ( id, name ) values ( 80002, ' xiaotg again he he '); 1 row inserted Executed in 0.015 seconds SQL> commit; Commit complete Executed in 6.985 seconds SQL> select * from xiaotg.testcf where id = 80002; ID NAME --------------------------------- -------------------------------------------------------------------------------- 80002 xiaotg again he he Executed in 0 seconds SQL> col id for a10; SQL> col name for a40; SQL> select * from xiaotg.testcf where id = 80002; ID NAME ---------- ---------------------------------------- 80002 xiaotg again he he Executed in 0.016 seconds SQL> select * from xiaotg.mv_testcf2 where id = 80002; ID NAME ---------- ---------------------------------------- 80002 xiaotg again he he Executed in 0.031 seconds
测试基表正常情况下的COMMIT速度
发表评论
-
根据多年经验整理的《互联网MySQL开发规范》
2015-08-10 16:09 749写在前面:无规矩不成方圆。对于刚加入互联网的朋友们,肯定会接 ... -
Mysql一主多从和读写分离配置简记
2015-05-15 10:56 1390近期开发的系统中使用MySql作为数据库,由于数据涉及到Mo ... -
MySQL第二天早上第一次连接超时报错,解决方法
2015-02-03 15:36 688| 浏览:44 | 更新:2014- ... -
MYSQL 设置 FOR UPDATE 超时时间
2015-01-21 10:37 2511show global variables like &q ... -
如何设计动态(不定)字段的产品数据库表?--淘宝多产品属性字段设计方法
2014-07-17 13:46 1962项目组会议上讨论的关于不定字段数目的数据库表问 ... -
要想学好SQL语句必会的50题,也是考试常考的点
2014-05-28 10:46 579-- 一、创建教学系统的数据库,表,以及数据 ... -
关于MYSQL group by 分组按时间取最大值的实现方法!
2013-02-06 08:22 1138类如 有一个帖子的回复表,posts( id , tid , ... -
ORACLE之游标实战
2012-12-07 08:45 1161这是一个忙碌的季节,也是一个收获颇多的季节。在忙碌工作的同时, ... -
全国行政区域数据库(地市数据库)
2012-10-28 13:48 1040创建表格: -- Create table creat ... -
oracle 数据库字段按照拼音首字母排序
2012-10-27 13:00 14521、创建oracle存储过程(网上拿来的的) Sql ... -
ORACALE查询数据库按汉字拼音排序
2012-10-27 13:03 1220CREATE OR REPLACE FUNCTION PUB_ ... -
ORA-12519,TNS:no appropriate service handler found的问题
2012-10-18 15:47 7652Java代码 ORA-12519, TNS:n ... -
dbcp的基本配置
2012-10-18 15:41 9891. 引入dbcp (选择1.4)Java代码 <dep ... -
in和exists区别
2012-10-15 09:21 1195引子 in和exists的讨论从未间断过。之前有“今年是 ... -
ORACLE-分区表分区字段的update操作
2012-10-16 09:44 10808默认情况下,oracle的分区表对于分区字段是不允许进行u ... -
oracle分区表的建立方法(包含已经存在的表要分区)
2012-10-13 10:22 1527Oracle提供了分区技术以支持VLDB(Very L ... -
查询Oracle表实际物理使用大小
2012-10-13 09:54 1078Oracle中有两种含义的表大小一种是分配给一个表的物理空 ... -
Oracle tablespace (表空间)的创建、删除、修改、扩展及检查等
2012-10-13 09:39 1441oracle 数据库表空间的作用 1.决定数据库实体的空 ... -
Oracle异常ORA-01502: 索引或这类索引的分区处于不可用状态
2012-10-13 09:25 8005原因: 出现这个问题,可能有人move过表,或者disab ... -
Oracle中分区表的使用
2012-10-13 08:53 705前提: 查询分区:Select *From use ...
相关推荐
Oracle物化视图使用[文].pdf
Oracle物化视图创建和使用,设置物化视图的自动刷新时间
物化视图 (MV)在一个段中存储查询结果,并且能够在提交查询时将结果返回给用户,从而不再需要重新执行查询 — 在查询要执行几次时,这是一个很大的好处。物化视图可以利用一个快速刷新机制从基础表中全部或增量刷新...
Oracle物化视图应用详,有实例和各属性介绍
Oracle物化视图增量刷新的应用研究.pdf
里面详细讲述物化视图的种种创建,物化日志的结构,group by 语句创建物化视图的处理
利用ORACLE物化视图建立报表数据库.pdf
ORACLE9I物化视图
物化视图学习笔记 错误问题分析总结 详细讲解了物化视图的内部构造,从零基础开始学习 重点说明ORA-12034的错误解决方案
Oracle物化视图详细介绍
物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。
ORACLE使用物化视图和查询重写功能
oracle物化视图资料,表明物化视图的创建等
Oracle物化视图整理,包含所有资料,值得学习
Oracle物化视图介绍 根据学习总结并将多篇文章结合而成。
Oracle中物化视图很重要,物化视图优秀论文. 在数据仓库中物化视图是非常重要的。Oracle中物化视图很重要,物化视图优秀论文. 在数据仓库中物化视图是非常重要的。Oracle中物化视图很重要,物化视图优秀论文. 在数据...
ORACLE 中的物化视图在大量的报表查询中使用平凡,本文介绍了物化视图的原理和使用方法,觉得不错。
基于Oracle物化视图日志的数据同步技术研究.pdf
Oracle怎么根据物化视图日志快速刷新物化视图