查看: 2117|回复: 6

大家帮我看看 这个语句怎么优化??谢谢

[复制链接]
论坛徽章:
0
发表于 2010-6-9 15:56 | 显示全部楼层 |阅读模式
这个maht表只有17万条数据。。。。。。。

SELECT MAH1.SENDERBIC,
MAH1.SENDERREF,
MAH1.REFSEQ,
MAH1.REFDATE,
MAH2.SENDERBIC,
MAH2.SENDERREF,
MAH2.REFSEQ,
MAH2.REFDATE,
MAH2.MATCHSTATUS,
MAH2.CDATE,
MAH2.CTIME,
MAH2.INOUTIND,
MAH2.BR,
MAH2.MATCHSENDERBIC,
MAH2.MATCHSENDERREF,
MAH2.MATCHREFSEQ,
MAH2.MATCHREFDATE,
MAH2.OPERATIONTYPE,
MAH1.OPERATIONTYPE,
MAH2.REF7
FROM maht MAH1, maht MAH2
WHERE
     ((MAH1.BR = '01'
       AND MAH1.INOUTIND='I')
       OR
      (MAH1.BR IN ('01')
       AND
      MAH1.INOUTIND='O'))
AND
     ((MAH2.BR = '01'
     AND MAH2.INOUTIND='I')
     OR
     (MAH2.BR IN ('01')
     AND MAH2.INOUTIND='O'))
AND MAH1.MATCHTYPE = 'CONF'
AND MAH1.MATCHLEVEL = '300'
AND MAH1.BR = MAH2.BR
AND MAH1.INOUTIND =MAH2.INOUTIND
AND MAH1.MATCHTYPE = MAH2.MATCHTYPE
AND MAH1.MATCHLEVEL = MAH2.MATCHLEVEL
AND MAH1.LINKFIELD = MAH2.SENDERREF
AND MAH1.OPERATIONTYPE IN ('CANC', 'AMND', 'DUPL')
AND MAH1.REPLACESENDERBIC  IS NULL
AND MAH1.REPLACESENDERREF IS NULL
AND MAH1.REPLACEREFSEQ  IS NULL
AND MAH1.REPLACEREFDATE IS NULL
AND
     ((MAH1.LINKFIELD <> MAH2.LINKFIELD)
     OR (MAH1.LINKFIELD IS NULL  AND MAH2.LINKFIELD IS NOT NULL )
     OR (MAH1.LINKFIELD IS NOT NULL  AND MAH2.LINKFIELD IS NULL ))
AND MAH1.REFSEQ = '00'
AND MAH2.REFSEQ = '00'
AND MAH1.LINKFIELD <> MAH1.SENDERREF
AND MAH2.MATCHSTATUS NOT IN ('O', 'R')
UNION ALL
SELECT MAH1.SENDERBIC,
MAH1.SENDERREF,
MAH1.REFSEQ,
MAH1.REFDATE,
MAH2.SENDERBIC,
MAH2.SENDERREF,
MAH2.REFSEQ,
MAH2.REFDATE,
MAH2.MATCHSTATUS,
MAH2.CDATE,
MAH2.CTIME,
MAH2.INOUTIND,
MAH2.BR,
MAH2.MATCHSENDERBIC,
MAH2.MATCHSENDERREF,
MAH2.MATCHREFSEQ,
MAH2.MATCHREFDATE,
MAH2.OPERATIONTYPE,
MAH1.OPERATIONTYPE,
MAH2.REF7
FROM maht MAH1, maht MAH2
WHERE
   ((MAH1.BR = '01'
     AND MAH1.INOUTIND='I')
     OR
   (MAH1.BR IN ('01')
     AND MAH1.INOUTIND='O'))
AND
  ((MAH2.BR = '01'
     AND MAH2.INOUTIND='I')
     OR
  (MAH2.BR IN ('01')
     AND MAH2.INOUTIND='O'))
