文档库 最新最全的文档下载
当前位置:文档库 › 物化视图的创建和快速刷新

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

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

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

1.物化视图简介

物化视图,它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性;

物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。

其中物化视图有三种:聚集物化视图、包含连接物化视图、嵌套物化视图。但三种物化视图的快速刷新的限制条件有很大区别,而其他方面则区别不大。

2.物化视图的创建和参数说明

2.1 物化视图实例

南昌营运数据为5000万条左右,每日新增20w条左右。根据报表的实际需求,在物化视图中按照车牌,车队,天汇总统计

后的数据为80万条。这样大大提高了查询的效率。

创建物化视图的语句如下:

create materialized view BUSINESS_DATA_CAR_MV

BUILD 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 --刷新时间间隔

as

select

to_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 bus

where date_up <= date_down and(date_down -date_up ) <0.5 and distance>=0 and distance<100 and free_distance<500

group by service_no,dispatch_car_no,to_char(date_up,'yyyy-MM-dd'),taxi_group,taxi_company;, 2.2参数介绍

2.2.1 BUILD

BUILD IMMEDIATE :是在创建物化视图的时候就生成数据,。

BUILD DEFERRED :则在创建时不生成数据,以后根据需要在生成数据。

默认为BUILD IMMEDIATE。

2.2.2 REFRESH

FAST :增量刷新用物化视图日志,来发送主表已经修改的数据行到物化视图中。

COMPLETE:完全刷新重新生成整个视图,如果请求完全刷新,oracle会完成完全刷新即使增量刷新可用。

FORCE:如果增量刷新可用Oracle将完成增量刷新,否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or FORCE)。

默认选项是Force。

2.2.3 ON

ON DEMAND :指物化视图在用户需要的时候进行刷新。

ON COMMIT :指出物化视图在对基表的DML操作提交的同时进行刷新。

默认是 ON DEMAND。

2.2.4 START WITH

通知数据库完成从主表到本地表第一次复制的时间。

2.2.5 NEXT

说明了刷新的间隔时间。

注:根据下一次刷新的时间=上一次执行完成的时间+时间间隔。

为了保证在用户需要的时间点刷新,一般使用TRUNC()命令对时间取整到天数,然后加上时间。如例子中的刷新是每天的凌晨2点开始执行物化视图的刷新。

3.物化视图的快速刷新

物化视图的快速刷新采用了增量的机制,在刷新时,只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。

但是快速刷新具有较多的约束,而且对于采用ON COMMIT模式进行快速刷新的物化视图更是如此。对于包含聚集和包含连接的物化视图的快速刷新机制并不相同,而且对于多层嵌套的物化视图的快速刷新更是有额外的要求。

3.1所有类型的快速刷新物化视图都必须满足的条件

物化视图不能包含对不重复表达式的引用,如SYSDATE和ROWNUM;

物化视图不能包含对LONG和LONG RAW数据类型的引用。3.2 只包含连接的物化视图的快速刷新条件

必须满足所有快速刷新物化视图都满足的条件;

不能包括GROUP BY语句或聚集操作;

如果在WHERE语句中包含外连接,那么唯一约束必须存在于连

接中内表的连接列上;

如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”

操作。

FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。

FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。

3.3 包含聚集的物化视图的快速刷新条件

必须满足所有快速刷新物化视图都满足的条件;

物化视图查询的所有表必须建立物化视图日志,且物化视图日志必须满足下列限制:

a.包含物化视图查询语句中的所有列,包括SELECT列表中的列和

WHERE语句中的列;

b.必须指明ROWID和INCLUDING NEW VALUES;

c.如果对基本的操作同时包括INSERT、UPDATE和DELETE操作(即

不是只包含INSERT操作),那么物化视图日志应该包括SEQUENCE。 允许的聚集函数包括:SUM、COUNT、AVG、STDDEV、VARIANCE、MIN 和MAX;

必须指定COUNT(*);

如果指明了除COUNT之外的聚集函数,则COUNT(expr)也必须存

在;

比如:包含SUM(a),则必须同时包含COUNT(a)。

如果指明了VARIANCE(expr)或STDDEV(expr),除了COUNT(expr)外,SUM(expr)也必须指明;

Oracle推荐同时包括SUM(expr*expr)。

SELECT列表中必须包括所有的GROUP BY列

