文档章节

Oracle之物化视图

Reborn-D
 Reborn-D
发布于 2016/06/27 13:26
字数 2244
阅读 23
收藏 2
点赞 0
评论 0

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会建立一个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 

物化视图创建参数

 (1)BUILD BUILD IMMEDIATE 是在创建物化视图的时候就生成数据 BUILD DEFERRED 则在创建时不生成数据,以后根据需要再生成数据。 默认为BUILD IMMEDIATE。
(2)REFRESH FAST 增量刷新用物化视图日志,来发送主表已经修改的数据行到物化视图中。 COMPLETE 完全刷新重新生成整个视图,如果请求完全刷新,oracle会完成完全刷新即使增量刷新可用。 FORCE 如果增量刷新可用Oracle将完成增量刷新,否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or FORCE)。 默认选项是Force。
(3)ON ON DEMAND 指物化视图在用户需要的时候进行刷新。 ON COMMIT 指出物化视图在对基表的DML操作提交的同时进行刷新。 默认是ON DEMAND.
(4)START WITH 通知数据库完成从主表到本地表第一次复制的时间。
(5)NEXT 说明了刷新的时间间隔 根据下一次刷新的时间=上一次执行完成的时间+时间间隔。 为了保证在用户需要的时间点刷新,一般使用TRUNC()命令对时间取整到天数,然后加上时间。

具体示例代码如下所示:

复制代码

/* Formatted on 2012/3/28 11:26:08 (QP5 v5.149.1003.31008) */
--删除日志
TRUNCATE TABLE mlog$_fe_fee;
DROP MATERIALIZED VIEW LOG ON fe_fee;
TRUNCATE TABLE mlog$_fe_order;
DROP MATERIALIZED VIEW LOG ON fe_order;
TRUNCATE TABLE mlog$_fe_job;
DROP MATERIALIZED VIEW LOG ON fe_job;
TRUNCATE TABLE mlog$_fi_acc_bill;
DROP MATERIALIZED VIEW LOG ON fi_acc_bill;
TRUNCATE TABLE mlog$_fi_acc_fee;
DROP MATERIALIZED VIEW LOG ON fi_acc_fee;
TRUNCATE TABLE mlog$_fe_fee_age;
DROP MATERIALIZED VIEW LOG ON fe_fee_age;

