查看: 4278|回复: 7

关于NOT EXISTS的SQL优化

[复制链接]
论坛徽章:
18
CTO参与奖
日期:2009-01-15 11:42:46奥运纪念徽章
日期:2012-11-28 09:33:31奥运纪念徽章
日期:2012-11-22 14:39:55灰彻蛋
日期:2012-05-15 14:37:582011新春纪念徽章
日期:2011-02-18 11:43:32生肖徽章2007版:兔
日期:2011-01-20 12:58:492011新春纪念徽章
日期:2011-01-04 10:35:48数据库板块每日发贴之星
日期:2010-12-09 01:01:012010广州亚运会纪念徽章:帆船
日期:2010-12-06 11:01:472010广州亚运会纪念徽章:曲棍球
日期:2010-12-06 10:59:28
发表于 2010-11-26 13:27 | 显示全部楼层 |阅读模式
帮忙优化一下下面的SQL,有两个表进行了全表扫描,怎么加索引合适呢
还有NOT EXISTS的用法效率怎么样?有没有更好的办法,如何修改SQL?
谢谢

SELECT A.INTERFACE_ID                                                 INTERFACE_ID
         , DECODE(A.SEND_PTNR_CD,'040740','031953',A.SEND_PTNR_CD)        SENDER_ABBR
         , A.RECV_PTNR_CD                                                 RECEIVE_ABBR
         , A.INVOICE_NO                                                   INVOICE_NO
         , DECODE(A.BIZ_UNIT_CD,'300','100',A.BIZ_UNIT_CD)                BIZ_UNIT_CD
         , A.EXIM_TYPE                                                    EXIM_TYPE
         , A.HBL_NO                                                       HBL_NO
         , A.MBL_NO                                                       MBL_NO
         , A.ONBOARD_DT                                                   ONBOARD_DATE
         , DECODE(A.ARR_DT, NULL, A.ONBOARD_DT, A.ARR_DT)                 ARRIVAL_DATE
         , A.INCOTERMS                                                    INCOTERMS
         , A.SHPR_NM                                                      SHIPP_NM
         , A.CARRIER_NM                                                   CARRIER_NM
         , A.ISSUE_DT                                                     ISSUE_DT
         , A.ISSUER                                                       ISSUER
         , A.ISSUER_TEL_NO                                                ISSUER_TEL_NO
         , A.ISSUER_MAIL_ID                                               ISSUER_MAIL_ID
         , A.HLDS_YN                                                      HLDS_YN
         , DECODE(A.EXIM_TYPE,'30','30',A.COMBINE_TS_TYPE)                COMBINE_TS_TYPE
         , A.BIZ_UNIT_CD                                                  OLD_BIZ_UNIT_CD
                     , A.DUE_DATE
                     , A.AMD_DUE_DATE
      FROM TB_INT021  A
     WHERE  ((null IS NULL AND 1 = 1) OR (null IS NOT NULL AND A.INTERFACE_ID = null))
        AND NOT EXISTS (SELECT 'T'
                          FROM mngfns.TB_DLA102 b
                         WHERE b.edi_inv_no       = a.invoice_no
                           AND b.inv_cust_cd      = a.send_ptnr_cd
                       )
        AND NOT EXISTS (SELECT 'T'
                          FROM mngfns.TB_DLA102_ptnr b
                         WHERE b.edi_inv_no       = a.invoice_no
                           AND b.inv_cust_cd      = a.send_ptnr_cd)
                       
       AND NOT EXISTS (SELECT 'T'
                     FROM tb_int023 b
                    WHERE b.invoice_no  = a.invoice_no
                      AND b.hbl_no      = a.hbl_no
                      AND b.result_type = 'REJECT');

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5583 Card=340 Bytes=
          86020)

   1    0   FILTER
   2    1     NESTED LOOPS (ANTI) (Cost=4903 Card=340 Bytes=86020)
   3    2       HASH JOIN (ANTI) (Cost=3075 Card=457 Bytes=110137)
   4    3         TABLE ACCESS (FULL) OF 'TB_INT021' (Cost=1708 Card=7  2471 Bytes=15798678)
   5    3         TABLE ACCESS (FULL) OF 'TB_DLA102_PTNR' (Cost=997 Card=218970 Bytes=5036310)
   6    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_DLA102' (Cost=4 Card=1245990 Bytes=14951880)
   7    6         INDEX (RANGE SCAN) OF 'IX_DLA102_17' (NON-UNIQUE) (Cost=2 Card=3)
   8    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_INT023' (Cost=2 Card=1 Bytes=29)
   9    8       INDEX (RANGE SCAN) OF 'IDX_INVOICE_NO_INT023' (NON-UNIQUE) (Cost=1 Card=5632)

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
      66502  consistent gets
      51479  physical reads
        308  redo size
     855627  bytes sent via SQL*Net to client
       3395  bytes received via SQL*Net from client
        252  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3735  rows processed