当物化视图属于下面的某种情况,则快速刷新只支持常规DML插入和直接装载,这种类型的物化视图又称为INSERT-ONLY物化视图:

a.物化视图包含MIN或MAX聚集函数;

b.物化视图包含SUM(expr),但是没有包括COUNT(expr);

c.物化视图没有包含COUNT(*)。

d.注意:如果建立了这种物化视图且刷新机制是ON COMMIT的,

则会存在潜在的问题。当出现了UPDATE或DELETE语句,除非手工完全刷新解决这个问题,否则物化视图至此以后都不再自动刷新,且不会报任何错误。

如果包含inline views、outer joins、self joins或grouping set,则兼容性的设置必须在9.0以上;

如果物化视图建立在视图或子查询上,则要求视图必须可以完全合并的。

如果没有外连接,则对WHERE语句没有限制。如果包含外连接,则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连

接的聚集物化视图,快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。

对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件:

a.SELECT语句列表中应该包含GROUPING标识符:可以是GROUP BY

表达式中所有列的GROUPING_ID函数,也可以是GROUP BY表达式中每一列的GROUPING函数;

b.例如:GROUP BY语句为:GROUP BY CUBE(a, b),则SELECT列

表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。

c.GROUP BY不能产生重复的GROUPING。

d.比如:GROUP BY a, ROLLUP(a, b)则不支持快速刷新,因为包

含了重复的GROUPING:(a), (a, b), (a)。

3.4 包含UNION ALL的物化视图的快速刷新条件

UNION ALL操作必须在查询的顶层。可以有一种情况例外:UNION ALL在第二层,而第一层的查询语句为SELECT * FROM;

被UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件;

SELECT列表中必须包含一列维护列,叫做UNION ALL标识符,每个UNION ALL分支的标识符列应包含不同的常量值;

不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询;

不支持基于分区改变跟踪(PCT)的刷新;

兼容性设置应设置为9.2.0。

嵌套物化视图的每层都必须满足快速刷新的限制条件;

对于同时包含聚集和连接的嵌套物化视图,不支持ON COMMIT 的快速刷新。

4.快速刷新实例

在南昌使用物化视图的过程中发现,物化视图的刷新方式为全刷新。当基表的数据量不断的上升,快速刷新的效率越来越慢。由于物化视图刷新时,使用物化视图的报表无法进行查询,且物化视图刷新时也会对数据库造成压力和产生大量的日志,影响到系统的使用。

快速刷新如此多的限制一般很难记全,当建立物化视图失败时,Oracle给出的错误信息又过于简单,有时无法使你准确定位到问题的原因。

Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW过程可以帮助你快速定位问题的原因。下面通过上面的例子来说明,如果通过这个过程来解决问题。

1.通过上图的sql语句看到

2.1例子中的物化视图

(BUSINESS_DATA_CAR_MV)执行的是complete及全刷新。

2.下面我们通过oracle提供的 dbms_mview.explain_mview 来

帮助我们查找快速刷新的问题。

SQL> EXEC dbms_mview.explain_mview('BUSINESS_DATA_CAR_MV', '123');

SQL> SELECT capability_name, possible,msgtxt FROM

mv_capabilities_table WHERE statement_id = '123' ORDER BY seq;

结果如下:

Oracle提示我们‘详细信息表没有实体视图日志’

3.修改物化视图

a.在基表中增加日志

SQL> CREA TE MA TERIALIZED VIEW LOG ON

SINGLE_BUSINESS_DA TA_BS

WITH SEQUENCE,ROWID

(taxi_group,taxi_company,dispatch_car_no,service_no,OYSTER_CAR D_ORIGIN_SUM,OYSTER_CARD_LEFT_SUM,sum,distance,free_di stance,date_down,date_up,waiting_hour,waiting_second) INCLUDING NEW V ALUES;

b.然后在基表中新增一条记录。

c.执行一次快速刷新

SQL> exec dbms_mview.refresh('BUSINESS_DATA_CAR_MV','F');

结果如下:

从结果中可以看到此时该物化视图执行快速刷新。

4.我们再执行oracle的物化视图分析工具

图中可以看到在基表做insert操作时,物化视图时可以做快速刷新了。由于计价器数据只是新增,所以就满足了该报表的使用。

5.根据提示对DML情况下快速刷新的条件的修改

create materialized view BUSINESS_DA TA_CAR_MV