AND MAH1.MATCHTYPE = 'CONF'
AND MAH1.MATCHLEVEL = '300'
AND MAH1.BR= MAH2.BR
AND MAH1.INOUTIND = MAH2.INOUTIND
AND MAH1.MATCHTYPE = MAH2.MATCHTYPE
AND MAH1.MATCHLEVEL = MAH2.MATCHLEVEL
AND MAH1.LINKFIELD = MAH2.SENDERREF
AND MAH1.OPERATIONTYPE IN ('CANC', 'AMND', 'DUPL')
AND MAH1.REPLACESENDERBIC  IS NULL
AND MAH1.REPLACESENDERREF  IS NULL
AND MAH1.REPLACEREFSEQ  IS NULL
AND MAH1.REPLACEREFDATE IS NULL
AND MAH1.LINKFIELD = MAH1.SENDERREF
AND MAH1.REFSEQ <> MAH2.REFSEQ
AND MAH2.MATCHSTATUS NOT IN ('O', 'R')
ORDER BY 5,6,7,10,11




TABLE_NAME           INDEX_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
MAHT                 MAHT1IDX             BR
MAHT                 MAHT1IDX             MATCHTYPE
MAHT                 MAHT1IDX             MATCHLEVEL
MAHT                 MAHT1IDX             INOUTIND
MAHT                 MAHT1IDX             MATCHSTATUS
MAHT                 MAHTPK               BR
MAHT                 MAHTPK               SENDERBIC
MAHT                 MAHTPK               SENDERREF
MAHT                 MAHTPK               REFSEQ
MAHT                 MAHTPK               REFDATE
MAHT                 MAHTPK               INOUTIND
MAHT                 MAHTPK               MATCHTYPE
MAHT                 MAHTPK               MATCHLEVEL

13 rows selected.




Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     UNION-ALL
   3    2       CONCATENATION
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
   7    6               INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)
   8    5             INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)
   9    3         TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
  10    9           NESTED LOOPS
  11   10             TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
  12   11               INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)
  13   10             INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)
  14    2       CONCATENATION
  15   14         TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
  16   15           NESTED LOOPS
  17   16             TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
  18   17               INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)
  19   16             INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)
  20   14         TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
  21   20           NESTED LOOPS
  22   21             TABLE ACCESS (BY INDEX ROWID) OF 'MAHT'
  23   22               INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)
  24   21             INDEX (RANGE SCAN) OF 'MAHT1IDX' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    6572896  consistent gets
          0  physical reads
        120  redo size
       3345  bytes sent via SQL*Net to client
       1231  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed

[ 本帖最后由 yaok2004 于 2010-6-9 16:02 编辑 ]
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:142013年新春福章
日期:2013-02-25 14:51:24
发表于 2010-6-9 16:50 | 显示全部楼层
SELECT /*+use_hash(MAH1 MAH2) */ MAH1.SENDERBIC,MAH1.SENDERREF,MAH1.REFSEQ,MAH1.REFDATE,MAH2.SENDERBIC,
        MAH2.SENDERREF,MAH2.REFSEQ,MAH2.REFDATE,MAH2.MATCHSTATUS,MAH2.CDATE,MAH2.CTIME,
        MAH2.INOUTIND,MAH2.BR,MAH2.MATCHSENDERBIC,MAH2.MATCHSENDERREF,MAH2.MATCHREFSEQ,
        MAH2.MATCHREFDATE,MAH2.OPERATIONTYPE,MAH1.OPERATIONTYPE,MAH2.REF7
