首页 > 其他 > 详细

一个谓词推入案例

时间:2014-03-26 06:00:25      阅读:575      评论:0      收藏:0      [点我收藏+]

ITpub 上有个帖子 http://www.itpub.net/thread-1852068-1-1.html

生产数据库版本  10.2.0.4
测试数据库版本  10.2.0.1

sql在生产库运行就使用了谓词推进,效率很高,只要3s,但是在测试库没有使用谓词推进,需要6分30s。
大家帮忙看看如何优化或者强制使用谓词推进,使其在测试库效率高一些
生产库执行计划:

bubuko.com,布布扣

测试库执行计划:

bubuko.com,布布扣

原SQL比较长,下面是截取了谓词推入所在的SQL块的代码.

bubuko.com,布布扣
select a.prtno qq,
                a.contno,
                d.prtno,
                a.appntname,
                a.appntidno,
                a.insuredname,
                a.insuredidno,
                d.riskcode,
                (select riskname
                   from lmrisk
                  where 1395381350000 = 1395381350000
                    and riskcode = trim(d.riskcode)),
                a.prem,
                a.amnt,
                decode(a.payintv,
                       -1,
                       不定期交,
                       0,
                       趸交,
                       1,
                       月交,
                       3,
                       季交,
                       6,
                       半年交,
                       12,
                       年交,
                       a.payintv),
                d.transdate,
                d.transtime,
                a.makedate,
                a.cvalidate,
                d.bankbranch,
                d.banknode,
                a.agentcom,
                (select name from lacom where agentcom = a.agentcom),
                d.managecom,
                (select c.name from ldcom c where c.comcode = a.signcom),
                (select name from laagent where agentcode = a.agentcode),
                decode(trim(a.originflag),
                       01,
                       ‘‘,
                       decode(e.doccode, ‘‘, 银保通待扫描, 银保通已扫描)),
                substr(e.makedate, 1, 10) ||     ||
                substr(e.maketime, 1, 5) as makedateandtime,
                (select ld1.codename
                   from ldcode1 ld1
                  where ld1.codetype = ybtsalechnl
                    and ld1.comcode =
                        rpad(a.salechnl, length(ld1.comcode),  )
                    and ld1.code1 =
                        rpad(a.salechnldetail, length(ld1.code1),  )
                    and ld1.code = rpad(a.bankcode, length(ld1.code),  ))
           from yktransstatus d, v_lccont_yc a
           left join es_doc_main e
             on e.doccode = trim(a.prtno)
            and e.busstype = TB
            and e.subtype = 1003
          where (a.appflag = 1 or a.appflag = 4)
            and a.contno = d.polno
            and exists
          (select 1
                   from ldcode1 ld
                  where ld.codetype = ybtsalechnl
                    and ld.comcode =
                        rpad(a.salechnl, length(ld.comcode),  )
                    and ld.code1 =
                        rpad(a.salechnldetail, length(ld.code1),  )
                    and ld.code = rpad(a.bankcode, length(ld.code),  ))
            and (d.funcflag = 01 or d.funcflag = 12)
            and d.rcode = 1
            and d.prtno is not null
            and d.managecom like %86%
            and d.bankcode = 01
            and d.managecom like 86%
            and d.transdate >= date 2014-03-21
            and d.transdate <= date 2014-03-21
bubuko.com,布布扣

测试库慢是因为对view  v_lccont_yc 进行谓词推入,导致到对view里的表LCCOUNT进行了full scan.

可以参试加入hints  /*+ leading(d)  no_merge(a) push_pred(a) */

只有当表yktransstatus d 在读取v_lccont_yc a 之前先访问,我们才能拿到polno的值,进而通过等值条件 a.contno = d.polno 推入到 视图 v_lccont_yc

一个谓词推入案例,布布扣,bubuko.com

一个谓词推入案例

原文:http://www.cnblogs.com/princessd8251/p/3624634.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!