--创建基表日志
CREATE MATERIALIZED VIEW LOG ON fe_fee WITH ROWID, SEQUENCE(job_id, order_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fe_order WITH ROWID, SEQUENCE( order_id)INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fe_job WITH ROWID ,SEQUENCE(job_id)INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fi_acc_bill WITH ROWID, SEQUENCE(bill_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fi_acc_fee WITH ROWID, SEQUENCE(fee_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fe_fee_age WITH ROWID, SEQUENCE(job_id, order_id) INCLUDING NEW VALUES;

--创建物化视图
DROP MATERIALIZED VIEW mv_job_fee;


CREATE MATERIALIZED VIEW mv_job_fee
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 5/(60*24)
AS
SELECT f.ROWID fi, j.ROWID ji, o.ROWID oi, b.ROWID bi, c.ROWID ci, f.fee_id,
       f.job_id, f.order_id, f.fee_type, f.fee_code, f.unit_price, f.quantity,
       f.currency, f.fx_rate, f.cust_id, f.invoice_num, f.is_confirm,
       f.blunt_flag, f.verify_balance, f.is_agreement, f.fiscal_period,
       f.attribute, f.continue, f.remark, f.security, f.create_by,
       f.create_time, f.pay_type, f.sharing_type, f.bill_id, f.direction,
       f.profit_loses, f.unit, f.relation_cust, f.amount, f.fx_amout,
       f.modified_by, f.modified_date, f.proportion, f.job_period,
       o.quantity ord_quantity, o.gross_weight ord_gross_weight,
       o.volume ord_volume, o.charge_weight ord_charge_weight,
       o.custom_num ord_custom_num, o.pay_type ord_pay_type,
       o.pay_type2 ord_pay_type2, o.teu ord_teu,
       o.cust_service ord_cust_service, o.oper ord_oper, o.bill ord_bill,
       o.sales ord_sales, o.cust_id ord_cust_id, o.bill_no  bill_no , 1  AS  ord_canvassing,1  AS ord_agent_type,
       j.dept_id job_dept_id,
       j.job_type job_job_type, j.firm job_firm, j.way_bill job_way_bill,
       j.loading job_loading, j.discharging job_discharging, j.etd job_etd,
       j.eta job_eta, j.flight_num job_flight_num, j.provider job_provider,
       j.carrier job_carrier, j.voyage job_voyage, j.quantity job_quantity,
       j.gross_weight job_gross_weight, j.volume job_volume,
       j.charge_weight job_charge_weight, j.teu job_teu,
       j.fee_lock job_fee_lock, j.lock_time job_lock_time,
       j.auditor job_auditor, j.archiveno job_archiveno,
       j.archived_by job_archived_by, j.archived_time job_archived_time,
       j.oversea_agent job_oversea_agent, j.container_info job_container_info,
       j.container_num job_container_num, j.proj_id job_proj_id,
       j.route  job_route,
       b.book_date bill_book_date, b.commit_flag bill_commit_flag,
       b.pay_period bill_pay_period, b.invoice_rise, c.confirm_amount,
       c.confirm_time
  FROM fe_fee f, fe_order o, fe_job j, fi_acc_bill b, fi_acc_fee c
 WHERE     f.job_id = j.job_id(+)
       AND f.order_id = o.order_id(+)
       AND f.bill_id = b.bill_id(+)
       AND f.fee_id = c.fee_id(+);



DROP MATERIALIZED VIEW mv_order_cargo;

CREATE MATERIALIZED VIEW mv_order_cargo
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 10/(60*24)
AS
SELECT j.ROWID ji, o.ROWID oi, o.order_id, o.job_type, o.cust_id, o.dept_id,
       o.firm, o.job_id, o.quantity, o.gross_weight, o.volume,
       o.charge_weight, o.custom_num, j.loading, j.discharging, o.pay_type,
       o.pay_type2, o.teu, o.cust_service, o.oper, o.bill, o.sales,
       o.booking_type, o.route, o.assign_agent, j.way_bill, j.etd, j.eta,
       j.flight_num, j.provider, j.carrier, j.voyage, j.quantity job_quantity,
       j.gross_weight job_gross_weight, j.volume job_volume,
       j.charge_weight job_charge_weight, j.teu job_teu, j.job_period,
       j.oversea_agent, j.container_info, j.container_num
  FROM fe_order o, fe_job j
 WHERE o.job_id = j.job_id(+);

DROP MATERIALIZED VIEW mv_fee_age;

CREATE MATERIALIZED VIEW mv_fee_age
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 5/(60*24)
AS
SELECT a.ROWID ai, f.ROWID fi, j.ROWID ji, o.ROWID oi, a.fee_id,
       a.job_id, a.order_id, f.fee_type, f.fee_code, f.unit_price, f.quantity,
       f.currency, f.fx_rate, f.cust_id, f.invoice_num, f.is_confirm,
       f.blunt_flag, f.verify_balance, f.is_agreement, f.fiscal_period,
       f.attribute, f.continue, f.remark, f.security, f.create_by,
       f.create_time, f.pay_type, f.sharing_type, f.bill_id, f.direction,
       f.profit_loses, f.unit, f.relation_cust, f.amount, f.fx_amout,
       f.modified_by, f.modified_date, f.proportion, f.job_period,
       o.quantity ord_quantity, o.gross_weight ord_gross_weight,
       o.volume ord_volume, o.charge_weight ord_charge_weight,
       o.custom_num ord_custom_num, o.pay_type ord_pay_type,
       o.pay_type2 ord_pay_type2, o.teu ord_teu,
       o.cust_service ord_cust_service, o.oper ord_oper, o.bill ord_bill,
       o.sales ord_sales, o.cust_id ord_cust_id, j.dept_id job_dept_id,
       j.job_type job_job_type, j.firm job_firm, j.way_bill job_way_bill,
       j.loading job_loading, j.discharging job_discharging, j.etd job_etd,
       j.eta job_eta, j.flight_num job_flight_num, j.provider job_provider,
       j.carrier job_carrier, j.voyage job_voyage, j.quantity job_quantity,
       j.gross_weight job_gross_weight, j.volume job_volume,
       j.charge_weight job_charge_weight, j.teu job_teu,
       j.fee_lock job_fee_lock, j.lock_time job_lock_time,
       j.auditor job_auditor, j.archiveno job_archiveno,
       j.archived_by job_archived_by, j.archived_time job_archived_time,
       j.oversea_agent job_oversea_agent, j.container_info job_container_info,
       j.container_num job_container_num, j.proj_id job_proj_id
  FROM fe_fee_age a, fe_fee f, fe_order o, fe_job j
 WHERE a.fee_id = f.fee_id(+)
 AND a.job_id = j.job_id(+)
 AND a.order_id = o.order_id(+);

本文转载自:

共有 人打赏支持
Reborn-D
粉丝 5
博文 52
码字总数 59390
作品 0
闵行
Oracle性能优化之物化视图管理_超越OCP精通Oracle视频教程培训39

Oracle性能优化之物化视图管理超越OCP精通Oracle视频教程培训39 课程介绍 风哥Oracle视频教程<>的第9/10套:Oracle性能优化之物化视图管理。物化视图(materialized view)基础概述,物化视图...

风哥Oracle ⋅ 2017/03/21 ⋅ 0

Oracle之物化视图

物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体...

车开源 ⋅ 2016/04/13 ⋅ 0

oracle物化视图的一般用法

oracle物化视图的一般用法 物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图...

程序员YB ⋅ 2011/12/21 ⋅ 0

Materialized View

物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体...

金樽清酒斗十千 ⋅ 04/05 ⋅ 0

Oracle 物化视图

一、物化视图概述 Oracle的物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。 物化...

hbxztc ⋅ 2017/08/09 ⋅ 0

普通视图和物化视图的区别

物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体...

JAVA_NINA ⋅ 2014/06/05 ⋅ 0

Oracle 物化视图日志

一、物化视图日志是什么 oracle 的物化视图的快速刷新要求必须建立物化视图日志,通过物化视图日志可以实现增量刷新功能。 官方文档给出的对物化视图日志的释义: A materialized view log ...

hbxztc ⋅ 2017/08/10 ⋅ 0

ORACLE物化视图

一、物化视图的一般用法物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL...

pczhangtl ⋅ 2014/03/30 ⋅ 0

postgresql9.5 物化视图测试

copyright http://cupegraf.com/ 视图是指数据库只存储定义该视图的查询语句(内容是查询时产生),而物化视图是一个其查询语句查询后的内容并存储的视图(内容是创建物化视图刷新视图时产生,...

廖君 ⋅ 2015/07/30 ⋅ 0

PostgreSQL 9.3物化视图使用

物化视图在Oracle里面是很早就内置的一个功能,而PostgreSQL也很早就将功能代码做出来,方式是类似create table as....,只是一直没有内置,9.3版本终于将此作为一个内置的功能点来使用,下面...

kenyon_君羊 ⋅ 2013/09/12 ⋅ 15

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Linux kernel脉络和主干总结

写在前面 前人常说,对Linux操作系统/内核的理解,是计算机行业从业者的内功,决定了你在技术领域想走多远。但内核的庞大以及学习曲线之陡峭,总让我在学习途中觉得犹如“管中窥豹”。 随着工...

Markz0928 ⋅ 10分钟前 ⋅ 0

在gcc中使用intel风格的内联汇编

很简单,内联汇编使用asm(“.intel_syntax noprefix/n”)声明一下,以后的内联汇编就可以用intel风格了,构建可执行文件时给gcc加上-masm=intel参数。 先写一个小程序测试一下: [cpp] view...

simpower ⋅ 21分钟前 ⋅ 0

NIO 之 ByteBuffer实现原理

相关文章 BIO、NIO、AIO 内部原理分析 NIO 之 Selector实现原理 NIO 之 Channel实现原理 前言 Java NIO 主要由下面3部分组成: Buffer Channel Selector 在传统IO中,流是基于字节的方式进行...

轨迹_ ⋅ 30分钟前 ⋅ 0

Jenkins docker权限问题

环境Ubuntu Server 工具 jenkins-war:2.89.2 报错信息 Cannot connect to the Docker daemon. Is the docker daemon running on this host?Build step 'Execute shell' marked build as fai......

Pulsar-V ⋅ 30分钟前 ⋅ 0

180621-一个简单的时间窗口设计与实现

如何设计一个计数的时间窗口 时间窗口,通常对于一些实时信息展示中用得比较多,比如维持一个五分钟的交易明细时间窗口,就需要记录当前时间,到五分钟之前的所有交易明细,而五分钟之前的数...

小灰灰Blog ⋅ 53分钟前 ⋅ 0

Android之Dalvik、ART、JIT、AOT

Android之Dalvik、ART、JIT、AOT 本文内容:Dalvik、ART、JIT、AOT之间关系 本文定位:知识记录 学习过程记录,加深理解,提升文字组合表达能力。也希望能给学习的同学一些灵感 本文整理于[...

lichuangnk ⋅ 57分钟前 ⋅ 0

Thrift RPC实战(五) thrift连接池

Thrift本身没有提供连接池,我们可以用Apache Commons Pool2来实现一个 一、定义对象工厂 BasePooledObjectFactory<T> extends BaseObject implements PooledObjectFactory<T> public class......

lemonLove ⋅ 57分钟前 ⋅ 0

git 命令简写

简写 命令 g git gst git status gd git diff gdc git diff --cached gdv git diff -w "$@" | view - gl git pull gup git pull --rebase gp git push gc git commit -v gc! git commit -v ......

charley158 ⋅ 今天 ⋅ 0

Java中的锁使用与实现

1.Lock接口 锁是用来控制多个线程访问共享资源的方式,一般来说,一个锁能够防止多个线程同时访问共享资源。 在Lock出现之前,java程序是靠synchronized关键字实现锁功能的,而Java SE5之后,...

ZH-JSON ⋅ 今天 ⋅ 0

Intellij IDEA神器常用技巧四-类和方法注释模板设置

IDEA自带的注释模板不是太好用,我本人到网上搜集了很多资料系统的整理了一下制作了一份比较完整的模板来分享给大家,我不是专业玩博客的,写这篇文章只是为了让大家省事。 这里设置的注释模...

Mkeeper ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部