SQL 优化之 oracle物化视图

原创 2006年05月18日 12:51:00

     在这里,其实大多都是我摘抄的参考资料……

    供自己学习和借鉴……

   可惜的是 也看的很少……

   塔里木物流系统实施快三个月了,灾难的生活也半年了……

   目前最大的工作就是优化sql……

 

  这次,北京派来个真正的dba……

 

 学会了物化视图!感觉很方便……

 

oracle物化视图
                                      


物化视图对于前台数据库使用者来说如同一个实际的表,具有和表相通的一般select操作,而其实际上是一个视图,一个定期刷新数据的视图(具体刷新时间在定义物化视图的时候已有定义),使用物化视图可以实现视图的所有功能,而物化视图确不是在使用时才读取,大大提高了读取速度,特别适用抽取大数据量表某些信息以及数据链连接表使用.具体语法如下:

CREATE MATERIALIZED VIEW an_user_base_file_no_charge
             REFRESH COMPLETE START WITH SYSDATE 
            NEXT  TRUNC(SYSDATE+29)+5.5/24
   as
select distinct user_no
  from cw_arrearage t
 where (t.mon = dbms_tianjin.getLastMonth or
       t.mon = add_months(dbms_tianjin.getLastMonth, -1))

drop materialized view an_user_base_file_no_charge;

 

比如一个很复杂的sql

select tt.CGFS       CGFS,
       tt.XQJHID     XQJHID,
       tt.XQJHBH     XQJHBH,
       tt.YHZBH      YHZBH,
       tt.TMBH       TMBH,
       tt.JHLX       JHLX,
       tt.JHSXBH     JHSXBH,
       tt.SXRQ       SXRQ,
       tt.JYHSFSBH   JYHSFSBH,
       tt.XMBH       XMBH,
       tt.XMMC       XMMC,
       tt.WZSXBH     WZSXBH,
       tt.GHQDBH     GHQDBH,
       tt.JHLBBH     JHLBBH,
       tt.ZZSXBH     ZZSXBH,
       tt.ZCZJLYBH   ZCZJLYBH,
       tt.SFCG       SFCG,
       tt.SFDL       SFDL,
       tt.CGDWBH     CGDWBH,
       tt.CGDWMC     CGDWMC,
       tt.SSDWBH     SSDWBH,
       tt.SSDWMC     SSDWMC,
       tt.DLRGWBH    DLRGWBH,
       tt.DLRGWMC    DLRGWMC,
       tt.DLRBH      DLRBH,
       tt.DLRMC      DLRMC,
       tt.DLRQ       DLRQ,
       tt.ZDGWBH     ZDGWBH,
       tt.ZDGWMC     ZDGWMC,
       tt.ZDRBH      ZDRBH,
       tt.ZDRMC      ZDRMC,
       tt.CGFAJHZBH  CGFAJHZBH,
       tt.CGFATBRQ   CGFATBRQ,
       tt.ZDRQ       ZDRQ,
       tt.BZ         BZ,
       tt.GSJE       GSJE,
       tt.JHXS       JHXS,
       tt.cgfagysbh  cgfagysbh,
       tt.cgfagysmc  cgfagysmc,
       tt.cgfajhbh   cgfajhbh,
       tt.cgfatbdwbh cgfatbdwbh,
       tt.cgfatbdwmc cgfatbdwmc,
       tt.mxzt       mxzt,
       tt.xqjhmc     xqjhmc,
       tt.SXKZZT     SXKZZT,
       tt.bjdbz      bjdbz
  from (select cc.cgfs, cc.mxzt, ab.*
          from (select distinct c.xqjhid, a.cgfs, a.mxzt
                  from jh_b_xqjhmx a, jh_b_jhzdmxdy c
                 where 1 = 1
                   and a.xqjhmxbh = c.ysjhmxbh
                   and ((a.mxzt >= 28 and a.mxzt < 200) or a.mxzt in (12, 13))
                   and (a.flgw2 like '01010109%' escape '/')) cc
         inner join (select e.XQJHID     XQJHID,
                           e.sxkzzt     sxkzzt,
                           e.XQJHBH     XQJHBH,
                           e.xqjhmc     xqjhmc,
                           e.YHZBH      YHZBH,
                           e.TMBH       TMBH,
                           e.JHLX       JHLX,
                           e.JHSXBH     JHSXBH,
                           e.SXRQ       SXRQ,
                           e.JYHSFSBH   JYHSFSBH,
                           e.XMBH       XMBH,
                           e.XMMC       XMMC,
                           e.WZSXBH     WZSXBH,
                           e.GHQDBH     GHQDBH,
                           e.JHLBBH     JHLBBH,
                           e.ZZSXBH     ZZSXBH,
                           e.ZCZJLYBH   ZCZJLYBH,
                           e.SFCG       SFCG,
                           e.SFDL       SFDL,
                           e.CGDWBH     CGDWBH,
                           e.CGDWMC     CGDWMC,
                           e.SSDWBH     SSDWBH,
                           e.SSDWMC     SSDWMC,
                           e.DLRGWBH    DLRGWBH,
                           e.DLRGWMC    DLRGWMC,
                           e.DLRBH      DLRBH,
                           e.DLRMC      DLRMC,
                           e.DLRQ       DLRQ,
                           e.CGFAJHZBH  CGFAJHZBH,
                           e.CGFATBRQ   CGFATBRQ,
                           e.ZDGWBH     ZDGWBH,
                           e.ZDGWMC     ZDGWMC,
                           e.ZDRBH      ZDRBH,
                           e.ZDRMC      ZDRMC,
                           e.ZDRQ       ZDRQ,
                           e.BZ         BZ,
                           e.GSJE       GSJE,
                           e.JHXS       JHXS,
                           e.cgfagysbh  cgfagysbh,
                           e.cgfagysmc  cgfagysmc,
                           e.cgfajhbh   cgfajhbh,
                           e.cgfatbdwbh cgfatbdwbh,
                           e.cgfatbdwmc cgfatbdwmc,
                           ttt.bjdbz    bjdbz
                      from jh_b_xqjh e,
                           (select distinct n.cgfabh, m.bjdbz
                              from cg_b_xbjd m, cg_b_xbjdmx n
                             where m.xjdbh = n.xjdbh) ttt
                     where jhlx != 1
                       and jhlx != 2
                       and e.xqjhbh = ttt.cgfabh(+)) ab on cc.xqjhid =
                                                           ab.xqjhid) tt
 order by tt.mxzt, tt.CGFATBRQ desc
