物化视图是一种特殊的物理表物化(Materialized)视图是相对普通视图而言的普通视图是虚拟表应用的局限性大任何对视图的查询Oracle都实际上转换为视图SQL语句的查询这样对整体查询性能的提高并没有实质上的好处
Oracle最早在OLAP系统中引入了物化视图的概念但后来很多大型OLTP系统中发现类似统计的查询是无可避免而这些查询操作如果很频繁对整体数据库性能是很致命的于是Oracle开始不断的改进物化视图使得其也开始合适OLTP系统从Oracle i到现在功能已经相对比较完备了
本文是Oracle物化视图系列文章的第一篇有两个主要目的来体验一下创建ON DEMAND和ON COMMIT物化视图的方法ON DEMAND和ON COMMIT物化视图的区别在于其刷新方法的不同ON DEMAND顾名思义仅在该物化视图需要被刷新了才进行刷新(REFRESH)即更新物化视图以保证和基表数据的一致性;而ON COMMIT是说一旦基表有了COMMIT即事务提交则立刻刷新立刻更新物化视图使得数据和基表一致
第一个ON DEMAND物化视图
创建ON DEMAND物化视图
下面创建一个最简单的物化视图这个物化视图的定义很类似于普通视图的创建语句只是多了一个materialized但就是这个单词造成了物化视图和普通视图(虚拟表)的天壤之别也引申出后面很多的事情呵呵
本例中需要特别注意的是Oracle给物化视图的重要定义参数的默认值处理在下面的例子中会有特别说明因为物化视图的创建本身是很复杂和需要优化参数设置的特别是针对大型生产数据库系统而言但Oracle允许以这种最简单的类似于普通视图的办法来做所以不可避免的会涉及到默认值问题
像我们这样创建物化视图时未作指定则Oracle按ON DEMAND模式来创建
从下例中可以看出
) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)这通过其可以被user_tables查询出来而得到佐证;
) 物化视图也是一种段(segment)所以其有自己的物理存储属性;
) 物化视图会占用数据库磁盘空间这点从user_segment的查询结果可以得到佐证
● 创建物化视图
获取数据库rdbms版本信息
SQL>select*fromv$version;
BANNER
OracleDatabasegEnterpriseEditionReleaseProduction
PL/SQLReleaseProduction
COREProduction
TNSforbitWindows:VersionProduction
NLSRTLVersion–Production
创建物化视图
SQL>creatematerializedviewmv_testcf
as
select*fromxiaotgtestcf;
Materializedviewcreated
分析物化视图以获得统计信息
SQL>analyzetablexiaotgmv_testcfcomputestatistics;
Tableanalyzed
查看物化视图的行数发现和master表(TESTCF)一样
SQL>selecttltable_nametlnum_rowsfromuser_tablestlwheretltable_namein(TESTCFMV_TESTCF);
TABLE_NAMENUM_ROWS
MV_TESTCF
TESTCF
查看物化视图的存储参数
SQL>colsegment_namefora
SQL>selectsgsegment_namesgbytessgblocksfromuser_segmentssgwheresgsegment_name=MV_TESTCF;
SEGMENT_NAMEBYTESBLOCKS
MV_TESTCF
● 查看物化视图关键定义
查看物化视图的定义设置请关注蓝色字体部分
这表明默认情况下如果没指定刷新方法和刷新模式则Oracle默认为FORCE和DEMAND
其他的集中刷新方法和刷新模式以后将分别予以介绍
SQL> select mv* from user_mviews mv where mvMVIEW_NAME = MV_TESTCF;
(为增加查询结果的可读性下面进行了行列的互转)
OWNERXIAOTG
MVIEW_NAMEMV_TESTCF
CONTAINER_NAMEMV_TESTCF
QUERY
QUERY_LEN
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_DATE:
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_TABLES
NUM_FRESH_PCT_REGIONS
NUM_STALE_PCT_REGIONS
测试ON DEMAND物化视图的更新特性
物化视图最重要的功能和特性之一就是其数据会随着基表(或称主表master表本例中为TESTCF)的变化而变基表数据增了物化视图数据会变多;基表数据删了物化视图数据也会变少
但怎么更新?或者说物化视图的数据怎么随着基表而更新?Oracle提供了两种方式手工刷新和自动刷新像我们这种在物化视图定义时未作任何指定那当然是默认的手工刷新了也就是说通过我们手工的执行某个Oracle提供的系统级存储过程或包来保证物化视图与基表数据一致性
这是最基本的刷新办法了但所谓的自动刷新其实也就是Oracle会建立一个job通过这个job来调用相同的存储过程或包加以实现这在本系列文章的第篇会将以详细阐述
下面将测试INSERTUPDATE和DELETE的测试方法类似大家有兴趣的话可以自己试一试
需要注意的是下面暂不讨论如何刷新ON DEMAND物化视图这是下一篇文章的内容下面仅仅关注ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别即前者不刷新(手工或自动)就不更新物化视图而后者不刷新也会更新物化视图——只要基表发生了COMMIT
● 在基表插入测试数据
基表数据插入后会发现物化视图并不会随之更新
检查基表和物化视图是否有这一行记录
SQL>colidfora;
SQL>colnamefora;
SQL>select*fromxiaotgtestcftwheretid=;
IDNAME
SQL>select*fromxiaotgmv_testcftwheretid=;
IDNAME
插入测试数据
这时发现基表有数据但物化视图并没有
SQL>insertintoxiaotgtestcf
values(xiaotghehe);
rowinserted
SQL>commit;
Commitcomplete
测试物化视图数据是否更新
从下面的实验可以看出物化视图数据不会更新即使等上分钟小时或者天
关于如何使得ON DEMAND物化视图数据被更新参加本系列的第二篇文章哈)
SQL>select*fromxiaotgtestcftwheretid=;
IDNAME
xiaotghehe
SQL>select*fromxiaotgmv_testcftwheretid=;
IDNAME
SQL>
第一个ON COMMIT物化视图
最简单的ON COMMIT物化视图的创建和上面创建ON DEMAND的物化视图区别不大因为ON DEMAND是默认的所以ON COMMIT物化视图需要再增加个参数即可
创建ON COMMIT物化视图
● 创建物化视图
需要注意的是无法在定义时仅指定ON COMMIT还得附带个参数才行本例中附带refresh force关于这个参数的意思以后将加以阐述
创建ON COMMIT物化视图
SQL>creatematerializedviewmv_testcf
refreshforceoncommit
as
select*fromxiaotgtestcf;
Materializedviewcreated
SQL>
分析物化视图和基表
SQL>analyzetablexiaotgmv_testcfcomputestatistics;
Tableanalyzed
SQL>analyzetablexiaotgtestcfcomputestatistics;
Tableanalyzed
查看当前基表和物化视图的行数
SQL>selecttltable_nametlnum_rowsfromuser_tablestlwheretltable_namein(TESTCFMV_TESTCF);
TABLE_NAMENUM_ROWS
MV_TESTCF
TESTCF
● 查看物化视图关键定义
可以从DBA_MVIEWS中看出刷新模式为COMMIT这也是它和上面ON DEMAND物化视图的唯一区别
SQL>selectmv*fromuser_mviewsmvwheremvMVIEW_NAME=MV_TESTCF;
(为增加查询结果的可读性下面进行了行列的互转且只显示前三个关键列的)
REFRESH_MODECOMMIT
REFRESH_METHODFORCE
BUILD_MODEIMMEDIATE
测试ON COMMIT物化视图的更新特性
ON COMMIT物化视图会在基表一旦提交时就会立刻更新物化视图本身而且一般仅在物化视图数据也被更新后基表数据才会事实的提交
这意味着这种模式可能会导致延迟基表数据的提交这点在下面的实验中体现得很清楚
实验中对基表TESTCF平常的COMMIT在秒内可以完成但在有了ON COMMIT视图MV_TESTCF后居然要秒速度减低了很多倍ON COMMIT视图对基表的影响可见一斑
● 在基表中插入数据
SQL>settimingon;
SQL>insertintoxiaotgtestcf(idname)values(xiaotgagainhehe);
rowinserted
Executedinseconds
SQL>commit;
Commitcomplete
Executedinseconds
SQL>select*fromxiaotgtestcfwhereid=;
IDNAME
xiaotgagainhehe
Executedinseconds
SQL>colidfora;
SQL>colnamefora;
SQL>select*fromxiaotgtestcfwhereid=;
IDNAME
xiaotgagainhehe
Executedinseconds
SQL>select*fromxiaotgmv_testcfwhereid=;
IDNAME
xiaotgagainhehe
Executedinseconds
● 测试基表正常情况下的COMMIT速度
SQL>dropmaterializedviewmv_testcf;
Materializedviewdropped
Executedinseconds
SQL>
SQL>
SQL>insertintoxiaotgtestcf(idname)values(xiaotgagainhehe);
rowinserted
Executedinseconds
SQL>commit;
Commitcomplete
Executedinseconds