refresh force on demand

start with to_date('14-07-2011 13:41:16', 'dd-mm-yyyy hh24:mi:ss') next SYSDA TE + 1/1440

as

select

to_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(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,1,-1,0))as ccardTime,

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,

count(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,OYSTER_CARD_ ORIGIN_SUM-OYSTER_CARD_LEFT_SUM,-1,0)) as ccardSum,

sum(sum) as sum,

count(sum) as csum,

sum(distance+free_distance)as total_distance,

count(distance+free_distance)as ctotal_distance,

sum(decode(sign(distance),1,distance,-1,0,distance)) as distance,

count(decode(sign(distance),1,distance,-1,0,distance)) as cdistance,

sum(free_distance) as free_distance,

count(free_distance) as cfree_distance,

sum(decode(sign(date_down-date_up),1,(date_down-date_up)*24,-1,0)) as workTime,

count(decode(sign(date_down-date_up),1,(date_down-date_up)*24,-1,0)) as cworkTime,

--sum((date_down-date_up)*24) as workTime,

sum(waiting_hour*60+waiting_second)as waiting_time,

count(waiting_hour*60+waiting_second)as cwaiting_time,

sum(decode(to_char(date_up,'HH24'),'02',1,'03',1,'04',1,0))as overTimeTimes,

count(decode(to_char(date_up,'HH24'),'02',1,'03',1,'04',1,0))as coverTimeTimes

from SINGLE_BUSINESS_DA TA_BS bus

where date_up <= date_down and(date_down -date_up ) <0.5 and distance>=0 and distance<100 and free_distance<500

group by service_no,dispatch_car_no,to_char(date_up,'yyyy-MM-dd'),taxi_group,taxi_company;

再执行下oracle物化视图分析工具:

就不做测试了。

物化视图

ORACLE中的物化(实体)视图 物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。 物化视图可以查询表,视图和其它的物化视图。 通常情况下,物化视图被称为主表(在复制期间)或明细表(在数据仓库中)。 对于复制,物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本,必须用高级复制的功能。当你想从一个表或视图中抽取数据时,你可以用从物化视图中抽取。 对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。 本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。 在复制环境下,创建的物化视图通常情况下主键,rowid,和子查询视图。 1.主键物化视图: 下面的语法在远程数据库表emp上创建主键物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE 1/48 + NEXT SYSDATE WITH PRIMARY KEY

AS SELECT * FROM emp@remote_db; created. view Materialized 注意:当用FAST选项创建物化视图,必须创建基于主表的视图日志,如下: SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created. 2.Rowid物化视图 下面的语法在远程数据库表emp上创建Rowid物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_rowid WITH ROWID REFRESH AS SELECT * FROM emp@remote_db; Materialized view log created. 3.子查询物化视图 下面的语法在远程数据库表emp上创建基于emp和dept表的子查询物化视图 SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp@remote_db e EXISTS WHERE (SELECT * FROM dept@remote_db d WHERE e.dept_no = d.dept_no) Materialized view log created. REFRESH 子句

第六章视图分析

第六章视图 1.概述 ?视图(View)是从一个或多个表(其他视图)中导出的表,其结构和数据是建立在对表的查询基础之上的。所以视图不是真实存在的基础表, 而是一张虚表。视图所对应的数据并不实际地以视图结构存储在数据库 中,而是存储在视图所引用的表中。 ?视图一经定义便存储在数据库中,与其相对应的数据并没有像表一样在数据库中另外存储一份,通过视图看到的数据只是存放在基表中的数据。 对视图的操作与对表的操作一样,可以对其进行查询、修改(有一定的 限制)和删除。 ?当对视图中的数据进行修改时,相应的基表的数据也要发生变化,同时,如果基表的数据发生变化,则这种变化也可以自动地反映到视图中 2.视图的特点 1.视点集中,减少对象大小 视图让用户能够着重于他们所需要的特定数据或所负责的特定要求,如 用户可以选择特定行或特定列。 2.从异构源组织数据 可以在连接两个或多个表的复杂查询的基础上创建视图,这样可以将单 个表显示给用户。 3.隐藏数据的复杂性,简化操作 视图向用户隐藏了数据库设计的复杂性,这样如果开发者改变数据库设 计,不会影响到用户与数据库交互。另外,用户可将经常使用的连接查 询、嵌套查询或联合查询定义为视图。 4.简化用户权限的管理 可以将视图的权限授予用户,而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义。 3.视图的四种类型 ?关系视图: 关系视图(relational view)基本上就是经过存储的查询,可以将它的输出看作是 一个表。它就是基于关系数据的存储对象。 ?内嵌视图: 又称为嵌套查询,是嵌入到父查询中的查询,能够在任何可以使用表名称的地 方使用。 ?对象视图: 为了迎合数据库中对象类型而将关系表投射到特定数据类型的虚拟对象表中, 视图的每行都是带有属性、方法和唯一标识(OID)的对象实例。 ?物化视图: 就是在数据库中查询结果存储在视图中,并支持查询重写、刷新、提交等特性 的视图 4.视图的创建及管理