论坛徽章:
27
数据库板块每日发贴之星
日期:2010-06-17 01:01:07迷宫蛋
日期:2011-07-07 15:25:46紫蛋头
日期:2011-08-10 10:31:56ITPUB十周年纪念徽章
日期:2011-09-27 16:33:28ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222012新春纪念徽章
日期:2012-02-07 09:59:35ITPUB知识分享者
日期:2012-02-20 17:49:25铁扇公主
日期:2012-02-21 15:02:40ITPUB年度最佳BLOG写作奖
日期:2012-03-13 17:09:53ITPUB 11周年纪念徽章
日期:2012-10-09 18:14:48
发表于 2010-11-26 14:18 | 显示全部楼层
全表扫描出现在 no exists 子句里面
SELECT 'T'
FROM mngfns.TB_DLA102 b
WHERE b.edi_inv_no       = a.invoice_no
AND b.inv_cust_cd      = a.send_ptnr_cd

SELECT 'T'
  FROM mngfns.TB_DLA102_ptnr b
WHERE b.edi_inv_no       = a.invoice_no
AND b.inv_cust_cd      = a.send_ptnr_cd

lz 看看那两个表的edi_inv_no 能不能加索引。。

使用道具 举报

回复
论坛徽章:
1
生肖徽章2007版:狗
日期:2009-04-24 15:23:43
发表于 2010-11-26 14:32 | 显示全部楼层
牛 顶一个。

使用道具 举报

回复
论坛徽章:
18
CTO参与奖
日期:2009-01-15 11:42:46奥运纪念徽章
日期:2012-11-28 09:33:31奥运纪念徽章
日期:2012-11-22 14:39:55灰彻蛋
日期:2012-05-15 14:37:582011新春纪念徽章
日期:2011-02-18 11:43:32生肖徽章2007版:兔
日期:2011-01-20 12:58:492011新春纪念徽章
日期:2011-01-04 10:35:48数据库板块每日发贴之星
日期:2010-12-09 01:01:012010广州亚运会纪念徽章:帆船
日期:2010-12-06 11:01:472010广州亚运会纪念徽章:曲棍球
日期:2010-12-06 10:59:28
 楼主| 发表于 2010-11-26 14:33 | 显示全部楼层
请问这样的写法跟上面一样吗?
这样写会很快。。。

