Oracle物化视图

Oracle物化视图：创建最简单物化视图 2008年09月19日社区交流关键字：触发器语法Oracle物化视图跟踪sql语句密码丢失数据仓储Oracle 9i个人版内容摘要：物化视图是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，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

1、第一个ON DEMAND物化视图1.1、创建ON DEMAND物化视图下面创建一个最简单的物化视图，这个物化视图的定义很类似于普通视图的创建语句，只是多了一个materialized，但就是这个单词，造成了物化视图和普通视图(虚拟表)的天壤之别，也引申出后面很多的事情，呵呵。

本例中需要特别注意的是，Oracle给物化视图的重要定义参数的默认值处理，在下面的例子中会有特别说明。

因为物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。

但Oracle允许以这种最简单的，类似于普通视图的办法来做，所以不可避免的会涉及到默认值问题。

像我们这样，创建物化视图时未作指定，则Oracle按ON DEMAND模式来创建。

从下例中可以看出：1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证;2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性;3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证。

创建物化视图--获取数据库rdbms版本信息SQL>select*fromv$version;BANNER--------------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.1.0.6.0-ProductionPL/SQLRelease11.1.0.6.0-ProductionCORE11.1.0.6.0ProductionTNSfor32-bitWindows:Version11.1.0.6.0-ProductionNLSRTLVersion11.1.0.6.0–Production--创建物化视图SQL>creatematerializedviewmv_testcf2as3select*fromxiaotg.testcf;Materializedviewcreated--分析物化视图，以获得统计信息SQL>analyzetablexiaotg.mv_testcfcomputestatistics;Tableanalyzed--查看物化视图的行数，发现和master表(TESTCF)一样SQL>selecttl.table_name,tl.num_rowsfromuser_tablestlwheretl.table_namein('TESTCF','MV_TEST CF');TABLE_NAMENUM_ROWS----------------------------------------MV_TESTCF80000TESTCF80000--查看物化视图的存储参数SQL>colsegment_namefora24SQL>selectsg.segment_name,sg.bytes,sg.blocksfromuser_segmentssgwheresg.segment_name=' MV_TESTCF';SEGMENT_NAMEBYTESBLOCKS--------------------------------------------MV_TESTCF94371841152查看物化视图关键定义--查看物化视图的定义设置，请关注蓝色字体部分。

--这表明，默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。

--其他的集中刷新方法和刷新模式以后将分别予以介绍。

SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'MV_TESTCF';(为增加查询结果的可读性，下面进行了行列的互转)OWNERXIAOTGMVIEW_NAMEMV_TESTCFCONTAINER_NAMEMV_TESTCFQUERYQUERY_LEN80UPDATABLENUPDATE_LOGMASTER_ROLLBACK_SEGMASTER_LINKREWRITE_ENABLEDNREWRITE_CAPABILITYGENERALREFRESH_MODEDEMANDREFRESH_METHODFORCEBUILD_MODEIMMEDIATEFAST_REFRESHABLEDMLLAST_REFRESH_TYPECOMPLETELAST_REFRESH_DATE2008-9-915:02STALENESSFRESHAFTER_FAST_REFRESHFRESHUNKNOWN_PREBUILTNUNKNOWN_PLSQL_FUNCNUNKNOWN_EXTERNAL_TABLENUNKNOWN_CONSIDER_FRESHNUNKNOWN_IMPORTNUNKNOWN_TRUSTED_FDNCOMPILE_STATEVALIDUSE_NO_INDEXNSTALE_SINCENUM_PCT_TABLES0NUM_FRESH_PCT_REGIONSNUM_STALE_PCT_REGIONS1.2、测试ON DEMAND物化视图的更新特性物化视图最重要的功能和特性之一，就是其数据会随着基表(或称主表，master表，本例中为TESTCF)的变化而变，基表数据增了，物化视图数据会变多;基表数据删了，物化视图数据也会变少。

但怎么更新?或者说物化视图的数据怎么随着基表而更新?Oracle提供了两种方式，手工刷新和自动刷新，像我们这种，在物化视图定义时，未作任何指定，那当然是默认的手工刷新了。

也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。

这是最基本的刷新办法了。

但所谓的自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现，这在本系列文章的第2篇会将以详细阐述。

下面将测试INSERT，UPDATE和DELETE的测试方法类似，大家有兴趣的话，可以自己试一试。

需要注意的是，下面暂不讨论如何刷新ON DEMAND物化视图，这是下一篇文章的内容。

下面仅仅关注ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。

在基表插入测试数据基表数据插入后，会发现，物化视图并不会随之更新。

--检查基表和物化视图是否有80001这一行记录。

SQL>colidfora10;SQL>colnamefora30;SQL>select*fromxiaotg.testcftwheret.id=80001;IDNAME----------------------------------------SQL>select*fromxiaotg.mv_testcftwheret.id=80001;IDNAME------------------------------------------插入测试数据80001--这时发现，基表有数据，但物化视图并没有SQL>insertintoxiaotg.testcf2values(80001,'xiaotghehe');1rowinsertedSQL>commit;Commitcomplete1.2.2 测试物化视图数据是否更新从下面的实验可以看出，物化视图数据不会更新，即使等上1分钟、1小时、或者1天。

关于如何使得ON DEMAND物化视图数据被更新，参加本系列的第二篇文章哈：)SQL>select*fromxiaotg.testcftwheret.id=80001;IDNAME----------------------------------------80001xiaotgheheSQL>select*fromxiaotg.mv_testcftwheret.id=80001;IDNAME----------------------------------------SQL>2、第一个ON COMMIT物化视图最简单的ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。

因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。

2.1 创建ON COMMIT物化视图创建物化视图需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行，本例中附带refresh force，关于这个参数的意思，以后将加以阐述。

--创建ON COMMIT物化视图SQL>creatematerializedviewmv_testcf22refreshforceoncommit3as4select*fromxiaotg.testcf;MaterializedviewcreatedSQL>--分析物化视图和基表SQL>analyzetablexiaotg.mv_testcf2computestatistics;TableanalyzedSQL>analyzetablexiaotg.testcfcomputestatistics;Tableanalyzed--查看当前基表和物化视图的行数SQL>selecttl.table_name,tl.num_rowsfromuser_tablestlwheretl.table_namein('TESTCF','MV_TEST CF2');TABLE_NAMENUM_ROWS----------------------------------------MV_TESTCF280000TESTCF80000查看物化视图关键定义可以从DBA_MVIEWS中看出，刷新模式为COMMIT，这也是它和上面ON DEMAND 物化视图的唯一区别。