Oracle物化视图创建全过程

Oracle物化视图创建全过程 我们如果遇到需要从其它系统的数据库中取数据进行统计分析的问题,可疑选择使用ORACLE的ODI工具进行抽数,但是对方提供的数据库用户下没有任何对象,只是有查询所有表的权限,因此无法做数据反向。 于是决定使用物化视图,把对方数据库中的数据拿过来,虽然数据量比较大,但是每月只拿一次,而且如果设置成增量更新,也不会太慢。现在记录下物化视图的创建过程(以一张表为例)。 一、准备条件以及备注 假设双方数据库都是ORACLE10g,需要同步过来的表名叫:GG_ZLX_ZHU,对方数据库用户名:username,密码:password,SID:CPEES。 二、开始干活 1、首先要创建DB_LINK CREATE DATABASE LINK to_cpees CONNECT TO "username" identified by "password" using "CPEES"; 或者 create database link to_cpees connect to username identified by "password" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =IP地址)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cti) ) )'; 其中CPEES为新建的到对方数据库的TNS。执行,现在我们就已经创建了到对方数据库的DB_LINK TO_CPEES。 2、创建Oracle物化视图快速刷新日志 因为上面说过,以后视图的刷新将采用增量刷新的方式,因此,为配合增量刷新,ORACLE要求要在住表上建立物化视图日志。 CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU WITH PRIMARY KEY INCLUDING NEW VALUES; (上面的SQL要在远程数据库上执行,不能在本地执行) 3、创建Oracle物化视图 Oracle物化视图,从名字上面来开,它应该是属于视图,但是确实物化。 其物化是针对普通视图并没有真正的物理存储而言,其实可以简单的把物化视图看做一个物理表。

第五章 视图的创建和使用

第五章视图的创建和使用 5.1 视图概述 5.1.1 视图的基本概念 ?视图是数据库中一个“不可见的表”,视图是一种基于表的关于数据库数据的查询,其内容由 查询的结果来定义。 ?对于数据库用户来说,视图似乎是一个真实的表,它具有一组命名的数据列和行。但是,与 真实的表不同,在视图中没有存储任何数据,仅仅是一种较简单的访问数据库里其他表中数据的方式,因此称它为“虚表”。而数据的物理存储位置仍然在表中,这些表称作视图的基表。 ?一个视图可以派生于一个或多个基表,也可以从其他视图中派生。视图只能建立在当前正在 使用的数据库中。 ?视图被引用时,其数据是动态生成的。 ?视图是一种SQL查询,在数据库中存储的是视图的定义,而不是查询的数据。 5.1.2 视图的用途 ?视点集中:视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务。 这样通过只允许用户看到视图中所定义的数据而不是视图引用表中的数据而提高了数据的安全性。 ?简化操作:视图大大简化了用户对数据的操作。因为在定义视图时,若视图本身就是一个复 杂查询的结果集,这样在每一次执行相同的查询时,不必重新写这些复杂的查询语句,只要一条简单的查询视图语句即可。可见视图向用户隐藏了表与表之间的复杂的连接操作。 ?定制数据:视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此,当有 许多不同水平的用户共用同一数据库时,这显得极为重要。 ?合并分割数据:在有些情况下,由于表中数据量太大,故在表的设计时常将表进行水平分割 或垂直分割,但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系,从而使外模式保持不变,原有的应用程序仍可以通过视图来重载数据。 ?安全性:视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。 其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集,必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。 5.1.3 视图的缺点 ?性能:对试图的查询,SQL Server必须转化成对基本表的查询,这时因为视图本身并不存储数据,其 中的数据来自它所引用的基表。如果这个视图是由一个复杂的多表查询所构成,那么,即使是对视图的一个简单查询,SQL也将把它变成一个复杂的结合体,这需要花费一定的时间。 ?修改的限制:对视图进行修改,SQL必须把它转化为对与视图引用基表对应的数据修改。对于简单视 图来说,这是很方便的,但是,对于比较复杂的视图,可能是不可修改的。