SELECT A.INTERFACE_ID                                                 INTERFACE_ID
         , DECODE(A.SEND_PTNR_CD,'040740','031953',A.SEND_PTNR_CD)        SENDER_ABBR
         , A.RECV_PTNR_CD                                                 RECEIVE_ABBR
         , A.INVOICE_NO                                                   INVOICE_NO
         , DECODE(A.BIZ_UNIT_CD,'300','100',A.BIZ_UNIT_CD)                BIZ_UNIT_CD
         , A.EXIM_TYPE                                                    EXIM_TYPE
         , A.HBL_NO                                                       HBL_NO
         , A.MBL_NO                                                       MBL_NO
         , A.ONBOARD_DT                                                   ONBOARD_DATE
         , DECODE(A.ARR_DT, NULL, A.ONBOARD_DT, A.ARR_DT)                 ARRIVAL_DATE
         , A.INCOTERMS                                                    INCOTERMS
         , A.SHPR_NM                                                      SHIPP_NM
         , A.CARRIER_NM                                                   CARRIER_NM
         , A.ISSUE_DT                                                     ISSUE_DT
         , A.ISSUER                                                       ISSUER
         , A.ISSUER_TEL_NO                                                ISSUER_TEL_NO
         , A.ISSUER_MAIL_ID                                               ISSUER_MAIL_ID
         , A.HLDS_YN                                                      HLDS_YN
         , DECODE(A.EXIM_TYPE,'30','30',A.COMBINE_TS_TYPE)                COMBINE_TS_TYPE
         , A.BIZ_UNIT_CD                                                  OLD_BIZ_UNIT_CD
                     , A.DUE_DATE
                     , A.AMD_DUE_DATE
      FROM TB_INT021  A
     WHERE  ((null IS NULL AND 1 = 1) OR (null IS NOT NULL AND A.INTERFACE_ID = null))
        AND NOT EXISTS (SELECT 'T'
                          FROM mngfns.TB_DLA102 b,mngfns.TB_DLA102_ptnr c,tb_int023 d
                         WHERE b.edi_inv_no       = a.invoice_no
                           AND b.inv_cust_cd      = a.send_ptnr_cd
                           And c.edi_inv_no       = a.invoice_no
                           And c.inv_cust_cd      = a.send_ptnr_cd
                           And d.invoice_no  = a.invoice_no
                           AND d.hbl_no      = a.hbl_no
                           AND d.result_type = 'REJECT'
                       )

使用道具 举报

回复
论坛徽章:
1
生肖徽章2007版:狗
日期:2009-04-24 15:23:43
发表于 2010-11-26 14:33 | 显示全部楼层
牛 顶一个。

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2010-11-26 15:02 | 显示全部楼层


条件“((null IS NULL AND 1 = 1) OR (null IS NOT NULL AND A.INTERFACE_ID = null))”, 中的“A.INTERFACE_ID = null”, 应该改为“A.INTERFACE_ID is null”,
这个条件写的非常奇怪啊? 其实这个条件更本不用写是多余的(应为该条件永远为true吗!), 原因是你可以看看Predicate Information , 关于这个条件应该是没有的!


LZ, 你语句发生目前语句执行效率差的原因不是“有两个表进行了全表扫描”(就是表TB_INT021和TB_DLA102_PTNR使用HASH JOIN(ANTI), 从目前该2表的数据量看, 用FTS
应该是对的), 而是你看如下的红色地方, 也就是执行计划用了"FILTER",  这才是问题的更本原因! 你能把目前语句的执行计划的Predicate Information贴出来吗? 另外你是否对表进行了最近的分析?


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5583 Card=340 Bytes= 86020)
   1    0   FILTER
   2    1     NESTED LOOPS (ANTI) (Cost=4903 Card=340 Bytes=86020)
   3    2       HASH JOIN (ANTI) (Cost=3075 Card=457 Bytes=110137)
   4    3         TABLE ACCESS (FULL) OF 'TB_INT021' (Cost=1708 Card=7  2471 Bytes=15798678)
   5    3         TABLE ACCESS (FULL) OF 'TB_DLA102_PTNR' (Cost=997 Card=218970 Bytes=5036310)
   6    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_DLA102' (Cost=4 Card=1245990 Bytes=14951880)
   7    6         INDEX (RANGE SCAN) OF 'IX_DLA102_17' (NON-UNIQUE) (Cost=2 Card=3)
   8    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_INT023' (Cost=2 Card=1 Bytes=29)
   9    8       INDEX (RANGE SCAN) OF 'IDX_INVOICE_NO_INT023' (NON-UNIQUE) (Cost=1 Card=5632)

