ORACLE物化视图 - 360文档中心

Oracle创建物化视图

Oracle创建物化视图1.物化视图语法create materialized view [view_name]refresh [fast|complete|force][on [commit|demand] |start with (start_time) next (next_time)]as{创建物化视图⽤的查询语句}以上是Oracle创建物化视图（Materialized View,以下简称MV）时的常⽤语法,各参数的含义如下:1.refresh [fast|complete|force] 视图刷新的⽅式:fast: 增量刷新.假设前⼀次刷新的时间为t1,那么使⽤fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化，建⽴增量刷新物化视图还需要⼀个物化视图⽇志表。

create materialized view log on （主表名）。

（多张表时，此语句也⽣效，创建后，原来的表中会多出两类视图表：MLOG$_table_name和RUPD$_table_name）complete:全部刷新。

相当于重新执⾏⼀次创建视图的查询语句。

force: 这是默认的数据刷新⽅式。

当可以使⽤fast模式时，数据刷新将采⽤fast⽅式；否则使⽤complete⽅式。

2. 建⽴基表的物化视图⽇志-- tablename 为基表 with后⾯可以接主键,rowid primary key是主键，rowid是表更新涉及的⾏号，sequence是序列对，⾃由添加。

--including new values必须包含create materialized view log on tablename with primary key,rowid,sequence (AREA_NM_R, AREA_NM_N) including new values;3. 赋予主表的权限给建⽴视图的⽤户grant select on tabelname to A;4.⽰例--1. 建⽴基表的物化视图⽇志create materialized view log on auth_role with rowid, sequence (role_id, role_ad, bpm_group, role_name, role_enable, role_type, order_num) including new values ; --2. 授权grant select on sys_role to auth;--3. 创建物化视图create materialized view viewnamerefresh force on demandstart with SYSDATE next SYSDATE + NUMTODSINTERVAL(2,'MINUTE')asselect role_id, role_ad, bpm_group, role_name, role_enable, role_type, order_num, 'auth' sys_code from auth_roleunion allselect role_id, role_ad, bpm_group, role_name, role_enable, role_type, order_num, 'bpm' sys_code from cfcap.sys_roleunion allselect role_id, role_id role_ad, role_id bpm_group, role_name,to_number(ROLE_STAT) role_enable,to_char(role_type)||'' role_type,0 order_num, 'wbs' sys_code from forms.ts_role;。

Oracle物化视图定时全量刷新导致归档日志骤增

Oracle物化视图定时全量刷新导致归档⽇志骤增⼀、问题描述某项⽬组来电，说有⼀个源表约2万多条的物化视图，每5分钟定时全量(Complete)刷新⼀次，⼀天下来，导致Oracle数据库归档⽇志骤增。

⼆、问题分析及解决先明确⼀个问题：归档⽇志(Archive Log)和重做⽇志(REDO Log)的关系。

Oracle的重做⽇志是⼀组（或⼏组）⽂件，按⼀定的规则顺序循环写，当重做⽇志写满后，从头开始写之前，如果数据库在归档模式(Archive)，则在重写之前，需要把当前的重做⽇志进⾏归档(Archive)，形成归档⽇志。

即归档⽇志来⾃于重做⽇志。

基于此，可以通过减少产⽣重做⽇志的量来达到减少归档⽇志量的⽬的。

综合⼀下： 1、不要全量刷新，采⽤在源表上记录物化视图⽇志的⽅式，实现快速刷新，减少更新的数据量，达到减少重做⽇志的⽬的； 2、指定物化视图为nologging模式 3、减少或取消其上的索引（2W条记录，如果使⽤得⽐较频繁，甚⾄可以考虑把它cache到内存中） 4、如果⼀定要有索引，⾃⼰写刷新的Job，先disable索引，然后刷新，然后重建索引（唯⼀索引可能有问题）。

5、评估业务、技术要求，考虑取消物化视图，建⽴⼀般视图，在访问该视图时，直接从源表中查询。

