ORACLE物化视图_全攻略

合集下载

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提供的系统级存储过程或包，来保证物化视图与基表数据⼀致性。

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

物化视图的创建和快速刷新

物化视图的创建和快速刷新1．物化视图简介物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。

物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

其中物化视图有三种：聚集物化视图、包含连接物化视图、嵌套物化视图。

但三种物化视图的快速刷新的限制条件有很大区别，而其他方面则区别不大。

2.物化视图的创建和参数说明2.1 物化视图实例南昌营运数据为5000万条左右，每日新增20w条左右。

根据报表的实际需求，在物化视图中按照车牌，车队，天汇总统计后的数据为80万条。

这样大大提高了查询的效率。

创建物化视图的语句如下：create materialized view BUSINESS_DATA_CAR_MVBUILD DEFERRED –在创建时不刷新，按照用户设定的时间刷新refresh force –如果可以快速刷新则进行快速刷新，否则进行完全刷新on demand –按照指定的方式刷新start with to_date('14-07-2011 13:41:16', 'dd-mm-yyyy hh24:mi:ss') --第一次刷新时间next TRUNC(SYSDA TE+1)+ 2/24 --刷新时间间隔asselectto_char(date_up,'yyyy-MM-dd') as day,taxi_group,taxi_company,dispatch_car_no,service_no,sum(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,1,-1,0)) as cardTime,count(*) as times,sum(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,OYSTER_ CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM,-1,0)) as cardSum,sum(sum) as sum,sum(distance+free_distance)as total_distance,sum(decode(sign(distance),1,distance,-1,0,distance)) as distance,sum(free_distance) as free_distance,sum(decode(sign(date_down-date_up),1,(date_down-date_up)*24,-1,0)) as workTime,sum(waiting_hour*60+waiting_second)as waiting_time,sum(decode(to_char(date_up,'HH24'),'02',1,'03',1,'04',1,0))as overTimeTimes,from SINGLE_BUSINESS_DA TA_BS buswhere date_up <= date_down and(date_down -date_up ) <0.5 and distance>=0 and distance<100 and free_distance<500group by service_no,dispatch_car_no,to_char(date_up,'yyyy-MM-dd'),taxi_group,taxi_company;, 2.2参数介绍2.2.1 BUILDBUILD IMMEDIATE ：是在创建物化视图的时候就生成数据，。

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物化视图全攻略

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

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

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

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

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

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

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

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

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

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

1.主键物化视图：下面的语法在远程数据库表emp上创建主键物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_pkREFRESH FAST START WITH 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_rowid REFRESH WITH 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 [fast|complete|force][on demand | commit][start with date] [next date][with {primary key|rowid}]]Refresh选项说明:a.oracle用刷新方法在物化视图中刷新数据.b.是基于主键还是基于rowid的物化视图c.物化视图的刷新时间和间隔刷新时间Refresh方法-FAST子句增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对主表创建物化视图日志SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created.对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

物化视图解释和刷新

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物化视图日志结构物化视图的快速刷新要求基本必须建立物化视图日志，这篇文章简单描述一下物化视图日志中各个字段的含义和用途。

物化视图日志的名称为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物化视图⽇志结构物化视图的快速刷新要求基本必须建⽴物化视图⽇志，这篇⽂章简单描述⼀下物化视图⽇志中各个字段的含义和⽤途。

物化视图⽇志的名称为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使用物化视图和查询重写功能

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

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

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

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

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

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

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

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

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

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

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

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

materialized view(物化视图)基础知识

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

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

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

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

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

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

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

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

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

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

1.1主键物化视图：下面的语法在远程数据库表emp上创建主键物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_pkREFRESH FAST START WITH 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 WITH 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 [fast|complete|force] [on demand | commit][start with date] [next date] [with {primary key|rowid}]] Refresh选项说明:a. oracle用刷新方法在物化视图中刷新数据.b. 是基于主键还是基于rowid的物化视图c. 物化视图的刷新时间和间隔刷新时间Refresh方法-FAST子句增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对主表创建物化视图日志SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

Oracle物化视图，物化视图日志，增量刷新同步远程数据库

Oracle物化视图，物化视图⽇志，增量刷新同步远程数据库1.创建DBLINK-- Drop existing database linkdrop public database link LQPVPUB;-- Create database linkCREATE DATABASE LINK LQPVPUB 6CONNECT TO "INTEPDM" identified by "password" 8using "LQPVPUB"2.创建物化视图⽇志(远程主机操作)CREATE MATERIALIZED VIEW LOG ON dm_basicmodelWITH PRIMARY KEYINCLUDING NEW VALUES;3.创建物化视图CREATE MATERIALIZED VIEW mv_model --创建物化视图BUILD IMMEDIATE --在视图编写好后创建REFRESH FAST WITH PRIMARY KEY--根据主表主键增量刷新（FAST，增量）ON DEMAND -- 在⽤户需要时，由⽤户刷新ENABLE QUERY REWRITE --可读写ASSELECT bm_id,bm_partid,bm_code from dm_basicmodel@LQPVPUB t --查询语句4.删除物化视图和⽇志⽇志和物化视图要分开删除DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;DROP MATERIALIZED VIEW GG_ZLX_ZHU;5.物化视图更新CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH ASBEGINDBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');END P_MVIEW_REFRESH;注意:5.1、如果需要同时刷新多个物化视图，必须⽤逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新⽅式（f、增量刷新，c、完全刷新，?、强制刷新）。

