问题描述

sql 调优 oracle 执行速度再快一点

select psf.pol_num, psf.bank_acct_num, cba_dda.bank_acct_nm
from   tbank_pos_slip_files psf, tclient_policy_links cpl_dda, tclient_bank_accounts cba_dda
where  cpl_dda.cli_num = cba_dda.cli_num
and    cpl_dda.bank_acct_typ = cba_dda.bank_acct_typ
and    cpl_dda.link_typ = 'O'
and    psf.bank_acct_num = cba_dda.bank_acct_num
and    psf.trade_role = 'C'
and    psf.mtch_ind = 'Y'
and    psf.mtch_dt between to_date('2013/11/01', 'yyyy/mm/dd') and to_date('2013/11/30', 'yyyy/mm/dd')
      --and (p_terr_cd = '*' or instr(p_terr_cd, psf.pos_terr_cd ||'/') > 0)
and    not exists (select 'Y'
        from   tclient_policy_links cpl, tclient_details cli
        where  cpl.cli_num = cli.cli_num
        and    cpl.link_typ in ('I', 'O')
        and    cpl.pol_num = psf.pol_num
        and    cli.cli_nm = cba_dda.bank_acct_nm
        union all
        select 'Y'
        from   tbeneficiary_details bft
        where  bft.pol_num = psf.pol_num
        and    bft.bnfy_nm = cba_dda.bank_acct_nm)
union
select psf.pol_num, psf.bank_acct_num, cba_dca.bank_acct_nm
from   tbank_pos_slip_files psf, vclient_policy_links cpl_dca, tclient_bank_accounts cba_dca
where  cpl_dca.cli_num = cba_dca.cli_num
and    cpl_dca.payo_bank_acct_typ = cba_dca.bank_acct_typ
and    psf.bank_acct_num = cba_dca.bank_acct_num
and    psf.trade_role = 'C'
and    psf.mtch_ind = 'Y'
and    psf.mtch_dt between to_date('2013/11/01', 'yyyy/mm/dd') and to_date('2013/11/30', 'yyyy/mm/dd')
      --and (p_terr_cd = '*' or instr(p_terr_cd, psf.pos_terr_cd ||'/') > 0)
and    not exists (select 'Y'
        from   tclient_policy_links cpl, tclient_details cli
        where  cpl.cli_num = cli.cli_num
        and    cpl.link_typ in ('I', 'O')
        and    cpl.pol_num = psf.pol_num
        and    cli.cli_nm = cba_dca.bank_acct_nm
        union all
        select 'Y'
        from   tbeneficiary_details bft
        where  bft.pol_num = psf.pol_num
        and    bft.bnfy_nm = cba_dca.bank_acct_nm)