三、验证过程验证全量刷新的物化视图产⽣的REDO⽇志的⼤⼩：-- 建⽴源表create table big_table as select * from dba_objects;-- 我机器上（11g），⼤概8W条记录select count(*) from big_table;/*开始验证全量刷新产⽣的REDO⽇志的量*/-- 建⽴物化视图create materialized view big_table_mv as select * from big_table;-- 查看⽬前REDO⽇志的量（重新启动数据库会⾃动清理）-- 记录下数值，⽤于接下来的⽐较select , b.value from v$statname a, v$mystat b where a.statisti = b.statistic# and = 'redo size';--243964-- ⼿⼯全量刷新物化视图begindbms_mview.refresh( 'BIG_TABLE_MV', 'C' );end;-- 再查看REDO⽇志的量，⽐较⼀下-- 记录下数值，⽤于接下来的⽐较select , b.value, to_char( b.value-&V, '999999999999' ) diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and = 'redo size';--value:38845196--diff:38601232，增加了约37M-- 还是⽐较可观的-- 把物化视图改为nologging模式alter table big_table_mv nologging;-- 再全量刷新begindbms_mview.refresh( 'BIG_TABLE_MV', 'C' );end;-- 再查看REDO⽇志的量，⽐较⼀下-- 记录下数值，⽤于接下来的⽐较select , b.value, to_char( b.value-&V, '999999999999' ) diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and = 'redo size';--value:77495608--diff:38894376，增加了约37M，全量刷新时，指定nologging没有什么效果喔。

oracle物化视图及创建索引