调整成
select tt.CGFS       CGFS,
       tt.XQJHID     XQJHID,
       tt.XQJHBH     XQJHBH,
       tt.YHZBH      YHZBH,
       tt.TMBH       TMBH,
       tt.JHLX       JHLX,
       tt.JHSXBH     JHSXBH,
       tt.SXRQ       SXRQ,
       tt.JYHSFSBH   JYHSFSBH,
       tt.XMBH       XMBH,
       tt.XMMC       XMMC,
       tt.WZSXBH     WZSXBH,
       tt.GHQDBH     GHQDBH,
       tt.JHLBBH     JHLBBH,
       tt.ZZSXBH     ZZSXBH,
       tt.ZCZJLYBH   ZCZJLYBH,
       tt.SFCG       SFCG,
       tt.SFDL       SFDL,
       tt.CGDWBH     CGDWBH,
       tt.CGDWMC     CGDWMC,
       tt.SSDWBH     SSDWBH,
       tt.SSDWMC     SSDWMC,
       tt.DLRGWBH    DLRGWBH,
       tt.DLRGWMC    DLRGWMC,
       tt.DLRBH      DLRBH,
       tt.DLRMC      DLRMC,
       tt.DLRQ       DLRQ,
       tt.ZDGWBH     ZDGWBH,
       tt.ZDGWMC     ZDGWMC,
       tt.ZDRBH      ZDRBH,
       tt.ZDRMC      ZDRMC,
       tt.CGFAJHZBH  CGFAJHZBH,
       tt.CGFATBRQ   CGFATBRQ,
       tt.ZDRQ       ZDRQ,
       tt.BZ         BZ,
       tt.GSJE       GSJE,
       tt.JHXS       JHXS,
       tt.cgfagysbh  cgfagysbh,
       tt.cgfagysmc  cgfagysmc,
       tt.cgfajhbh   cgfajhbh,
       tt.cgfatbdwbh cgfatbdwbh,
       tt.cgfatbdwmc cgfatbdwmc,
       tt.mxzt       mxzt,
       tt.xqjhmc     xqjhmc,
       tt.SXKZZT     SXKZZT,
       tt.bjdbz      bjdbz
  from mv_test tt
 order by tt.mxzt, tt.CGFATBRQ desc