【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-----视图物化视图

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

视图只有逻辑定义。

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

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

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

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

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

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

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

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

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

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

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

Oracle的实体化视图管理

Oracle的实体化视图管理1. 实体化视图概念实体化视图管理是用于汇总,预计算,复制或分发数据的对象, 在大型的数据库中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度,只要在实体化视图管理上创建了统计,查询优化器将自动的使用实体化视图管理,这特性被称为QUERY REWRITE(查询重写).与普通的视图不同的是实体化视图管理存储数据,占据数据库的物理空间的.创建实体化视图管理的用户的权限必须有: CREATE MATERIALZED VIEW,CREATETABLE,CREATE VIEW,SELECT等,如果在其他的模式中创建的话要在表上有CREATE ANY MATERIALIZED VIEW和SELECT权限.要查询重引用别的模式中的实体化视图管理的话,还要有引用的表的GLOBAL QUERY REWRITE OR QUERY REWRITE权限.如果计划使用实体化视图管理的话,要修改参数文件中加: QUERY_REWRITE_ENABLE=TRUE2. 创建实体化视图注意事项:创建之后,是否要填写数据多长时间刷新一次使用那种刷新类型: COMPLE(完全),FAST(快速),FORCE(强制),NEVER(从不)3. 创建实体化视图CREATE MATERIALIZED VIEW TEST3PCTFREE 0 TABLESPACE MTESTSTORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)BUILD DEFERREDREFRESH FAST ON COMMITENABLE QUERY REWRITEAS SELECT EMP_NO,SUM(QTY_PSC) AS QTY_PSC FROM BSEMPMS GROUP BY EMP_NO;4. 删除实体化视图DROP MATERIALIZED VIEW TEST3;5. 管理和评估实体化视图的工具要运行DBMSSNAP.SQL和DBMSSUM.SQL为实体化视图创建包DBMS_MVIEW和DBMS_OLAPDBMS_MVIEW用于执行管理活动的.DBMS_OLAP确定实体化视图是否能提高查询的性能.6. 刷新实体化视图的方法EXECUTE DBMS_MVIEW.REFRESH('TEST3','C');其中TEST3为实体化视图名称,'C'为刷新类型中的一种.刷新类型有: C-完全刷新F-快速刷新和强制刷新EXECUTE DBMS_MVIEW.REFRESH_ALL不接受任何参数,全部刷新.7. 实体化视图间的完整性在实体化视图互相嵌套的时候会用不同的实体化视图的刷新的方式和时间的不同而引起的数据的完整性的问题,对于这种情况,建议把相关联的实体化视图放在同一个刷新组中,协调进行刷新.DBMS_REFRESH软件包中提供MAKE过程来创建一个刷新组.例如:EXECUTE DBMS_REFRESH.MAKE(NAME=>'TEST_GROUP',LIST=>'LOC_EMP,LOC_DPT',NEXT_DATE=>SYSDATE,INTERVAL=>'SYSDATE+7');上面创建一个实体化视图刷新组TEST_GROUP,刷新周期为7天一次的. 两个实体化视图LOC_EMP和LOC_DPT通过一个参数来传递给过程的. 8. 向刷新组中增加刷新的实体化视图DBMS_REFRESH.ADD(NAME IN VARCHAR2,LIST IN VARCHAR2,|TAB IN DBMS_UTILITY.UNCLARRAY,LAX IN BOOLEAN:=FALSE);9. 向刷新组中删除刷新的实体化视图DBMS_REFRESH.SUBTRACT(NAME IN VARCHAR2,LIST IN VARCHAR2,|TAB IN DBMS_UTILITY.UNCLARRAY,LAX IN BOOLEAN:=FALSE);10. 手工删除一个刷新组EXECUTE DBMS_REFRESH.REFRESH('TEST_GROUP');也可以EXECUTE DBMS_REFRESH.DESTROY(NAME=>'TEST_GROUP');11. 实体化视图的日志管理实体化视图日志是一个表,保持对实体化视图操作的历史记录.要创建实体化视图日志必须能在表上创建AFTER ROW触发器,必须有CREATE TRIGGER,CREATE TABLE权限例如:CREATE MATERIALIZED VIEW LOG ON BSEMPMSTABLESPACE DATA_TESTSTORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)PCTFREE 5 PCTUSED 90;实体化视图日志的PCTFREE应该很小,PCTUSED应该很大的.通过ALTER MATERIALIZED VIEW LOG命令可以修改实体化视图日志参数.如:ALTER MATERIALIZED VIEW LOG BSEMPMS PCTFREE 1;要撤销实体化视图日志,可以用DROP MATERIALIZED VIEW LOG命令如:DROP MATERIALIZED VIEW LOG ON BSEMPMS;12. 清除实体化视图日志要减少实体化视图日志使用的空间,可用DBMS_MVIEW软件包中的PURGE_LOG过程,PURGE_LOG 有三个参数:主表名称,NUM变量,DELETE标志NUM指最近最少刷新的实体化视图数量例如:EXECUTE DBMS_MVIEW.PURGE_LOG(MATER=>'BSEMPMS',NUM=>1,FLAG=>'DELETE');BSEMPMS表的实体化视图将清除实体化视图最近最少使用的条目. 要在截断主表的时候而不丢失实体化视图日志条目,可以用命令TRUNCATE TABLE BSEMPMS PRESERVE MATERIALIZED VIEW LOG;。