oracle物化视图及创建索引物化视图是⼀种特殊的物理表，“物化”(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物化视图详解

物化视图详解物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

物化视图存储基于远程表的数据，也可以称为快照。

对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。

如果你想修改本地副本，必须用高级复制的功能。

当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

物化视图可以查询表，视图和其它的物化视图。

一、关于物化视图日志：查询物化视图日志文件格式：desc mlog$_lzwmvtest;创建物化视图时默认指定物化视图中存在主键，如果不指定，那么创建的物化视图日志文件的基表必须存在主键，否则会报错Demo:对一个表test创建日志：create materialized view log on test;那么会报：表'LZWMVTEST'不包含主键约束条件这种情况下，就必须指定日志文件结构比如：create materialized view log on test with rowid（具体的针对日志内容方面的在另外一个专题里说明，这里就简述到此）二、关于生成数据和刷新：1>生成数据两大选项：build immediate build deferredBuild immediate:在创建物化视图的同时根据主表生成数据Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh(‘MV_name’,’C’)，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。

2>关于刷新²刷新方式：complete fast forceComplete :完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新ØFast:当有数据更新时依照相应的规则对物化视图进行更新（此时必须创建物化视图日志（物化视图日志记录了数据更新的日志），关于日志的说明，参照“物化视图日志文件介绍”）ØForce:当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致fast刷新不可用，有待总结…..）²刷新时间：on demand on commit start with/ nextOn demand:在需要刷新时进行刷新（人工判断）On commit:在基表上有提交操作时，进行更新Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）Next:刷新的周期时间三、基于主键的物化视图和ROWID的物化视图的说明创建物化视图日志时，指定了记录更新的原则即with 后面的primary 或者rowid 或者object id等等，后面，默认是以primary key为记录更新，在物化视图内也是以此为更新的原则。

物化视图解释和刷新

Oracle 物化视图刷新物化视图对于前台数据库使用者来说如同一个实际的表,具有和一般表相同的如select等操作,而其实际上是一个视图,一个由系统实现定期刷新其数据的视图(具体刷新时间在定义物化视图的时候已有定义),使用物化视图更可以实现视图的所有功能,而物化视图却不是在使用时才读取,大大提高了读取速度,特别适用抽取大数据量表某些信息以及数据链连接表使用，但是物化视图占用数据库磁盘空间。

具体语法如下:create materialized view [view_name]refresh [fast|complete|force][on [commit|demand] |start with (start_time) next (next_time)]as{创建物化视图用的查询语句}具体实例如下：CREATE MATERIALIZED VIEW an_user_base_file_no_chargeREFRESH COMPLETE START WITH SYSDATENEXT TRUNC(SYSDATE+29)+5.5/24 --红色部分表示从指定的时间开始，每隔一段时间（由next指定）就刷新一次ASselect distinct user_nofrom cw_arrearage twhere (t.mon = dbms_tianjin.getLastMonth ort.mon = add_months(dbms_tianjin.getLastMonth, -1))删除物化视图：drop materialized view an_user_base_file_no_charge;以上是Oracle创建物化视图（Materialized View,以下简称MV）时的常用语法,各参数的含义如下:1.refresh [fast|complete|force] 视图刷新的方式:fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化，建立增量刷新物化视图还需要一个物化视图日志表。

Oracle物化视图在数据仓库中的应用

Ｏｒａｃｌｅ物化视图在数据仓库中的应用作者：谢任东杨军来源：《电脑知识与技术·学术交流》2008年第12期摘要：在数据仓库的开发中，随着业务数据量的剧增以及其数据量增加的不稳定性，如何使用一种合适的方式来实现ETL(数据的抽取，转换，装载)成为在数据仓库开发中最需要考虑的热点。

本文介绍一种可以实现数据仓库ETL的技术——Oracle物化试图，并以作者在数据仓库开发实践作为例子，具体说明Oracle物化试图的实现方法。

关键词：数据仓库；ETL；物化试图；物化试图日志；增量抽取中图分类号：TP311文献标识码：A 文章编号：1009-3044(2008)12-20000-00Application of Oracle Materialized View in Data WarehouseXIE Ren-dong1, YANG Jun2(1.Beijing Global InfoTech Group,Beijing 100013,China;2.Department of Information Management Engineering ,Jiangxi Toursm & Commerce College,Nanchang 330039,China)Abstract: In the data warehouse development, along with the service data quantity sharp increase the instabilitywhich increases by and its the data quantity, how uses one appropriate way to realize ETL (data extracting, transformation, loading) becomes the hot spot which most needs to consider in the data warehouse development. This article introduced one kind may realize the data warehouseETL technical - Oracle Materialized View, and takes the example by the author in the data warehouse development practice, specifically explained the Oracle Materialized View realization method.Key words: data warehouse; Materialized View; data extracting; transformation; loading1 引言随着信息技术的不断推广和应用，许多企业都已经在使用管理信息系统处理管理事务和日常业务。

oracle物化视图日志结构

oracle物化视图⽇志结构物化视图的快速刷新要求基本必须建⽴物化视图⽇志，这篇⽂章简单描述⼀下物化视图⽇志中各个字段的含义和⽤途。

物化视图⽇志的名称为MLOG$_后⾯跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会⾃动在物化视图⽇志名称后⾯加上数字作为序号。

物化视图⽇志在建⽴时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECT ID⼏种类型，同时还可以指定SEQUENCE或明确指定列名。

上⾯这些情况产⽣的物化视图⽇志的结构都不相同。

任何物化视图都会包括的4列：SNAPTIME$$：⽤于表⽰刷新时间。

DMLTYPE$$：⽤于表⽰DML操作类型，I表⽰INSERT，D表⽰DELETE，U表⽰UPDATE。

OLD_NEW$$：⽤于表⽰这个值是新值还是旧值。

N（EW）表⽰新值，O（LD）表⽰旧值，U表⽰UPDATE操作。

CHANGE_VECTOR$$：表⽰修改⽮量，⽤来表⽰被修改的是哪个或哪⼏个字段。

如果WITH后⾯跟了ROWID，则物化视图⽇志中会包含：M_ROW$$：⽤来存储发⽣变化的记录的ROWID。

如果WITH后⾯跟了PRIMARY KEY，则物化视图⽇志中会包含主键列。

如果WITH后⾯跟了OBJECT ID，则物化视图⽇志中会包含：SYS_NC_OID$：⽤来记录每个变化对象的对象ID。

如果WITH后⾯跟了SEQUENCE，则物化视图⽇⼦中会包含：SEQUENCE$$：给每个操作⼀个SEQUENCE号，从⽽保证刷新时按照顺序进⾏刷新。

如果WITH后⾯跟了⼀个或多个COLUMN名称，则物化视图⽇志中会包含这些列。

下⾯通过例⼦进⾏详细说明：SQL> create table t_rowid (id number, name varchar2(30), num number);表已创建。

SQL> create materialized view log on t_rowid with rowid, sequence (name, num) including new values;实体化视图⽇志已创建。

利用ORACLE物化视图建立报表数据库

第１４卷
第５期
天津职业院校联合学报
ＪｕｎｌｆａｊｏａｉｎｌｎｔｕｅｏｒａｏｎｉＶｃｔａｓｉｔｓＴｉｎｏＩｔ
Ｎｏ．ｏ１１５Ｖ．４Ｍａｖ．２２０１
２１０２年５月
利用ＯＲＣＥ物化视图建立报表数据库ＡＬ
报表数据库与生产数据库实时同步，整复制。同步复制任务是透明的，对最终用户毫无影响，软件完既对开发人员来说应用程序也基本无需改动。不同类型的用户访问不同的服务器，时达到提高整个系统性同能的目的。第三，过渡到数据仓库打下基础。用户通常同时有各种不同的业务系统，而有各种报表数为从
的解决方案。
一
、
物化视图的介绍
物化视图是包括一个查询结果的数据库对像，对于普通视图而言，化视图是一个物理表。物化视相物图也是一种段（ｅｍｅｔ，自己的物理存储属性，且占用数据库磁盘空间。ｓｇｎ）有并
据库，各种报表数据库的信息又有着内在的复杂关系，户希望把各种报表数据统一成一个数据中心。而用采用数据仓库技术是数据应用到一定阶段的必然产物。ＯＲＣＥ物化视图技术提供了建立报表数据库ＡＬ

ORACLE使用物化视图和查询重写功能

使用物化视图和查询重写功能1引言1.1目的在本教程中，您将了解如何利用强大的物化视图和查询重写功能。

所需时间大约2个小时1.2主题本教程包括下列主题：概述情景前提条件为销售历史模式实施模式更改启用查询重写分析物化视图更新和重写功能Oracle 的查询重写功能带有GROUP BY扩展项的增强的重写功能使用重写或错误来控制语句的执行分区与物化视图使用带有 PMARKER 信息的物化视图使用查询重写和部分陈旧的物化视图使用 TUNE_MVIEW 使物化视图快速刷新重置环境总结查看屏幕截图将鼠标置于此图标上以加载和查看本教程的所有屏幕截图.(警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。

）注意：此外,您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图.您可以单击单个屏幕截图将其隐藏.2概述通过使用概要管理(Summary Management) 特性，您可以减轻数据库管理员的工作负荷，这是因为您无需再手动创建概要，而且最终用户也不再必须知道已经定义的概要。

一旦您创建了一个或多个物化视图（它们和概要等效），最终用户就可以查询数据库中的表和视图。

Oracle 服务器中的查询重写机制自动重写SQL查询以使用概要表。

这一机制减少了从查询返回结果的响应时间.数据仓库内的物化视图对于最终用户或数据库应用程序来说是透明的。

Oracle 数据库为您提供用于更先进的重写和刷新机制的增强功能，以及全面的顾问与调整框架.这为您的环境带来了更加优化的物化视图,在占用额外空间最小的情况下提升了性能。

注意：本教程不是物化视图的介绍。

它假定读者对物化视图功能有基本了解。

如果您需要更多关于某些主题的背景信息，请参看Oracle 数据仓库指南。

案例您将使用SALES HISTORY (SH) 示例模式创建、修改并分析物化视图和重写功能。

此研讨会依赖于在默认安装上对SH 模式进行的一些小修改。

Oracle中的物化视图

物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

物化视图存储基于远程表的数据，也可以称为快照。

物化视图可以查询表，视图和其它的物化视图。

通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。

对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。

如果你想修改本地副本，必须用高级复制的功能。

当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。

在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。

1.主键物化视图：下面的语法在远程数据库表emp上创建主键物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_pkREFRESH FAST START W ITH SYSDATENEXT SYSDATE + 1/48WITH PRIMARY KEYAS SELECT * FROM emp@remote_db;Materialized view created.注意：当用FAST选项创建物化视图，必须创建基于主表的视图日志,如下: SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.2.Rowid物化视图下面的语法在远程数据库表emp上创建Rowid物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_rowidREFRESH W ITH ROWIDAS SELECT * FROM emp@remote_db;Materialized view log created.3.子查询物化视图下面的语法在远程数据库表emp上创建基于emp和dept表的子查询物化视图SQL> CREATE MATERIALIZED VIEW mv_empdeptAS SELECT * FROM emp@remote_db eWHERE EXISTS(SELECT * FROM dept@remote_db dWHERE e.dept_no = d.dept_no)Materialized view log created.REFRESH 子句[refresh [fastcompleteforce][on demand commit][start with date] [next date][with {primary keyrowid}]]Refresh选项说明:a. oracle用刷新方法在物化视图中刷新数据.b. 是基于主键还是基于rowid的物化视图c. 物化视图的刷新时间和间隔刷新时间Refresh方法-FAST子句增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对主表创建物化视图日志SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created.对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

【IT专家】创建Oracle物化视图,每5分钟刷新一次使用物化视图日志

本文由我司收集整编，推荐下载，如有疑问，请与我司联系创建Oracle物化视图，每5分钟刷新一次使用物化视图日志创建Oracle物化视图，每5分钟刷新一次使用物化视图日志[英]Create Oracle Materialized View to be refreshed every 5 minute Using materialized view log I’m Trying to create Materialized View which will be updated every 5 minute automatically, I need update based on Mview log table. 我正在尝试创建物化视图,它将每5分钟自动更新一次,我需要基于Mview日志表进行更新。

I have created Materialized view log on TABLE1 TABLE1.SQL Script 我在TABLE1 TABLE1.SQL脚本上创建了Materialized视图日志 CREATE MATERIALIZED VIEW LOG ON TABLE1; -- MLOG$_TABLE1 Then I’ve created Materialized View 然后我创建了物化视图 CREATE MATERIALIZED VIEW JIBO_MVIEW REFRESH START WITH SYSDATE NEXT SYSDATE +5/24/60 ENABLE QUERY REWRITE AS SELECT O.ID ,O.DATETIME_CREATED ,O.ORIGINATOR ,O.DETAILS ,O.PAYMENT_REF FROM TABLE1 O WHERE O.ORIGINATOR LIKE ‘53%’; after changing some value In TABLE1, new Record is inserted MLOG$_TABLE1 log table 更改一些值后在TABLE1中,新的Record插入了MLOG $ _TABLE1日志表 but changed value is not updated in Materialized view (JIBO_MVIEW). (even after one day :) ) 但在物化视图(JIBO_MVIEW)中未更新更改的值。

Oracle性能调优之物化视图用法简介

Oracle性能调优之物化视图⽤法简介⽬录⼀、物化视图简介物化视图分类物化视图分类，物化视图语法和as后⾯的sql分为：(1) 基于主键的物化视图(主键物化视图)(2)基于Rowid的物化视图(Rowid物化视图)本博客介绍⼀下Oracle的物化视图，物化视图(Materialized view)是相对与普通视图⽽已的，普通视图是伪表，功能没那么多，⽽物化视图创建是需要占⽤⼀定的存储空间的，物化视图常被应⽤与调优⼀些列表SQL查询，物化视图的基本语法：create materialized view [视图名称]build immediate | deferredrefresh fase | complete | forceon demand | commitstart with [start time]next [next time]with primary key | rowid //可以省略，⼀般默认是主键物化视图as [要执⾏的SQL]ok，解释⼀下这些语法⽤意：build immediate | deferred (视图创建的⽅式)：(1) immediate：表⽰创建物化视图的时候是⽣成数据的；(2) deferre：就相反了，只创建物化视图，不⽣成数据refresh fase | complete | force (视图刷新的⽅式)：(1) fase：增量刷新，也就是距离上次刷新时间到当前时间所有改变的数据都刷新到物化视图，注意，fase模式必须创建视图⽇志(2) complete：全量更新的，complete⽅式相当于创建视图重新全部查⼀遍(3) force：视图刷新⽅式的默认⽅式，当增量刷新可⽤则增量刷新，当增量刷新不可⽤，则全量刷新，⼀般不要⽤默认⽅式on demand | commit start with ... next ...(视图刷新时间)：(1) demand：根据⽤户需要刷新时间，也就是说⽤户要⼿动刷新(2) commit：事务⼀提交，就⾃动刷新视图(3) start with：指定⾸次刷新的时间，⼀般⽤当前时间(4) next：物化视图刷新数据的周期，格式⼀般为“startTime+时间间隔”⼆、实践：创建物化视图上⾯是物化视图主要语法的简介，下⾯可以实践⼀下，创建⼀个主键物化视图ps:创建⼀个名称为MV_T的物化视图，视图创建完成是⽣成数据的，增量刷新，根据⽤户需要刷新，每隔两天刷新⼀次视图create materialized view MV_Tbuild immediaterefresh faston demandstart with sysdatenext sysdate + 2as select * from sys_user;可能遇到问题：(1)、ORA-12014: 表不包含主键约束条件SQL> create materialized view mv_t2 build immediate3 refresh fast4 on demand5 start with sysdate6 next sysdate + 27 as select * from sys_user;as select * from sys_user;第 7 ⾏出现错误:ORA-12014: 表 'SYS_USER' 不包含主键约束条件这是因为as SQL语句的表没创建主键，⽽是使⽤的是基于表的物化视图，解决⽅法是新建主键(2)、ORA-23413: 表不带实体化视图⽇志SQL> create materialized view mv_t2 build immediate3 refresh fast4 on demand5 start with sysdate6 next sysdate + 27 as select * from sys_user;as select * from sys_user;第 7 ⾏出现错误:ORA-23413: 表 "T_BASE"."SYS_USER" 不带实体化视图⽇志这是因为refresh⽅式⽤fast⽅式，fast增量⽅式必须创建视图⽇志create materialized view log on [表名];删除视图⽇志：drop materialized view log on [表名]假如是基于Rowid的物化视图，就可以⽤这种⽅法：create materialized view log on [表名] with rowid;附录：物化视图常⽤SQL删除物化视图：drop materialized view [视图名称];查看物化视图：select mv.* from user_mviews mv where mv.MVIEW_NAME = [视图名称];查看物化视图列：select sg.segment_name, sg.bytes, sg.blocks from user_segments sg where sg.segment_name = [视图名称];⼿动刷新物化视图：exec dbms_mview.refresh([视图名称]);。

物化视图

查询重写
• 是指当对物化视图的基表进行查询时，Oracle会自动判断
能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。
物化视图刷新的方式
• On commit – 指物化视图在对基表的DML操作提交（更新数据）的同时进行刷新。 • On demand – 指物化视图在用户需要的时候进行刷新
物化视图刷新的方法
• 物化视图刷新的方法有：FAST、COMPLETE、FORCE和
NEVER。其中： • FAST刷新：采用增量刷新，只刷新自上次刷新以后进行的修改。
• COMPLETE刷新：对整个物化视图进行完全的刷新。
• FORCE刷新：在刷新时会去判断是否可以进行快速刷新，如果
可以则采用FAST方式，否则采用COMPLETE的方式。（click here and you can know why.） • NEVER指物化视图不进行任何刷新。 • 默认值是FORCE ON DEMAND。
From table1 T1，table2，T2 Where T1.field1=T2.field2
• • • • • • •
What is materialized view？ Why do we need materialized view？ What is essence（本质）of materialized view？ What is Query Rewrite？ How many ways have materialized view refreshed? What is premise of creating materialized view? Scott intend to create a materialized view. Whether you are a DBA, pls you grant power to scott. • According the example, fill function of each row on the blanks,pls. • Create materialized view mtrlview_test Build immediate | deffered Refresh fast on commit Enable query rewrite As Select t1.c1，t1.c2，t2.c2，t2.c4 From table1 T1，table2，T2 Where T1.field1=T2.field2

oracle-----视图物化视图

oracle-----视图物化视图什么是视图视图(view)，也称虚表, 不占⽤物理空间，这个也是相对概念，因为视图本⾝的定义语句还是要存储在数据字典⾥的。

视图只有逻辑定义。

每次使⽤的时候,只是重新执⾏SQL。

视图是从⼀个或多个实际表中获得的，这些表的数据存放在数据库中。

那些⽤于产⽣视图的表叫做该视图的基表。

⼀个视图也可以从另⼀个视图中产⽣。

视图的定义存在数据库中，与此定义相关的数据并没有再存⼀份于数据库中。

通过视图看到的数据存放在基表中。

视图看上去⾮常象数据库的物理表，对它的操作同任何其它的表⼀样。

当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会⾃动反映在由基表产⽣的视图中。

由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。

还有⼀种视图：物化视图（MATERIALIZED VIEW ），也称实体化视图，快照（8i 以前的说法），它是含有数据的，占⽤存储空间。

视图的作⽤1)提供各种数据表现形式, 可以使⽤各种不同的⽅式将基表的数据展现在⽤户⾯前, 以便符合⽤户的使⽤习惯(主要⼿段: 使⽤别名)；2)隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句⼀般是⽐较复杂的, ⽽且⽤户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建⼀个视图, ⽤户就可以直接对这个视图进⾏"简单查询"⽽获得结果. 这样就隐藏了数据的复杂性并简化了查询语句.这也是oracle提供各种"数据字典视图"的原因之⼀,all_constraints就是⼀个含有2个⼦查询并连接了9个表的视图(在catalog.sql中定义)；3)执⾏某些必须使⽤视图的查询. 某些查询必须借助视图的帮助才能完成. ⽐如, 有些查询需要连接⼀个分组统计后的表和另⼀表, 这时就可以先基于分组统计的结果创建⼀个视图, 然后在查询中连接这个视图和另⼀个表就可以了；4)提供某些安全性保证. 视图提供了⼀种可以控制的⽅式, 即可以让不同的⽤户看见不同的列, ⽽不允许访问那些敏感的列, 这样就可以保证敏感数据不被⽤户看见；5)简化⽤户权限的管理. 可以将视图的权限授予⽤户, ⽽不必将基表中某些列的权限授予⽤户, 这样就简化了⽤户权限的定义。