就可以了。]

mv_test tt

 

create materialized view MV_TEST
refresh force on demand
as
select cc.cgfs, cc.mxzt, ab.*
          from (select distinct c.xqjhid, a.cgfs, a.mxzt
                  from wz_wlpt.jh_b_xqjhmx a, wz_wlpt.jh_b_jhzdmxdy c
                 where 1 = 1
                   and a.xqjhmxbh = c.ysjhmxbh
                   and ((a.mxzt >= 28 and a.mxzt < 200) or a.mxzt in (12, 13))
                   and (a.flgw2 like '01010109%' escape '/')) cc
         inner join (select e.XQJHID     XQJHID,
                           e.sxkzzt     sxkzzt,
                           e.XQJHBH     XQJHBH,
                           e.xqjhmc     xqjhmc,
                           e.YHZBH      YHZBH,
                           e.TMBH       TMBH,
                           e.JHLX       JHLX,
                           e.JHSXBH     JHSXBH,
                           e.SXRQ       SXRQ,
                           e.JYHSFSBH   JYHSFSBH,
                           e.XMBH       XMBH,
                           e.XMMC       XMMC,
                           e.WZSXBH     WZSXBH,
                           e.GHQDBH     GHQDBH,
                           e.JHLBBH     JHLBBH,
                           e.ZZSXBH     ZZSXBH,
                           e.ZCZJLYBH   ZCZJLYBH,
                           e.SFCG       SFCG,
                           e.SFDL       SFDL,
                           e.CGDWBH     CGDWBH,
                           e.CGDWMC     CGDWMC,
                           e.SSDWBH     SSDWBH,
                           e.SSDWMC     SSDWMC,
                           e.DLRGWBH    DLRGWBH,
                           e.DLRGWMC    DLRGWMC,
                           e.DLRBH      DLRBH,
                           e.DLRMC      DLRMC,
                           e.DLRQ       DLRQ,
                           e.CGFAJHZBH  CGFAJHZBH,
                           e.CGFATBRQ   CGFATBRQ,
                           e.ZDGWBH     ZDGWBH,
                           e.ZDGWMC     ZDGWMC,
                           e.ZDRBH      ZDRBH,
                           e.ZDRMC      ZDRMC,
                           e.ZDRQ       ZDRQ,
                           e.BZ         BZ,
                           e.GSJE       GSJE,
                           e.JHXS       JHXS,
                           e.cgfagysbh  cgfagysbh,
                           e.cgfagysmc  cgfagysmc,
                           e.cgfajhbh   cgfajhbh,
                           e.cgfatbdwbh cgfatbdwbh,
                           e.cgfatbdwmc cgfatbdwmc,
                           ttt.bjdbz    bjdbz
                      from wz_wlpt.jh_b_xqjh e,
                           (select distinct n.cgfabh, m.bjdbz
                              from wz_wlpt.cg_b_xbjd m, wz_wlpt.cg_b_xbjdmx n
                             where m.xjdbh = n.xjdbh) ttt
                     where jhlx != 1
                       and jhlx != 2
                       and e.xqjhbh = ttt.cgfabh(+)) ab on cc.xqjhid =ab.xqjhid

 

 