FROM
maht MAH1, maht MAH2
WHERE        ((MAH1.BR = '01'
AND        MAH1.INOUTIND='I') OR (MAH1.BR IN ('01')
AND        MAH1.INOUTIND='O'))
AND        ((MAH2.BR = '01'
AND        MAH2.INOUTIND='I') OR (MAH2.BR IN ('01')
AND        MAH2.INOUTIND='O'))
AND        MAH1.MATCHTYPE = 'CONF'
AND        MAH1.MATCHLEVEL = '300'
AND        MAH1.BR = MAH2.BR
AND        MAH1.INOUTIND =MAH2.INOUTIND
AND        MAH1.MATCHTYPE = MAH2.MATCHTYPE
AND        MAH1.MATCHLEVEL = MAH2.MATCHLEVEL
AND        MAH1.LINKFIELD = MAH2.SENDERREF
AND        MAH1.OPERATIONTYPE IN ('CANC', 'AMND', 'DUPL')
AND        MAH1.REPLACESENDERBIC IS NULL
AND        MAH1.REPLACESENDERREF IS NULL
AND        MAH1.REPLACEREFSEQ IS NULL
AND        MAH1.REPLACEREFDATE IS NULL
AND        ((MAH1.LINKFIELD <> MAH2.LINKFIELD) OR (MAH1.LINKFIELD IS NULL
AND        MAH2.LINKFIELD IS NOT NULL ) OR (MAH1.LINKFIELD IS NOT NULL
AND        MAH2.LINKFIELD IS NULL ))
AND        MAH1.REFSEQ = '00'
AND        MAH2.REFSEQ = '00'
AND        MAH1.LINKFIELD <> MAH1.SENDERREF
AND        MAH2.MATCHSTATUS NOT IN ('O', 'R')
UNION ALL
SELECT /*+use_hash(MAH1 MAH2) */ H1.SENDERBIC,MAH1.SENDERREF,MAH1.REFSEQ,MAH1.REFDATE,MAH2.SENDERBIC,
        MAH2.SENDERREF,MAH2.REFSEQ,MAH2.REFDATE,MAH2.MATCHSTATUS,MAH2.CDATE,MAH2.CTIME,
        MAH2.INOUTIND,MAH2.BR,MAH2.MATCHSENDERBIC,MAH2.MATCHSENDERREF,MAH2.MATCHREFSEQ,
        MAH2.MATCHREFDATE,MAH2.OPERATIONTYPE,MAH1.OPERATIONTYPE,MAH2.REF7
FROM
maht MAH1, maht MAH2
WHERE        ((MAH1.BR = '01'
AND        MAH1.INOUTIND='I') OR (MAH1.BR IN ('01')
AND        MAH1.INOUTIND='O'))
AND        ((MAH2.BR = '01'
AND        MAH2.INOUTIND='I') OR (MAH2.BR IN ('01')
AND        MAH2.INOUTIND='O'))
AND        MAH1.MATCHTYPE = 'CONF'
AND        MAH1.MATCHLEVEL = '300'
AND        MAH1.BR= MAH2.BR
AND        MAH1.INOUTIND = MAH2.INOUTIND
AND        MAH1.MATCHTYPE = MAH2.MATCHTYPE
AND        MAH1.MATCHLEVEL = MAH2.MATCHLEVEL
AND        MAH1.LINKFIELD = MAH2.SENDERREF
AND        MAH1.OPERATIONTYPE IN ('CANC', 'AMND', 'DUPL')
AND        MAH1.REPLACESENDERBIC IS NULL
AND        MAH1.REPLACESENDERREF IS NULL
AND        MAH1.REPLACEREFSEQ IS NULL
AND        MAH1.REPLACEREFDATE IS NULL
AND        MAH1.LINKFIELD = MAH1.SENDERREF
AND        MAH1.REFSEQ <> MAH2.REFSEQ
AND        MAH2.MATCHSTATUS NOT IN ('O', 'R')
ORDER BY 5,6,7,10,11

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-6-9 17:02 | 显示全部楼层
CONCATENATION, 走这个算ORACLE还比较聪明,我不确定HASH是否更快,楼主可以自己测试。

但是我建议把CONCATENATION的地方用UNION来写。 这里ORACLE虽然帮你走了CONCATENATION,但是这种计划很容易变,最好人为拆开。之后再确定是否HASH更好,或者其他因素。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2010-6-9 17:03 | 显示全部楼层
谢谢 如果不改变应用的话 而且库是9i的 还有办法么?

[ 本帖最后由 yaok2004 于 2010-6-9 17:06 编辑 ]

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-6-9 17:47 | 显示全部楼层
不改变应用是指不修改SQL?

我没办法。ORACLE优化器还不足够聪明,否者HINT要来干嘛?

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
发表于 2010-6-9 23:01 | 显示全部楼层
6572896  consistent gets --逻辑多

但是没有调试环境,比较难于测试。

使用道具 举报

回复
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:142013年新春福章
日期:2013-02-25 14:51:24
发表于 2010-6-10 08:36 | 显示全部楼层
用CBO试试看
原帖由 yaok2004 于 2010-6-9 17:03 发表
谢谢 如果不改变应用的话 而且库是9i的 还有办法么?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表