物化视图刷新方法

start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=12,19',
enabled => TRUE,
comments => 'Refresh materialized view mv_emp'
);
END;
/
repeat_interval是执行的时间间隔，FREQ表示执行频率，DAILY表示每天执行，BYHOUR定义了具体的执行时间为每天的12时和19时
enabled表示任务为启用状态，如果不加这个参数，为缺省的不启用
comments为任务的一个说明，这里面的内容可以根据实际情况自行填写。
CREATE OR REPLACE PROCEDURE auto_refresh_mview_job_proc
AS
BEGIN
dbms_mview.REFRESH('mv_emp');
END;
/
3. 用Oracle 10g的scheduler每天12:00和19:00定期刷新物化视图
AS
SELECT * FROM ........
============================================================
方法二：存储过程========================================================
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=12,19',

利用物化视图和Excel数据透视表.

利用物化视图和Excel数据透视表实现一卡通数据查询师职门诊一卡通试用以来，院领导常需要有关体系外师职干部门诊就诊情况的有关信息，进而需要体系内的军人就诊情况，甚至包括同比和环比相关就诊信息情况。

这些需求，需要的数据是固定的，但展现的形式是不定的。

因此，不适合在下发的程序中固定，其实更确切地讲，这是一个初步的BI要求。

下文中，主要介绍如何结合ORACLE的物化视图和Excel的数据透视表(图)功能，实现上述要求并提供扩展空间。

