物化视图普通视图区别.doc

物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。1、物化视图的类型：ON DEMAND、ON COMMIT 二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。2、ON DEMAND物化视图 物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。 物化视图的特点： (1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证； (2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性； (3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证； 创建语句：create materialized view mv_name as select * from table_name 默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。 物化视图的数据怎么随着基表而更新？ Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。 ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，只要基表发生了COMMIT。 创建定时刷新的物化视图：create materialized view mv_name refresh force on demand start with sysdate next sysdate+1 （指定物化视图每天刷新一次） 上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每天晚上10：00定时刷新一次）：create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,dd-mm-yyyy), 22:00:00),dd-mm-yyyy hh24:mi:ss)3、ON COMMIT物化视图 ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。 需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行。 创建ON COMMIT物化视图：create materialized view mv_name refresh force on commit as select * from table_name 备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）4、物化视图的刷新 刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。（如上所述） 刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。 对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次：alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,dd-mm-yyyy), 22:00:00),dd-mm-yyyy hh24:mi:ss) 5、物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。6、物化视图的删除： 虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除（在表上右键选择drop并不能删除物化视图），可以使用语句来实现：drop materialized view mv_name物化视图的一篇blog/14969361/viewspace-541299最近在做一个BI项目，BI项目，就一定会涉及到数据仓库。数据仓库和生产数据库一个很重要的区别就是：生产数据库存放的是当前的实时的数据，而数据仓库存放的是历史数据。这就会引发一个很重要的问题：如何保持生产数据库和数据仓库的同步问题。现实中有多种解决的办法，不过由于刚接触这方面的东西，目前只了解到有2种方法。一种是通过ETL工具创建映射和计划来定期的刷新数据，另一种办法就是物化视图。正在做的这个项目中，这两种方法都用到了（不知道还有没有用到别的方法）。在项目中，我本身没有用过物化视图，不过在业余的时间里，学习了一下这方面的东西，这里做一个笔记以备以后查询。刚接触物化视图这个概念的时候，存在以下三个问题：1、 什么是物化视图？2、 它有什么用？3、 怎么操作它？下面的内容，我将视图回答以上三个问题，重点在于如何操作物化视图。1、什么是物化视图，以及它有什么用 物化视图是数据库中的一种存储数据的对象，和表一样，存储了数据。用于预先计算并保存表连接或者聚集等耗时较多的操作。这样，在执行查询的时候，就可以避开连接、聚集等耗时的操作，从而快速的得到结果。它可以查询表，视图甚至是其他物化视图中的数据。和视图不同的是，它存储了实实在在的数据，而视图只是存储了定义而已，所以，也是可以在物化视图上创建索引的。物化视图在很多方面，和索引很相似：使用它的主要目的是为了提高查询性能；物化视图对应用是透明的，增加或者删除物化视图不会影响应用程序中查询语句的正确性和有效性；物化视图会占用存储空间；当基表发生变化时，物化视图也应当刷新。通常情况下，物化视图被称为主表（在复制期间）或者明细表（在数据仓库中）。对于数据仓库这样的数据库来说，由于生产系统中存储了大量的明细和实时的数据，因此，在数据仓库中使用物化视图是非常必要和重要的。2、操作物化视图 创建物化视图的用户需要一下权限： CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE VIEW, SELECT，如果需要在其他的模式下创建，需要在表上有CREATE ANY MATERIALIZED VIEW和SLECT权限。如果是要查询引用别的模式中的物化视图，需要有引用的表的GLOBAL QUERY REWRITE OR QUERY REWRITE权限。如果打算使用物化视图，还需要修改或者添加参数文件中QUERY_REWRITE_ENABLED选项，值为TRUE。2.1、创建物化视图 CREATE MATERIALIZED VIEW MV_NAME ON PREBUILT TABLETABLESPACE TABLESPACE_NAME BUILD IMMEDIATE | DEFERRED REFRESH FAST | COMPLETE | FORCE ON DEMAND | COMMIT START WITH DATE NEXT DATE WITH PRIMARY KEY | ROWID ENABLE QUERY REWRITE | DISABLE QUERY REWRITE AS SELECT CLAUSE ;以上是创建物化视图的比较完整命令，其中的 部分均是可选的。下面分别对它们进行说明。MV_NAME : 是物化视图的名称一般是XXX_MV形式的；ON PREBUILT TABLE : 将已经存在的表注册为物化视图。同时还需要提供描述创建该表的查询的SELECT子句。对于这个语句，知道的并不多。TABLESPACE : 指定物化视图存放的表空间。BUILD 子句 : 指定何时生成数据。IMMEDIATE指定在创建物化视图时就生成数据；DEFERRED指定在创建时并不生成数据，以后根据需要生成数据。默认为IMMEDIATE选项。REFRESH 子句 : 指定当基表的数据发生变化时，物化视图何时以何种方式和基表进行同步。FAST指定增量刷新方式，也就是只刷新自上次刷新后被修改的数据；COMPLETE 指定对整个物化视图进行完全刷新；FORCE是默认选项，指定在刷新数据时，先判断是否能够使用FAST方式进行刷新，如果可行，则采用FAST方式刷新，如果不可行，则使用COMPLETE方式刷新。ON DEMAND指定物化视图在用户需要的时候进行刷新，可以手工刷新，通过使用DBMS_MVIEW.REFRESH等方式刷新，也可以通过JOB方式进行定时刷新，可以到表USER_JOBS中查看JOB的信息；ON COMMIT指定物化视图在基表的DML操作提交的同时进行刷新。START WITH指定第一次刷新的时间；NEXT指定刷新的时间间隔。WITH PRIMARY KEY: 指定生成主键物化视图，也就是物化视图是基于表的主键，而不是ROWID(对应于ROWID选项)。如果要使用WITH PRIMARYKEY选项，基表上应该定义了主键，否则只能使用ROWID选项。WITH PRIMARY是默认选项。RowID物化视图只有一个单一的主表，不能包括下面任何一项：Distinct 或者聚合函数；Group by，子句，连接和Set操作。ENABLE/DISABLE QUERY REWIRTE : 指定是否支持查询重写，默认不支持。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。SELECT 子句 : 从基表取数据的查询语句，和普通的查询语句没有区别。这里需要特别注意的是，如果需要进行快速刷新，则需要创建物化视图日志。物化视图日志的详细内容见“物化视图日志”一节。 为了练习，我创建了一张表：CREATE TABLE tax (tax_id NUMBER, region VARCHAR2(30), tax_date DATE, tax NUMBER, PRIMARY KEY (tax_id);插入了一下数据：839,天津,2008-3-1,12038.48840,威海,2008-3-1,15310.32841,温州,2008-3-1,459432.9842,信阳,2008-5-1,1574.73843,徐州,2008-4-1,401.54844,烟台,2008-8-1,17869.17845,盐城,2008-3-1,1156.73846,义乌,2008-3-1,988.22下面我将先创建物化视图日志：CREATE MATERIALIZED VIEW LOG ON tax TABLESPACE usersWITH ROWID;然后，我创建物化视图tax_mv：CREATE MATERIALIZED VIEW tax_mv TABLESPACE users BUILD IMMEDIATE REFRESH FAST ON DEMAND START WITH trunc(SYSDATE) + 1 NEXT trunc(SYSDATE) + 1 WITH ROWID DISABLE QUERY REWRITE AS SELECT t.tax_id, t.region, t.tax_date, t.tax FROM tax t;以上两条SQL语句，第一条，以ROWID创建表tax的物化视图日志；第二条，基于ROWID创建物化视图，该物化视图的数据需要马上生成，采用根据用户需要而进行增量刷新的方式同步数据，开始刷新的时间为系统当期时间的第二天，每天刷新一次，并且不支持查询重写。现在查询物化视图TAX_MV中的数据，和表TAX中的完全一致，因为我们采取了IMMEDIATE的生成数据方式。2.2、刷新物化视图现在，我们向表TAX中，插入以下数据: INSERTINTOtax VALUES(847,营口,to_date(2008-3-1,yyyy-mm-dd),219.48);此时，查询表tax中的数据，自然是多了一条新数据，但是去查询tax_mv中的数据时，并没有新插入的数据，因为数据并没有同步到物化视图中，这得需要下次刷新数据的时候，才能同步过来。不过我们现在可以手工的来同步数据。执行以下任一程序语句：SQL Window:BEGINdbms_mview.refresh(tax_mv,C);END;COMMAND Window:EXECUTEdbms_mview.refresh(tax_mv,C);第一个参数是物化视图名称，第二个参数指明刷新方式，C-COMPLETE, F-FAST，?-FORCE。这就手工刷新了物化视图中的数据，去查找物化视图中的数据，已经和基表中的数据同步了。实际上，oracle提供了两个包：DBMS_MVIEW、DBMS_REFRESH、DBMS_OLAP，用于对物化视图进行管理。这三个包我还没有去好好的学习，大概知道：DBMS_MVIEW用于管理物化视图；DBMS_REFRESH用于管理物化视图的刷新；DBMS_OLAP用于确定物化视图是否能提高查询的性能。2.3、删除物化视图删除物化视图很简单，执行如下SQL语句：DROPMATERIALIZEDVIEWtax_mv;2.4、管理物化视图前面我们说过，oracle提供了DBMS_MVIEW、DBMS_REFRESH、DBMS_OLAP三个包来管理物化视图。DBMS_MVIEW:常用的有以下4个：Dbms_mview.refresh(list IN VARCHAR2, method IN VARCHAR2, )List :指定需要刷新的物化视图，可以是一张，也可以是以逗号分隔的列表；Method:指定刷新方式，F-FAST，C-COMPLETE，?-FORCE。不指定刷新方式，会使用它的默认刷新方式，默认刷新方式是存放在数据字典里面的。Dbms_mivew.refresh_all;执行所有待执行的刷新任务，需要ALTER ANY SNAPSHOT权限。Dbms_mview.explain_mview(mv in VARCHAR2, stmt_id IN VARCHAR2 := null)用于陈述一个物化视图的能力和性能的。参数mv可以是一个schema.mvname的视图名称，也可以是一句欲用于创建mv的select语句。结果将会被保存在MV_CAPABILITIES_TABLE中，如果没有这张表，需要预先调用ORACLE_HOMERDBMSADMINutlxmu.sql脚本来创建此表。此脚本中，对表中的各字段有较详细的解释，这里不赘述。针对我前面创建的视图TAX_MV，在COMMAND WINDOW下面执行了下列语句：exec dbms_mview.explain_mview(tax_mv);提示执行完成。但是令我意外的是，表MV_CAPABILITIES_TABLE什么都没有。这个问题留待解决。Dbms_mview.explain_rewrite(query in varchar2, mv in varchar2 :=null, msg_arrag IN OUT SYS.REWRITEARRAYTYPE);陈述关于mv的查询重写方面的信息。同explain_mview一样，结果被保存在表REWRITE_TABLE中，通过执行脚本ORACLE_HOMERDBMSADMINutlxrw.sql来创建此表。DBMS_REFRESH物化视图是可以嵌套的。也就是指一个物化视图可以被放在另一个物化视图的select子句里面。在物化视图相互嵌套的时候，由于各个物化视图的定义不一致，或许由于刷新方式和刷新时间的不同，会引起数据的完整性问题，对于这种情况，可以考虑把管理的物化视图放在同一个刷新组中。DBMS_REFRESH就是专门为刷新组而来的。USER_REFRESH USER_JOBSDbms_refresh.make(name in varchar2, list in varchar2, next_date in date, interval in varchar2.)这是创建一个刷新组，name是刷新组的名称，list是以都好分隔的物化视图名词，next_date是第一次刷新时间，interval是刷新间隔。省略号为更多的默认参数。BEGIN dbms_refresh.make(test_refresh_group,tax_mv3,SYSDATE,sysdate+1);END;上语句创建了一个新的刷新组，刷新间隔为1天。执行这个语句，结果会报错：ORA-23410:实体化视图tax_mv3已在刷新组中，查了些资料，原来是在创建物化视图的时候，会自动创建一个刷新组，名称和物化视图名称一样。这可以从表user_refresh可以查看到结果。解决办法就是，先从默认的刷新组中移除这个物化视图，再执行上面的语句，也就是先执行下面的语句： BEGIN dbms_refresh.subtract(name=TAX_MV3,list=TAX_MV3); END;这就是从刷新组TAX_MV3从移除物化视图TAX_MV3。Dbms_refresh.add(name in varhcar2, list in varchar2)省略号为更多的默认参数。此语句添加一个可刷新对象到刷新组中。BEGIN dbms_refresh.add(test_refresh_group,tax_mv1);END;Dbms_refresh.subtract(name in varchar2, list in varchar2)省略号为更多的默认参数。此语句从刷新组中清除指定的物化可刷新对象。BEGIN dbms_refresh.subtract(test_refresh_group,tax_mv3);END;Dbms_refresh.refresh(name in varhcar2)刷新制定的刷新组。BEGIN dbms_refresh.refresh(test_refresh_group);END;Dbms_refresh.destroy(name in varchar2)删除指定的刷新组。BEGIN dbms_refresh.destroy(test_refresh_group);END;2.5、物化视图的日志前面已经简单介绍了物化视图日志在某些情况下的