物化视图BUG 导致CPU消耗超高

单击此项可添加到收藏夹 MATERIALIZED VIEW FAST REFRESH IS VERY SLOW, "AS OF SNAPSHOT" CURSORS NOT BEING SHARED (文档ID 1051346.1) 转到底部转到底部 In this Document Symptoms Cause Solution References Applies to: Oracle Database - Enterprise Edition - Version 10.1.0.5 to 11.2.0.2.0 [Release 10.1 to 11.2] Information in this document applies to any platform. Symptoms On a production database version 11.1.0.7, the fast refresh of a nested materialized view takes a lot of time comparing to the select statement used for the creation of the materialized view. The refresh of the mview takes approximately 16 min. The select statement itself finishes in about 8 seconds. It can be seen that most of the time is spent with the parse of update sys.sumpartlog$ statement. ******************************************************************************* * updatesys.sumpartlog$ s set s.timestamp = :1, s.scn = :2 where

物化视图

物化视图日志结构 物化视图的快速刷新要求基本必须建立物化视图日志,这篇文章简单描述一下物化视图日志中各个字段的含义和用途。 物化视图日志的名称为MLOG$_后面跟基表的名称,如果表名的长度超过20位,则只取前20位,当截短后出现名称重复时,Oracle 会自动在物化视图日志名称后面加上数字作为序号。 物化视图日志在建立时有多种选项:可以指定为ROWID、PRIMARY KEY和OBJECT ID几种类型,同时还可以指定SEQUENCE 或明确指定列名。上面这些情况产生的物化视图日志的结构都不相同。 任何物化视图都会包括的列: 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);

数据库原理视图的创建与使用实验报告

***大学计算机科学与信息学院软件工程系上机实验报告

显示结果: 2、用企业管理器创建、管理视图 (1)创建视图 A、使用企业管理器在表teacher上创建一职称为副教授的教师视图teacherview视图,该视图中需包 括以下信息:姓名,系别,职称,工资。 1)依次进入企业管理器、数据库、XSGL库,并用鼠标右键单击“视图”。 2)在系统弹出的快捷菜单中单击“新建视图”,出现“新建视图”窗口。

B、使用企业管理器在表student、course、sc表上创建一个能查询学生的学号、姓名、课程名及课程成绩的视图,视图名为S_C_VIEW。 (2)修改视图stuview1 将视图studview1的定义修改为从student表中查询出MA系学生的学号,姓名,系别。 (提示:修改视图:在企业管理器中选中视图后->击右键->按设计视图->进行修改) (3)管理视图中的数据 1)查看视图stuview1中的数据。 (提示:用鼠标右键单击要管理的视图stuview1,单击“打开视图”,再单击“返回所有行”。)

2)将视图stuview1中学号为“6”的学生姓名改为“许华”。再重新打开student表观察一下,有什么变化(注:须按按钮“!”进行修改确认。) 3、用T-SQL语言创建、管理视图 (1)创建视图 ①创建一个名为stuview2的水平视图,从数据库XSGL的student表中查询出性别为“男”的所有学生的资 料。并在创建视图时使用with check option。(注:该子句用于强制视图上执行的所有修改语句必须符合由Select语句where中的条件。) T-SQL语句为: CREATE VIEW stuview2 AS SELECT* FROM Student WHERE Ssex='男' WITH CHECK OPTION; ②创建一个名为stuview3的视图,能检索出CS系所有女生的学号、选修的课程名及相应的成绩。 T-SQL语句为: CREATE VIEW stuview3 AS SELECT,, FROM SC,Course WHERE= AND IN ( SELECT Sno FROM Student WHERE Sdept='CS'AND Ssex='女'); ③创建一个名为stuview4的视图,能检索出选课学生的学号、姓名、课程名、成绩。

oracle物化视图同步