一．物化视图ORACLE的物化视图(Materialed View)可用于预先计算保存连接或聚集等耗时较多的操作的结果。

这和军卫一号中医务统计中的统计中间表功能类似，但更灵活。

关于军人门诊就诊和门诊费用的统计，我们也可以将有关统计生成为物化视图，方面查询使用。

下面脚本以ORACLE10G为例，其它版本可自行调整。

为不影响HIS系统，我选择单建用户和表空间以方便今后的删除。

以DBA 用户执行：--- 建立存储表空间CREATE TABLESPACE TSP_INSURANCE_SUM DATAFILE'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TSP_INSU_75H7Z9 VT_.DBF' SIZE 100M AUTOEXTEND ON NEXT5M MAXSIZE UNLIMITEDNOLOGGINGDEFAULT COMPRESSONLINEPERMANENTEXTENT MANAGEMENT LOCAL UNIFORM SIZE10MBLOCKSIZE8KSEGMENT SPACE MANAGEMENT MANUALFLASHBACK OFF;--- 建立用户CREATE USER "CARDSUM" IDENTIFIED BY "CARDSUM"DEFAULT TABLESPACE TSP_INSURANCE_SUMTEMPORARY TABLESPACE TEMPGRANT CREATE MATERIALIZED VIEW TO CARDSUM;GRANT CREATE PROCEDURE TO CARDSUM WITH ADMIN OPTION;GRANT SELECT ANY TABLE TO CARDSUM;GRANT UNLIMITED TABLESPACE TO CARDSUM;GRANT "CONNECT" TO "CARDSUM";ALTER USER "CARDSUM" DEFAULT ROLE "CONNECT";ALTER USER CARDSUM QUOTA UNLIMITED ON TSP_INSURANCE_SUM;以cardsum用户执行：--- 判断是否是体系内外，通过判断帐号的体系医院和本院代码/* Formatted on 2011/08/27 20:27 (Formatter Plus v4.8.6) */CREATE OR REPLACE FUNCTION getserviceclass (a_visit_date DATE,a_insurance_no VARCHAR2)RETURN VARCHAR2ISv_serviceclass VARCHAR2(30);v_hospital_code hospital_config.unit_code%TYPE;--±¾Ôº´úÂëv_inservicelist VARCHAR2(1);v_service_class VARCHAR2(29);/******************************************************************** **********NAME: GetServiceClassPURPOSE: 获取指定帐号的身份信息参数： a_visit_date 就诊时间a_insurance_no 帐号返回值：第一字符标明是体系内(0)还是体系外(1) ,第二个字符标明身份REVISIONS:Ver Date Author Description--------- ---------- ---------------------------------------------------1.0 2011-8-14 1. Created this function.NOTES:Automatically available Auto Replace Keywords:Object Name: GetServiceClassSysdate: 2011-8-14Date and Time: 2011-8-14, 10:52:27, and 2011-8-14 10:52:27Username:Table Name: (set in the "New PL/SQL Object" dialog)********************************************************************* *********/BEGINSELECT unit_codeINTO v_hospital_codeFROM hospital_config;SELECT DECODE (designated_hospital, v_hospital_code,0,1), identity_classINTO v_inservicelist, v_service_classFROM insurance_accountsWHERE insurance_no = a_insurance_no;IF v_inservicelist =1THEN--外体系BEGINSELECT service_classINTO v_service_classFROM outp_card_bill_itemsWHERE visit_date = a_visit_dateAND insurance_no = a_insurance_noAND ROWNUM=1;EXCEPTIONWHEN NO_DATA_FOUNDTHENNULL;END;END IF;v_serviceclass := v_inservicelist || v_service_class;RETURN v_serviceclass;EXCEPTIONWHEN NO_DATA_FOUNDTHENRETURN'1其它';WHEN OTHERSTHEN-- Consider logging the error and then re-raiseRETURN'1其它';END getserviceclass;/--- 创建门诊就诊的物化视图CREATE MATERIALIZED VIEW cardsum.mv_card_clinicTABLESPACE tsp_insurance_sumSTORAGE (INITIAL 10 mNEXT 5 m)NOCACHENOLOGGINGCOMPRESSNOPARALLELBUILD DEFERREDREFRESH FORCE ON DEMANDSTART WITH TO_DATE('20110828 23:00:00','yyyymmdd hh24:mi:ss')NEXT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE - 1, 'd')) + 7ASSELECT DECODE (SUBSTR (service_class, 1, 1),'0', '体系内','体系外') "体系内外",SUBSTR (service_class, 2, LENGTH (service_class) - 1) "身份",trunc(visit_date ,'MM')"时间", COUNT (visit_no) "人次"FROM (SELECT visit_date, visit_no, patient_id, insurance_no, insurance_type,getserviceclass (visit_date, insurance_no) service_classFROM clinic_masterWHERE visit_date > TO_DATE ('20110601', 'YYYYMMDD')AND charge_type = '军队医改' AND insurance_no IS NOT NULL )GROUP BY trunc(visit_date ,'MM'), service_class;需要说明的是：BUILD DEFERRED 表示该物化视图不是立即更新数据，因为更新数据需要消耗较多系统资源，因此建议到数据库闲时操作。