物化视图刷新方法

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',

Oracle复杂物化视图

Oracle 物化视图 oracle materialized view 中的首先创建两个数据库表ORACLE中的物化视图物化视图刷新-- Create table PC_WELL_TESTcreate table PC_WELL_TEST(WELLID NUMBER,TUBING_PRES NUMBER,WELLDATE DATE default sysdate)--添加数据insert into pc_well_test (wellid,tubing_pres) values(1007,199); commit;-- Create table PC_ALARM_SORT_TESTcreate table PC_ALARM_SORT_TEST(ID NUMBER,LOWER_LIMIT_1 NUMBER,LOWER_LIMIT_2 NUMBER,LOWER_LIMIT_3 NUMBER,UPPER_LIMIT_1 NUMBER,UPPER_LIMIT_2 NUMBER,UPPER_LIMIT_3 NUMBER)insert into pc_alarm_sort_test values(1,30,20,10,100,200,300); commit;创建物化视图--不能从复杂查询中获得快速查询create materialized view mview_pc_well_testbuild immediaterefresh ON DEMAND WITH ROWIDenable query rewriteas select pctest.wellid wellid,pctest.tubing_prestubing_pres,(case when pctest.tubing_pres<=(SELECT lower_limit_3 FROM pc_alarm_sort_test )or pctest.tubing_pres>=(SELECT upper_limit_3 FROM pc_alarm_sort_test ) then'3'when(pctest.tubing_pres>=(SELECT upper_limit_2 FROM pc_alarm_sort_test )and pctest.tubing_pres<=(SELECT upper_limit_3 FROM pc_alarm_sort_test )) or(pctest.tubing_pres<(SELECT lower_limit_2 FROM pc_alarm_sort_test ) and pctest.tubing_pres>(SELECT lower_limit_3 FROM pc_alarm_sort_test ) ) then'2'when(pctest.tubing_pres>=(SELECT upper_limit_1 FROM pc_alarm_sort_test )and pctest.tubing_pres<=(SELECT upper_limit_2 FROM pc_alarm_sort_test )) or(pctest.tubing_pres<(SELECT lower_limit_1 FROM pc_alarm_sort_test ) and pctest.tubing_pres>(SELECT lower_limit_2 FROM pc_alarm_sort_test ) ) then'1'else'error'end) alert_level,'报警'as报警 ,pctest.welldate welldatefrom pc_well_test pctest order by pctest.wellid--查询物化视图select * from mview_pc_well_test;--手动刷新物化视图exec dbms_mview.refresh('mview_pc_well_test')--当有新数据添加时，先要手动刷新物化视图，再查询物化视图即可以得到最新的结果。

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分钟，否则对数据库服务器压力太大。

Oracle 的物化视图

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

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

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

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

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

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

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

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

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

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

1.主键物化视图：下面的语法在远程数据库表emp上创建主键物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_pkREFRESH FAST START WITH 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 WITH 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 [fast|complete|force] [on demand | commit][start with date] [next date] [with {primary key|rowid}]]Refresh选项说明:a. oracle用刷新方法在物化视图中刷新数据.b. 是基于主键还是基于rowid的物化视图c. 物化视图的刷新时间和间隔刷新时间Refresh方法-FAST子句增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对主表创建物化视图日志SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

ORACLE物化视图_全攻略

oracle物化视图及创建索引

物化视图的创建和快速刷新

Oracle物化视图详解

ORACLE物化视图 全攻略

物化视图解释和刷新

物化视图日志简介

oracle物化视图日志结构

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

materialized view(物化视图)基础知识

Oracle物化视图，物化视图日志，增量刷新同步远程数据库

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

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

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

Oracle的实体化视图管理

物化视图刷新方法

Oracle复杂物化视图

oracle物化视图实现数据同步

Oracle 的物化视图

ORACLE物化视图全攻略