这样原来的sql执行 要用1.433秒多,而使用物化视图执行才0.2秒……

但也不能多用,因为他需要实时来刷新视图才能保持和基表的一致……

 

所以很耗费资源……

恩……

 

不管怎么样,又学了点东西……

用DBMS_ADVISOR.SQLACCESS_ADVISOR创建SQL Access Advisor访问优化建议

用DBMS_ADVISOR.SQLACCESS_ADVISOR创建SQL Access Advisor访问优化建议
  • ljunjie82
  • ljunjie82
  • 2013年09月15日 20:12
  • 1241

SQL:SQL Access Advisor.Quick Tune

一、功能:        Materialized views, partitions, and indexes are essential when tuning a database to ac...
  • u010719917
  • u010719917
  • 2016年07月23日 09:53
  • 328

Oracle使用物化视图提高group by性能

情况介绍 现有表OE.PRODUCT_INFORMATION数据量为800万,求出各个供应商供应产品的数量 无使用物化视图,只在建立SUPPLIER_ID建立索引,执行时间为15s左右SELECT ...
  • u012557814
  • u012557814
  • 2017年01月04日 18:44
  • 1076

oracle笔记整理10——性能调优之临时表与物化视图

1. 临时表 1) 概念 a) 临时表跟永久表最大的区别就是表中的数据不会永远的存在 b) Oracle临时表分为会话级临时表和事务级临时表。 c) 会话临时表,结束或中断会话时清空数据。 cr...
  • thinkpadshi
  • thinkpadshi
  • 2016年01月14日 11:28
  • 2282

ORACLE物化视图-高速高效 创建物化视图提高查询速度

你是否为等待你的查询返回结果而感到疲惫?你是否已经为增强索引和调优SQL而感到疲惫,但仍然不能提高查询性能?那么,你是否已经考虑创建物化视图?有了物化视图,那些过去需要数小时运行的报告可以在几分钟内完...
  • outget1
  • outget1
  • 2010年02月02日 11:03
  • 3488

ORACLE物化视图创建实例

公司的项目中遇到需要从其它系统的数据库中取数据进行统计分析的问题,初步选择使用ORACLE的ODI工具进行抽数,但是对方提供的数据库用户下没有任何对象,只是有查询所有表的权限,因此无法做数据反向。于是...
  • zhangchu_63
  • zhangchu_63
  • 2010年04月29日 17:25
  • 10762

创建物化视图详解(图解)

创建物化视图详解                  一,什么是物化视图     物化视图是包括一个查询结果的数据库对象,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储...
  • wk843620202
  • wk843620202
  • 2014年10月03日 19:57
  • 5854

oracle中Job定期执行存储过程刷新物化视图并记录异常(我的物化视图不能自己刷)

最近一个项目,我们的系统中需要处理老数据,但是有一些客观限制:(都是Oracle11.2.0.2) (1)这些老数据存储在人家的数据库中 (2)这些老数据还会持续更新 (3)不能动人家的数据库 (4)...
  • jiq408694711
  • jiq408694711
  • 2013年12月08日 01:03
  • 1522

Oracle--大数据量创建 物化视图

cha CREATE MATERIALIZED VIEW MV_BUET_BUSINESSEVENT  REFRESH FAST START WITH SYSDATE   NEXT  SYSDA...
  • mickey_gezi
  • mickey_gezi
  • 2014年05月23日 14:43
  • 970

oracle物化视图的刷新机制

 Oracle在刷新的时候,先进行删除操作,然后是更新和插入操作;对于更新和插入操作,Oracle是按照主键顺序进行的;Oracle采用行级批量更新的方式来提高效率;Oracle会过滤掉同一条记录的相...
  • andfind
  • andfind
  • 2007年06月21日 10:40
  • 1363
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL 优化之 oracle物化视图
举报原因:
原因补充:

(最多只允许输入30个字)