1.目标服务器A,数据库oracle,待同步数据表city,用户hnjcfx,口令hnjcfx 2.同步服务器B,数据库oracle,需要同步A中city表数据,服务名192(参见tnsnames.ora文件中配置,按照格式需自己配置) 192 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.192)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = fantlam) ) ) 注:HOST 为目标服务器IP地址 Fantlam为目标服务器数据库实例名 3.登录B数据库,在Boracle数据库中创建连接Aoracle数据库的连接名testlk:Create public database link testlk connect to hnjcfx identified by hnjcfx using ‘192’ 注:目标方数据库(A)的init.ora文件中的global_names设为false。 创建数据库链接的帐号(B)必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系统权限,用来登录到远程数据库的帐号(hnjcfx)必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中(CREATE PUBLIC DATABASE LINK权限在DBA中)。 4.在A数据库中创建存储日志视图 Create materialized view log on city 5.在B 数据库中创建物化视图city create materialized view city refresh force on demand with rowid START WITH TO_DATE('08-12-2008 16:40:00','dd-mm-yyyy hh24:mi:ss') NEXT SYSDATE+1/(24*60)as SELECT * from city@testlk; 注:如果24小时同步刷新数据改动SYSDATE+1 6.B数据库中city视图是由A同步来的,只可以做查询操作,无增改删操作。

物化视图PPT

SQL> CREATE MATERIALIZED VIEW 2> depart_sal_sum as 3> select d.department_name, sum(e.salary) 4> from departments d, employees e 5> where d.department_id = e.department_id 6> group by d.department_name; SQL> CREATE MATERIALIZED VIEW 2> depart_sal_sum as 3> select d.department_name, sum(e.salary) 4> from departments d, employees e 5> where d.department_id = e.department_id 6> group by d.department_name;

