程序博客网 > 网站seo公司

SQL 优化之 oracle物化视图

来源：互联网 发布：网站seo公司 编辑：程序博客网 时间：2024/05/22 05:14

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

    供自己学习和借鉴……

   可惜的是 也看的很少……

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

   目前最大的工作就是优化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秒……

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

 

所以很耗费资源……

恩……

 

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

网站seo公司 网站seo公司
原创粉丝点击
热门IT博客
淘宝人群标签怎么看淘宝人群标签怎么看
河北广电网络集团电话河北广电网络集团电话
大数据会烂大街吗
35互联域名转出
java 破解图形验证码
广告纸设计软件
粤贵宝交易软件
java 日历类
mac可以用手绘板吗
scipy python
淘宝香港it代购真假
jq 获取表单数据
电子病历数据库 下载
三拼音域名
大数据与业务系统架构
mac excel 日历控件
凸包问题java
快递入库软件app
java编写九九乘法表
js定义固定长度的数组
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 单相深井潜水泵 深井泵和潜水泵的区别 博山深井泵 深井泵安装示意图 深井泵无塔供水 深井泵转子 深井潜水泵哪个品牌好 锦霸深井泵 深井泵控制 重庆水泵 深井盐 男生深亚麻 亚麻深棕色 深交 深交所将暂停长生生物上市 深交所创业板 深交所大宗交易 深交所逆回购 深交所股票代码 深交所大宗交易规则 深交所大宗交易系统 深交所上市的公司 深交所互动 深交所上市 深交所股票交易费用 深交所新规 证券交易所和证券公司的区别 深圳市证券交易斦 深圳股票交易所 中国证券交易所 深圳证交所 广东交易所 下载上海证券交易所 深市股票行情 上海证券排名 一入侯门深似海 春深似海 侯门深似海 深似海 仇深似海 恩深似海

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里