另外你后面的语句改写是不是和你原先语句完全等效, 这要看你目前外表和3个参与子查询表的关系是如何的!






[ 本帖最后由 bell6248 于 2010-11-26 15:13 编辑 ]

使用道具 举报

回复
论坛徽章:
18
CTO参与奖
日期:2009-01-15 11:42:46奥运纪念徽章
日期:2012-11-28 09:33:31奥运纪念徽章
日期:2012-11-22 14:39:55灰彻蛋
日期:2012-05-15 14:37:582011新春纪念徽章
日期:2011-02-18 11:43:32生肖徽章2007版:兔
日期:2011-01-20 12:58:492011新春纪念徽章
日期:2011-01-04 10:35:48数据库板块每日发贴之星
日期:2010-12-09 01:01:012010广州亚运会纪念徽章:帆船
日期:2010-12-06 11:01:472010广州亚运会纪念徽章:曲棍球
日期:2010-12-06 10:59:28
 楼主| 发表于 2010-11-26 16:17 | 显示全部楼层
原帖由 bell6248 于 2010-11-26 15:02 发表


条件“((null IS NULL AND 1 = 1) OR (null IS NOT NULL AND A.INTERFACE_ID = null))”, 中的“A.INTERFACE_ID = null”, 应该改为“A.INTERFACE_ID is null”,
这个条件写的非常奇怪啊? 其实这个条件更本不用写是多余的(应为该条件永远为true吗!), 原因是你可以看看Predicate Information , 关于这个条件应该是没有的!


LZ, 你语句发生目前语句执行效率差的原因不是“有两个表进行了全表扫描”(就是表TB_INT021和TB_DLA102_PTNR使用HASH JOIN(ANTI), 从目前该2表的数据量看, 用FTS
应该是对的), 而是你看如下的红色地方, 也就是执行计划用了"FILTER",  这才是问题的更本原因! 你能把目前语句的执行计划的Predicate Information贴出来吗? 另外你是否对表进行了最近的分析?


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5583 Card=340 Bytes= 86020)
   1    0   FILTER
   2    1     NESTED LOOPS (ANTI) (Cost=4903 Card=340 Bytes=86020)
   3    2       HASH JOIN (ANTI) (Cost=3075 Card=457 Bytes=110137)
   4    3         TABLE ACCESS (FULL) OF 'TB_INT021' (Cost=1708 Card=7  2471 Bytes=15798678)
   5    3         TABLE ACCESS (FULL) OF 'TB_DLA102_PTNR' (Cost=997 Card=218970 Bytes=5036310)
   6    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_DLA102' (Cost=4 Card=1245990 Bytes=14951880)
   7    6         INDEX (RANGE SCAN) OF 'IX_DLA102_17' (NON-UNIQUE) (Cost=2 Card=3)
   8    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_INT023' (Cost=2 Card=1 Bytes=29)
   9    8       INDEX (RANGE SCAN) OF 'IDX_INVOICE_NO_INT023' (NON-UNIQUE) (Cost=1 Card=5632)

另外你后面的语句改写是不是和你原先语句完全等效, 这要看你目前外表和3个参与子查询表的关系是如何的!






条件“((null IS NULL AND 1 = 1) OR (null IS NOT NULL AND A.INTERFACE_ID = null))”, 这只是存储过程里的一部分,null是输入的变量值,这里的or子句是一个判断,在逻辑上是有他的作用的

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2010-11-26 16:27 | 显示全部楼层
原帖由 lichangzai 于 2010-11-26 16:17 发表


条件“((null IS NULL AND 1 = 1) OR (null IS NOT NULL AND A.INTERFACE_ID = null))”, 这只是存储过程里的一部分,null是输入的变量值,这里的or子句是一个判断,在逻辑上是有他的作用的




我后面给你的意见, 你可以参考一下. 你是否对表进行了的分析?





[ 本帖最后由 bell6248 于 2010-11-26 16:28 编辑 ]

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表