?刷新一个到多个基表的物化视图 DBMS_MVIEW.REFRESH (’CUST_SALES ’, parallelism => 10);DBMS_MVIEW.REFRESH (’CUST_SALES ’, parallelism => 10);DBMS_MVIEW.REFRESH_DEPENDENT DBMS_MVIEW.REFRESH_DEPENDENT((’SALES ’);DBMS_MVIEW.REFRESH_DEPENDENT (’SALES ’);DBMS_MVIEW.REFRESH_ALL_MVIEWS DBMS_MVIEW.REFRESH_ALL_MVIEWS; ;DBMS_MVIEW.REFRESH_ALL_MVIEWS ;

物化视图使用详解

一.物化视图概述 Oracle的物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。 物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。 物化视图可以查询表,视图和其它的物化视图。 通常情况下,物化视图被称为主表(在复制期间)或明细表(在数据仓库中)。 对于复制,物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本,必须用高级复制的功能。当你想从一个表或视图中抽取数据时,你可以用从物化视图中抽取。 对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。 在复制环境下,创建的物化视图通常情况下主键,rowid,和子查询视图。 物化视图由于是物理真实存在的,故可以创建索引。 1.1 物化视图可以分为以下三种类型 (1)包含聚集的物化视图; (2)只包含连接的物化视图; (3)嵌套物化视图。 三种物化视图的快速刷新的限制条件有很大区别,而对于其他方面则区别不大。创建物化视图时可以指定多种选项,下面对几种主要的选择进行简单说明: (1)创建方式(BuildMethods):包括BUILD IMMEDIATE和BUILD DEFERRED两种。

物化视图日志简介

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; 实体化视图日志已创建。 SQL> create table t_pk (id number primary key, name varchar2(30), num number); 表已创建。 SQL> create materialized view log on t_pk with primary key; 实体化视图日志已创建。

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

materialized view(物化视图)基础知识 1物化视图概念 物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。 物化视图可以查询表,视图和其它的物化视图。 通常情况下,物化视图被称为主表(在复制期间)或明细表(在数据仓库中)。 对于复制,物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本,必须用高级复制的功能。当你想从一个表或视图中抽取数据时,你可以用从物化视图中抽取。 对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。 本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。 在复制环境下,创建的物化视图通常情况下主键,rowid,和子查询视图。 1.1主键物化视图: 下面的语法在远程数据库表emp上创建主键物化视图 SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/48 WITH PRIMARY KEY AS 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 ROWID AS SELECT * FROM emp@remote_db; Materialized view log created. 3.子查询物化视图 下面的语法在远程数据库表emp上创建基于emp和dept表的子查询物化视图 SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp@remote_db e WHERE EXISTS (SELECT * FROM dept@remote_db d WHERE 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. 物化视图的刷新时间和间隔刷新时间

用友oracle物化视图实现数据同步

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

本功能只支持oracle数据库。要使用此功能,需要在oracle 数据库上按以下步骤进行配置。 一、按照NC标准创建一个oracle空用户,假设为nc57des(以下称为目标用户) 二、使用该空用户创建到NC数据库的数据库链接,假设NC数据库用户为nc57(以下称为 源用户) Create database link dblink connect to nc57 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分钟同步一次,可以根据需要进行修改同步的频率,单位为“分钟”

如何创建物化视图

1、创建方式(Build Methods):Build Immediate 和Build Deferred两种。Build Immediate 是在创建物化视图的时候就生成数据,而Build Deferred则在创建时不生成数据,以后根据需要再生成数据。默认为Build Immediate 。 2、查询重写(Query Rewrite):Enable Query Rewrite和Disable Query Rewrite两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为Disable Query Rewrite。 3、刷新(Refresh):指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种:On Demand和On Commit。On Demand指物化视图在用户需要的时候进行刷新,可以手工通过 DBMS_MVIEW.REFRESH等方法来进行刷新,也可以通过JOB定时进行刷新。On Commit指物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种:Fast 、Complete 、Force和Never。Fast 刷新采用增量刷新,只刷新自上次刷新以后进行的修改。Complete 刷新对整个物化视图进行完全的刷新。如果选择Force方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用Complete 的方式。Never 指物化视图不进行任何刷新。默认值是Force On Demand。 在建立物化视图的时候可以指定Order by 语句,使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中,而且对以后的刷新也无效。 4、物化视图日志:如果需要进行快速刷新,则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要,可以建立为RowID或Primary Key类型的。还可以选择是否包括Sequence、Including New Values 以及指定列的列表。 可以指明On PreBuild Table语句将物化视图建立在一个已经存在的表上。这种情况下,物化视图和表必须同名。当删除物化视图时,不会删除同名的表。这种物化视图的查询重写要求参数 Query_Rewrite_integerity必须设置为 trusted或者stale_tolerated。 5、物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪(PCT)。具有这种特性的物化视图,当基表进行了分区维护操作后,仍然可以进行快速刷新操作。对于聚集物化视图,可以在Group by 列表中使用Cube或RollUp,来建立不同等级的聚集物化视图。

【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)中未更新更改的值。(纵然一天后:)) As I checked in Alert Log there is problem with auto generated DBMS_JOB, it fails on every executions. ?当我检查警报日志时,自动生成的DBMS_JOB存在问题,每次执行都会失败。 ?- ORA-12012: error on auto execute of job 4263 - ORA-00942: table or view does not

实验六 视图的创建和使用

实验六视图的创建和使用 开课实验室:指导老师: 学院:专业(班级): 姓名:学号: 一、实验目的与要求 1)理解视图的概念 2)掌握利用企业管理器和CREATE VIEW命令创建视图方法。 3)熟悉修改视图、查看视图和删除视图的方法。 4)掌握通过视图修改数据表的方法 二、实验过程设计及实验步骤 创建视图是数据库应用中的常见需求,可以使用企业管理器创建、管理视图,也可以用T-SQL语句创建、管理视图。 1)在企业管理器中创建如下视图:在XSGL数据库中使用表STUDENT和SCORE 创建视图VIEW_STUDENTSCROE,来查询每个学生的姓名和选课情况。 2)在查询分析器中创建视图:将上题用CREATE VIEW 来创建,视图名为VIEW2 程序代码: 3)查看视图信息:使用系统存储过程SP_HELP、SP_HELPTEXT、SP_DEPENDS 4)删除视图:将视图VIEW2删除. 三、SQL调试及结果 创建视图是数据库应用中的常见需求,可以使用企业管理器创建、管理视图,也可以用T-SQL语句创建、管理视图。 1)在企业管理器中创建如下视图:在XSGL数据库中使用表STUDENT和SCORE 创建视图VIEW_STUDENTSCROE,来查询每个学生的姓名和选课情况。