oracle物化视图实现数据同步

ORACLE物化视图实现数据同步简介NC56新增双引擎查询功能，以下报表支持此功能：财务：总账—辅助余额表总账—辅助明细账总账—辅助属性余额表总账—多维分析表应收—余额表应收—应收账龄分析应付—余额表应付—应付账龄分析供应链：采购管理—供应商暂估余额表销售管理—综合日报库存管理—收发存汇总表库存管理—出入库流水账存货核算—收发存汇总表存货核算—入库汇总表存货核算—出库汇总表本功能只支持oracle数据库。

要使用此功能，需要在oracle 数据库上按以下步骤进行配置。

一、按照NC标准创建一个oracle空用户，假设为nc56des（以下称为目标用户）二、使用该空用户创建到NC数据库的数据库链接，假设NC数据库用户为nc56（以下称为源用户）Create database link dblink connect to nc56 identified by密码using ora10g_127.0.0.1;需要注意：如果源用户和目标用户未在同一台数据库服务器上，则在目标数据库服务器上的tnsnames.ora中已经配置了源用户所在数据库服务器，本例假设源用户和目标用户在同一台服务器上。

三、在源用户上执行以下sql：1、对于已定义主键的表，分别执行以下sql：create materialized view log on 表名with primary key;2、对于未定义主键的表，分别执行以下sql：create materialized view log on 表名with rowid;四、在目标用户上执行以下sql：1、对于已定义主键的表，分别执行以下sql：create materialized view 表名refresh fast start with sysdate next sysdate+10/1440 as select * from 表名@dblink;以上假设为10分钟同步一次，可以根据需要进行修改同步的频率，单位为“分钟”2、对于未定义主键的表，分别执行以下sql：create materialized view 表名refresh fast with rowid start with sysdate next sysdate+10/1440 as select * from表名@dblink;以上假设为10分钟同步一次，可以根据需要进行修改同步的频率，单位为“分钟”需要特别注意，不要将同步时间间隔设置的太短，最短不要低于10分钟，否则对数据库服务器压力太大。