一个谓词推入案例

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

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

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

测试库执行计划：

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

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'

测试库慢是因为对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