2)在查询分析器中创建视图:将上题用CREATE VIEW 来创建,视图名为VIEW2 程序代码: CREATE VIEW VIEW2 AS SELECT STUDENT.SNAME,https://www.wendangku.net/doc/c91627870.html,O FROM STUDENT,SCORE 3)查看视图信息:使用系统存储过程SP_HELP、SP_HELPTEXT、SP_DEPENDS 执行代码;SP_HELPTEXT VIEW2

实验十一 创建和使用视图

实验十一创建和使用视图 1.实验目的 1)掌握视图的创建、修改和删除。 2)掌握使用视图来访问数据。 2.实验内容 1)创建一个简单的视图,查询101号课程不及格的学生信息。 2)修改简单视图,查询107号课程成绩介于70-90的学生信息。 3)使用视图访问数据。 4)删除所创建的视图。 3.实验步骤 1)启动SQL Server企业管理器,打开“SQL Server Enterprise Manager” 窗口。 2)选择要创建视图的数据库文件夹,如“XSCJ”文件夹,并在右边的对象窗口中选择其中的“视图”对象。 3)选择“操作”菜单中的“新建视图”命令,打开SQL Server的视图设计窗口。 4)在“数据源关系图窗口”中单击鼠标右键,打开“添加表”窗口,添加XSQK 表和XS_KC表。 5)选择XSQK表的学号和姓名列,选择XS_KC表的课程号和成绩列,作为视图的显示列。 6)设置学号列的排序类型为升序。 7)设置查询条件:先在课程号行的“准则”列设置条件为“=‘101’”,然后在成绩行的“准则”列设置条件为“<60”。 (截图) 8)点击快捷工具栏上的快捷按钮,在弹出的“另存为”对话框中输入视图名,如“v_101不及格”,然后单击“确定”按钮,关闭视图设计窗口,完成视图的创建。

9)在“v_101不及格”视图上单击鼠标右键,在弹出的快捷菜单中选择“设计视图”命令,修改视图定义。 10)添加数据源KC表,以显示107号课程的课程名称。打开“添加表”窗口,选择“KC”表,系统自动为KC表和XS_KC表建立基于课程号的内连接。11)选择KC表中的课程名列。 12)添加查询条件:修改课程号行中“准则”列的条件为“=‘107’”;修改成绩行中“准则”列的条件为“>=70”,并复制该行,去掉“输出”列中的复选,并修改“准则”列的条件为“<=90”。(截图) 13)点击快捷工具栏上的快捷按钮,关闭视图设计窗口,保存对视图的修改。 给出最后的代码清单(SQL): SELECT TOP 100 PERCENT dbo.XSQK.学号, dbo.XSQK.姓名, dbo.XS_KC.课程号, dbo.KC.课程名 FROM dbo.XSQK INNER JOIN dbo.XS_KC ON dbo.XSQK.学号 = dbo.XS_KC.学号 INNER JOIN dbo.KC ON dbo.XS_KC.课程号 = dbo.KC.课程号 WHERE (dbo.XS_KC.课程号 = '107') AND (dbo.XS_KC.成绩 <= 90) ORDER BY dbo.XSQK.学号 请自已创建以下视图并写出代码(SQL): 14)创建一个简单视图,查询“计算机系”学生的信息。(截图)

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

物化视图的创建和快速刷新 1.物化视图简介 物化视图,它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性; 物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。 其中物化视图有三种:聚集物化视图、包含连接物化视图、嵌套物化视图。但三种物化视图的快速刷新的限制条件有很大区别,而其他方面则区别不大。 2.物化视图的创建和参数说明 2.1 物化视图实例 南昌营运数据为5000万条左右,每日新增20w条左右。根据报表的实际需求,在物化视图中按照车牌,车队,天汇总统计 后的数据为80万条。这样大大提高了查询的效率。 创建物化视图的语句如下: create materialized view BUSINESS_DATA_CAR_MV BUILD 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 --刷新时间间隔 as select to_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 bus where date_up <= date_down and(date_down -date_up ) <0.5 and distance>=0 and distance<100 and free_distance<500 group by service_no,dispatch_car_no,to_char(date_up,'yyyy-MM-dd'),taxi_group,taxi_company;, 2.2参数介绍 2.2.1 BUILD BUILD IMMEDIATE :是在创建物化视图的时候就生成数据,。 BUILD DEFERRED :则在创建时不生成数据,以后根据需要在生成数据。 默认为BUILD IMMEDIATE。

相关文档
相关文档 最新文档