oracle insert select和select执行计划不同解决

  之前有提到,一个查询独立执行的时候走的是A执行计划,作为另外一个查询的一部分时走的是B执行计划。不仅如此,还会出现insert select和select执行计划不同的情况,前者属于正常情况,可是后者就不是很好理解了,如下所示:

INSERT INTO ta_tagencycfm04file
                  (c_tenantid,          c_tacode,
                   c_tradeacco,         c_cityno,           c_fundcode,            d_requestdate,
                   d_requesttime,       c_businflag,        c_requestno,           c_moneytype,
                   f_shares,            f_balance,          f_confirmshares,       f_confirmbalance,
                   c_fundacco,          c_custtype,         d_hopedate,            c_agencyno,
                   d_cdate,             f_stamptax,         f_bsharefare,          f_changefillfare,
                   f_agentfare,         f_fundfare,         f_interesttax,         f_factinterest,
                   f_sumfare,           c_oricserialno,     f_agio,                c_netno,
                   c_otheragency,       c_othernetno,       c_othertradeacco,      c_otheracco,
                   c_cserialno,         c_bonustype,        c_freezecause,         d_freezeenddate,
                   c_orirequestno ,     c_chargetype,       f_netvalue,            c_datadetailflag,
                   c_targetchargetype,  c_changeflag,       f_interestshare,       f_backbalance,
                   f_changefare,        f_profitbalance,    f_chincome_abs,        c_incomeflag,
                   f_breachfare,        f_breachfare4fund,  f_raiseinerest,        c_requestendflag,
                   c_exceedflag,        d_registdate,       c_childnetno,          c_taflag,
                   c_cserialnototal,    f_othernetvalue,    f_othercfmshares,      c_protocolno,
                   c_txtfundcode,       c_txtcause,         c_txtbusinflag,        c_txtothercode,
                   c_txterrordetail,    c_custno,           c_adjustflag,          f_profitcompensation,
                   d_originaldate,      c_combcode,         c_specification,       c_forceredemptiontype,
                   f_otherfee2)
            SELECT a.c_tenantid,        a.c_tacode,         c_tradeacco,
                   CASE CONCAT(CONCAT(CONCAT('F6' ,a.c_agencyno),c_taflag),a.c_businflag) WHEN '66003153' THEN ' ' ELSE c_cityno END c_cityno,
                   a.c_fundcode,       a.d_requestdate,    d_requesttime,          a.c_businflag,
                   c_requestno,        c_moneytype,        f_shares,               f_balance,
                   f_confirmshares,    f_confirmbalance,
                   CASE a.c_fundacco WHEN ' ' THEN ' ' ELSE (CASE (SELECT t.c_paramvalue FROM ta_textparameter t WHERE c_paramitem='C_FUNDACCO3CHG')
                   WHEN '1' THEN CONCAT('T',SUBSTR(a.c_fundacco,4,9)) ELSE a.c_fundacco END) END c_fundacco,
                   c_custtype,          d_hopedate,         a.c_agencyno,             d_cdate,
                   f_stamptax,          f_bsharefare,       f_changefillfare,         f_agentfare,
                   f_fundfare,          f_interesttax,      f_factinterest,           f_sumfare,
                   c_oricserialno,      f_agio,             c_netno,                  c_otheragency,
                   c_othernetno,
                   CASE CONCAT(CONCAT(a.c_agencyno,a.c_businflag),a.c_adjustcause) WHEN '002704' THEN ' ' ELSE a.c_othertradeacco END c_othertradeacco,
                   CASE a.c_otheracco WHEN ' ' THEN ' ' ELSE (CASE (SELECT t.c_paramvalue FROM ta_textparameter t WHERE c_paramitem='C_FUNDACCO3CHG')
                   WHEN '1' THEN CONCAT('T',SUBSTR(a.c_otheracco,4,9)) ELSE a.c_otheracco END) END c_otheracco,
                   c_cserialno,         c_bonustype,        c_freezecause,            d_freezeenddate,
                   c_orirequestno,      c_chargetype,       f_netvalue,               c_datadetailflag,
                   c_targetchargetype,  c_changeflag,       f_interestshare,          f_backbalance,
                   f_changefare,        f_profitbalance,    f_chincome_abs,           c_incomeflag,
                   f_breachfare,        f_breachfare4fund,  f_raiseinerest,           c_requestendflag,
                   c_exceedflag,        d_registdate,       c_childnetno,             c_taflag,
                   c_cserialnototal,    f_othernetvalue,    f_othercfmshares,         c_protocolno,
                   CASE a.c_sharetypes WHEN 'AB' THEN a.c_fundcode ELSE COALESCE(b.c_outfundcode,a.c_fundcode) END c_txtfundcode,
                   COALESCE(c.c_outcause,a.c_cause) c_txtcause,
                   COALESCE(d.c_confirmflag,CONCAT('1', SUBSTR(a.c_outbusinflag,2,2))) c_txtbusinflag,
                   CASE a.c_sharetypes WHEN 'AB' THEN a.c_othercode ELSE COALESCE(e.c_outfundcode,a.c_othercode) END c_txtothercode,
                   CASE CONCAT(TRIM(COALESCE(c.c_describe,a.c_errordetail)), ' ') WHEN ' ' THEN '0000' ELSE COALESCE(c.c_describe, a.c_errordetail) END c_txterrordetail,
                   ' ' c_custno,        a.c_adjustflag,     a.f_profitcompensation,   a.d_originaldate,
                   a.c_combcode,        a.c_specification,  a.c_forceredemptiontype,  a.f_otherfee2
              FROM (SELECT /*+full(a) full(d) use_hash(d) */ 
                           CASE a.c_fundacco WHEN COALESCE(icbc.c_fundacco,'***' ) THEN COALESCE(icbc.c_tradeacco,a.c_tradeacco) ELSE a.c_tradeacco END c_tradeacco,
                           CASE a.c_agencyno WHEN '002' THEN LPAD(TRIM(a.c_netno),4,'0') ELSE a.c_cityno END c_cityno, a.c_fundcode c_fundcode,
                           CASE CONCAT(CONCAT(a.c_iversion,a.c_businflag),a.c_taflag)
                             WHEN '400031' THEN getrealdays(a.c_tenantid, a.c_fundcode, a.c_agencyno, 20100513,
                                                -1*getsysvalue(a.c_tenantid, a.c_tacode, '*', '*', a.c_fundcode, 'FundTnConfirm', '1'))
                                           ELSE a.d_requestdate
                             END d_requestdate,
                           a.d_requesttime, a.c_tacode, a.c_tenantid,
                           CASE CONCAT(a.c_businflag,a.c_taflag) WHEN '031' THEN '53' ELSE (CASE CONCAT(CONCAT(a.c_iversion,a.c_cause),a.c_businflag) WHEN '400200154' THEN '50' ELSE a.c_businflag END) END c_businflag,
                           CASE CONCAT(a.c_agencyno,a.c_taflag)
                             WHEN '0061' THEN (CASE a.c_outbusinflag WHEN '081' THEN a.c_freezeno ELSE ' ' END)
                             WHEN '0021' THEN (CASE a.c_businflag
                                                 WHEN '50' THEN (CASE a.c_requestno WHEN ' ' THEN a.c_cserialno ELSE a.c_requestno END)
                                                 WHEN '54' THEN (CASE a.c_requestno WHEN ' ' THEN a.c_cserialno ELSE a.c_requestno END)
                                                           ELSE ' '
                                                END)
                                         ELSE (CASE a.c_requestno WHEN ' ' THEN a.c_cserialno ELSE a.c_requestno END)
                            END c_requestno,
                           COALESCE(fi.c_moneytype,'156') c_moneytype,
                           CASE
                             WHEN CONCAT(a.c_businflag,a.c_status) IN ('041','051','061') THEN a.f_confirmshares
                             WHEN CONCAT(CONCAT(CONCAT('0',a.c_businflag),a.c_agencyno),a.c_status) = '1130021' THEN  (CASE a.c_iversion WHEN '400' THEN a.f_shares ELSE a.f_confirmshares END)
                             WHEN (CONCAT(a.c_iversion,a.c_agencyno) = '400002') AND (CONCAT(a.c_status,a.c_businflag) = '103') THEN a.f_confirmshares /*+ a.f_unshares*/
                             ELSE a.f_shares
                           END f_shares,
                           a.f_balance,
                           CASE a.c_iversion WHEN '400' THEN d.f_confirmshares ELSE (CASE /*COALESCE(tai.c_changeonstep,'0')*/CONCAT(CONCAT(CONCAT('0',a.c_businflag),a.c_agencyno),a.c_status)
                           WHEN '1130021' THEN /*COALESCE(chgin.f_confirmshares,0)*/ 0 ELSE d.f_confirmshares END) END  f_confirmshares,
                           CASE d.c_businflag
                             WHEN '70' THEN 0
                             WHEN '71' THEN 0
                             WHEN '13' THEN (CASE a.c_iversion WHEN '400' THEN 1 ELSE 0 END)*(d.f_tradefare+d.f_backfare+d.f_otherfare1)+d.f_confirmbalance
                             WHEN '16' THEN (CASE a.c_iversion WHEN '400' THEN 1 ELSE 0 END)*(d.f_tradefare+d.f_backfare+d.f_otherfare1)+d.f_confirmbalance
                                       ELSE d.f_confirmbalance
                            END f_confirmbalance,
                           a.c_fundacco, a.c_custtype, a.d_hopedate d_hopedate, a.c_agencyno, a.d_outputdate d_cdate,
                           a.c_status, d.f_tradefare, d.f_stamptax, d.f_tafare, d.f_otherfare1, d.f_backfare,
                           CASE a.c_businflag WHEN '03' THEN d.f_tradefare ELSE 0 END f_redeemfare,
                           CASE d.c_businflag WHEN '03' THEN d.f_backfare  ELSE 0 END f_bsharefare,
                           CASE d.c_businflag WHEN '13' THEN d.f_backfare  ELSE 0 END f_changefillfare,
                           0 f_agentfare,
                           0 f_fundfare,
                           CASE a.c_businflag
                             WHEN '54' THEN (CASE a.f_confirmbalance-a.f_frozenbalance WHEN 0 THEN 0 ELSE a.f_interesttax END)
                             WHEN '50' THEN 0
                                       ELSE a.f_interesttax
                            END f_interesttax,
                           0 f_factinterest,
                           (d.f_tradefare+d.f_stamptax+d.f_tafare+d.f_otherfare1+d.f_backfare+d.f_breachfare+d.f_profitbalance) f_sumfare,
                           CASE WHEN
                             'F6' = '27' AND d.c_agencyno = '227' AND a.c_adjustcause<>'1'
                           THEN
                             (CASE a.c_businflag WHEN '15' THEN ' ' WHEN '16' THEN ' ' WHEN '05' THEN ' ' WHEN '70' THEN ' ' WHEN '21' THEN '' ELSE d.c_oricserialno END)
                            ELSE
                              d.c_oricserialno
                            END c_oricserialno,
                           a.f_agio,a.c_netno, a.c_otheragency,a.c_othernetno,a.c_othertradeacco,
                           CASE a.c_businflag
                             WHEN '13' THEN a.c_othercode
                             WHEN '16' THEN a.c_othercode
                             WHEN '70' THEN (CASE a.c_adjustcause WHEN '5' THEN a.c_othercode WHEN 'h' THEN a.c_othercode ELSE ' ' END)
                             WHEN '71' THEN (CASE a.c_adjustcause WHEN '5' THEN a.c_othercode WHEN 'h' THEN a.c_othercode ELSE ' ' END)
                             ELSE ' '
                           END c_othercode,
                           a.c_otheracco, d.c_cserialno,
                           CASE a.c_businflag WHEN '07' THEN a.c_bonustype ELSE ' ' END c_bonustype,
                           a.c_freezecause, a.d_freezeenddate, a.f_frozenbalance,
                           CASE a.c_fundacco
                             WHEN COALESCE(icbc.c_fundacco,'***') THEN (CASE a.c_businflag
                                                                        WHEN '70' THEN a.c_tradeacco
                                                                        WHEN '71' THEN a.c_tradeacco
                                                                                  ELSE (CASE CONCAT(a.c_agencyno,a.c_taflag) WHEN '0021' THEN ' ' ELSE a.c_orirequestno END)
                                                                         END)
                                                                  ELSE (CASE CONCAT(a.c_agencyno,a.c_taflag) WHEN '0021' THEN ' ' ELSE a.c_orirequestno END)
                            END c_orirequestno,
                           d.c_sharetype,
                           CASE d.c_sharetype WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE d.c_sharetype END c_chargetype,
                           a.f_netvalue,
                           CASE a.c_businflag WHEN '13' THEN a.c_othershare WHEN '16' THEN a.c_sharetype WHEN '70' THEN a.c_sharetype WHEN '71' THEN a.c_sharetype ELSE ' ' END c_targetsharetype,
                           '1' c_datadetailflag,
                           CASE a.c_businflag
                             WHEN '13' THEN (CASE a.c_othershare WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_othershare END)
                             WHEN '16' THEN (CASE a.c_sharetype  WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_sharetype END)
                             WHEN '70' THEN (CASE a.c_sharetype  WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_sharetype END)
                             WHEN '71' THEN (CASE a.c_sharetype  WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_sharetype END)
                                       ELSE ' '
                            END c_targetchargetype,
                           CASE a.c_businflag WHEN '13' THEN '0'  WHEN '16' THEN '1' WHEN '12' THEN '0' WHEN '14' THEN '0' WHEN '15' THEN '1' ELSE ' ' END c_changeflag,
                           0 f_interestshare, 0 f_backbalance,
                           CASE a.c_businflag WHEN '13' THEN d.f_tradefare ELSE 0 END f_changefare,
                           d.f_profitbalance, 0 f_chincome_abs, '0' c_incomeflag, d.f_breachfare, 0 f_breachfare4fund,
                           0 f_raiseinerest, a.c_requestendflag,
                           CASE a.c_requestendflag WHEN '1' THEN '0' ELSE a.c_exceedflag END c_exceedflag,
                           COALESCE(d.d_registdate,a.d_cdate) d_registdate,
                           SUBSTR(a.c_cause,5,100) c_errordetail,
                           a.c_childnetno,
                           CASE CONCAT(CONCAT(a.c_iversion,a.c_outbusinflag),a.c_businflag) WHEN '40002605' THEN '1' ELSE a.c_taflag END c_taflag,
                           CASE a.c_iversion
                             WHEN '400' THEN (CASE d.c_businflag
                                                WHEN '16' THEN (CASE a.c_tacode   WHEN 'F6'     THEN chgout.c_cserialno ELSE d.c_cserialnototal END)   /*3 c_tacode*/
                                                WHEN '15' THEN (CASE a.c_agencyno WHEN a.c_otheragency THEN d.c_cserialnoout   ELSE d.c_cserialnototal END)
                                                          ELSE d.c_cserialnototal
                                                END)
                                          ELSE d.c_cserialnototal
                            END c_cserialnototal,
                           CASE a.c_businflag
                             WHEN '14' THEN (CASE CONCAT(a.c_iversion,a.c_outbusinflag) WHEN '400033'THEN (CASE a.c_agencyno WHEN a.c_otheragency THEN '133' ELSE '135' END) ELSE a.c_outbusinflag END)
                             WHEN '15' THEN (CASE CONCAT(a.c_iversion,a.c_outbusinflag) WHEN '400033'THEN (CASE a.c_agencyno WHEN a.c_otheragency THEN '133' ELSE '134' END) ELSE a.c_outbusinflag END)
                             WHEN '12' THEN (CASE CONCAT(a.c_iversion,a.c_outbusinflag) WHEN '400033'THEN '133' ELSE a.c_outbusinflag END)
                             WHEN '13' THEN (CASE a.c_iversion WHEN '400' THEN (CASE (CASE a.c_tacode WHEN ' ' THEN 'F6' ELSE a.c_tacode END) WHEN 'F6' THEN '136' ELSE '038' END) ELSE (CASE /*nvl(tai.c_changeonstep,'0')*/'0' WHEN '1' THEN '***' ELSE a.c_outbusinflag END) END)
                             WHEN '16' THEN (CASE a.c_iversion WHEN '400' THEN (CASE (CASE a.c_tacode WHEN ' ' THEN 'F6' ELSE a.c_tacode END) WHEN 'F6' THEN '136' ELSE '038' END) ELSE (CASE /*nvl(tai.c_changeonstep,'0')*/'0' WHEN '1' THEN '***' ELSE a.c_outbusinflag END) END)
                             WHEN '04' THEN (CASE a.c_iversion WHEN '400' THEN '126' ELSE (CASE a.c_agencyno WHEN '006' THEN '126' ELSE a.c_outbusinflag END) END)
                             WHEN '05' THEN (CASE a.c_iversion WHEN '400' THEN '127' ELSE (CASE CONCAT(a.c_agencyno,a.c_outbusinflag) WHEN '006026' THEN '*' ELSE a.c_outbusinflag END) END)
                             WHEN '06' THEN (CASE a.c_iversion WHEN '400' THEN (CASE a.c_outbusinflag WHEN '026' THEN '126' ELSE a.c_outbusinflag END) ELSE (CASE CONCAT(a.c_agencyno,a.c_outbusinflag) WHEN '006026' THEN '126' ELSE a.c_outbusinflag END) END)
                                       ELSE a.c_outbusinflag
                            END c_outbusinflag,
                           a.f_otherprice f_othernetvalue,
                           CASE /*tai.c_changeonstep*/CONCAT(CONCAT('0',a.c_businflag),a.c_status)  WHEN '1131' THEN (CASE a.c_agencyno WHEN '002'THEN 0 ELSE /*COALESCE(chgin.f_confirmshares,0)*/ 0 END) ELSE 0 END f_othercfmshares,
                           CASE CONCAT(a.c_iversion,a.c_agencyno) WHEN '400002' THEN (CASE INSTR('039059060061',a.c_outbusinflag) WHEN 0 THEN '' ELSE (CASE CONCAT(TRIM(a.c_otheracco),' ') WHEN ' ' THEN a.c_protocolno ELSE TRIM(a.c_otheracco) END) END) ELSE a.c_protocolno END c_protocolno,
                           a.c_custno,
                           CASE WHEN (a.c_adjustcause = '5') AND (a.c_agencyno = '004') THEN '8' ELSE '' END c_adjustflag,
                           0 f_profitcompensation,  d.d_originaldate, ' ' c_combcode, a.c_adjustcause, a.c_memo c_specification,
                           a.c_cause, a.c_forceredemptiontype,
                           CASE d.c_sharetype WHEN 'C' THEN d.f_otherfare1 ELSE 0 END f_otherfee2,
                           a.c_sharetypes 
                      FROM "sett_ta_tconfirm_*_F6" a
                           LEFT JOIN ta_tfundinfo fi
                                  ON (a.c_fundcode  = fi.c_fundcode
                                 AND  a.c_tacode    = fi.c_tacode
                                 AND  a.c_tenantid  = fi.c_tenantid)
                           LEFT JOIN ta_tnetvalueday c
                                  ON (a.c_othercode = c.c_fundcode
                                 AND  a.d_cdate     = c.d_cdate
                                 AND  a.c_tacode    = c.c_tacode
                                 AND  a.c_tenantid  = c.c_tenantid)
                           LEFT JOIN (SELECT /*+ no_merge */chg.d_cdate, chg.c_cserialno, chg.c_requestno,
                                             chg.c_agencyno, chg.c_tacode, chg.c_tenantid
                                        FROM "sett_ta_tconfirm_*_F6" chg ,
                                             ta_tagencyexpbatch ai,
                                             ta_tfundinfo fi
                                       WHERE chg.c_agencyno  = ai.c_agencyno
                                         AND chg.c_tacode    = ai.c_tacode
                                         AND chg.c_tenantid  = ai.c_tenantid
                                         AND chg.c_fundcode  = fi.c_fundcode
                                         AND chg.c_businflag IN ('13','14')
                                         AND chg.c_tacode    = fi.c_tacode
                                         AND chg.c_tenantid  = fi.c_tenantid) chgout
                                  ON (a.c_requestno = chgout.c_requestno
                                 AND  a.d_cdate     = chgout.d_cdate
                                 AND  a.c_agencyno  = chgout.c_agencyno
                                 AND  a.c_tacode    = chgout.c_tacode
                                 AND  a.c_tenantid  = chgout.c_tenantid),
                           ta_tconfirmdetail d
                           LEFT JOIN (SELECT /*+ no_merge */c_fundacco, c_agencyno, c_netno,
                                             c_tradeacco, c_tacode, c_tenantid
                                        FROM ta_tacconet
                                       WHERE c_fundacco = COALESCE((SELECT c_paramvalue
                                                                      FROM ta_textparameter
                                                                     WHERE c_paramclass = 'TADeal'
                                                                       AND c_paramitem = 'ICBCFundacco'),'***' )) icbc
                                  ON (d.c_fundacco = icbc.c_fundacco
                                 AND  d.c_agencyno = icbc.c_agencyno
                                 AND  d.c_netno    = icbc.c_netno
                                 AND  d.c_tacode   = icbc.c_tacode
                                 AND  d.c_tenantid = icbc.c_tenantid)
                     WHERE a.c_tacode        = 'F6'
                       AND a.c_tenantid      = '*'
                       AND a.d_cdate         = d.d_cdate
                       AND a.c_cserialno     = d.c_cserialnototal
                       AND a.c_tenantid      = d.c_tenantid
                       AND d.f_confirmshares > 0
                       AND CONCAT(a.c_taflag, a.c_status) <>'12'
                       AND CONCAT(a.c_businflag, a.c_outbusinflag) <> '15088'
                       AND CONCAT(CONCAT(CONCAT(a.c_agencyno,a.c_taflag),a.c_businflag),a.c_cause)<>'002170GHYH'
                       AND CONCAT(CONCAT(CONCAT(a.c_agencyno,a.c_taflag),a.c_businflag),a.c_cause)<>'002171GHYH'
                       AND a.c_isdetail    = '1'
                       AND (a.c_agencyno <> '207' OR a.c_businflag NOT IN ('13','16'))
                   ) a
                   LEFT JOIN ta_tfundcodechange b
                          ON (a.c_fundcode        = b.c_fundcode
                         AND  a.c_sharetype       = b.c_sharetype
                         AND  a.c_tacode          = b.c_tacode
                         AND  a.c_tenantid        = b.c_tenantid)
                   LEFT JOIN ta_terrormess c
                          ON (a.c_cause           = c.c_cause
                         AND  a.c_tenantid        = c.c_tenantid)
                   LEFT JOIN ta_tbusinchangeout d
                          ON (a.c_businflag       = d.c_businflag
                         AND  a.c_outbusinflag    = d.c_requestflag
                         AND  a.c_tenantid        = d.c_tenantid)
                   LEFT JOIN ta_tfundcodechange e
                          ON (a.c_othercode       = e.c_fundcode
                         AND  a.c_targetsharetype = e.c_sharetype
                         AND  a.c_tacode          = b.c_tacode
                         AND  a.c_tenantid        = e.c_tenantid);

执行计划如下:

 Plan Hash Value  : 697776383 

----------------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                         | Name                      | Rows    | Bytes      | Cost  | Time     |
----------------------------------------------------------------------------------------------------------------------------------
|    0 | INSERT STATEMENT                                  |                           |       1 |        756 | 28913 | 00:05:47 |
|    1 |   LOAD TABLE CONVENTIONAL                         | TA_TAGENCYCFM04FILE       |         |            |       |          |
|  * 2 |    INDEX RANGE SCAN                               | IDX_TEXTPARAMETER_VALUE   |       2 |         36 |     1 | 00:00:01 |
|  * 3 |    INDEX RANGE SCAN                               | IDX_TEXTPARAMETER_VALUE   |       2 |         36 |     1 | 00:00:01 |
|  * 4 |    HASH JOIN OUTER                                |                           |       1 |        756 | 28913 | 00:05:47 |
|    5 |     VIEW                                          |                           |       1 |        725 | 28911 | 00:05:47 |
|  * 6 |      HASH JOIN OUTER                              |                           |       1 |        740 | 28911 | 00:05:47 |
|  * 7 |       HASH JOIN OUTER                             |                           |       1 |        705 | 28908 | 00:05:47 |
|  * 8 |        HASH JOIN OUTER                            |                           |       1 |        692 | 28905 | 00:05:47 |
|    9 |         VIEW                                      |                           |       1 |        658 | 28903 | 00:05:47 |
| * 10 |          HASH JOIN OUTER                          |                           |       1 |        866 | 28903 | 00:05:47 |
| * 11 |           HASH JOIN OUTER                         |                           |       1 |        848 | 28885 | 00:05:47 |
|   12 |            NESTED LOOPS OUTER                     |                           |       1 |        832 | 28867 | 00:05:47 |
| * 13 |             HASH JOIN                             |                           |       1 |        810 | 28845 | 00:05:47 |
| * 14 |              HASH JOIN OUTER                      |                           |       1 |        295 |     3 | 00:00:01 |
| * 15 |               TABLE ACCESS FULL                   | TA_TCONFIRMDETAIL         |       1 |        245 |     2 | 00:00:01 |
|   16 |               VIEW                                |                           |       1 |         50 |     1 | 00:00:01 |
|   17 |                TABLE ACCESS BY GLOBAL INDEX ROWID | TA_TACCONET               |       1 |         40 |     1 | 00:00:01 |
| * 18 |                 INDEX RANGE SCAN                  | PK_TACCONET               |       1 |            |     1 | 00:00:01 |
| * 19 |                  TABLE ACCESS BY INDEX ROWID      | TA_TEXTPARAMETER          |       1 |         25 |     1 | 00:00:01 |
| * 20 |                   INDEX RANGE SCAN                | IDX_TEXTPARAMETER_VALUE   |     170 |            |     1 | 00:00:01 |
| * 21 |              TABLE ACCESS FULL                    | sett_ta_tconfirm_*_F6     | 2201315 | 1133677225 | 28836 | 00:05:47 |
|   22 |             VIEW PUSHED PREDICATE                 |                           |       1 |         22 |    22 | 00:00:01 |
| * 23 |              FILTER                               |                           |         |            |       |          |
| * 24 |               HASH JOIN                           |                           |       1 |         92 |    22 | 00:00:01 |
|   25 |                NESTED LOOPS                       |                           |       1 |         80 |     4 | 00:00:01 |
|   26 |                 NESTED LOOPS                      |                           |       1 |         80 |     4 | 00:00:01 |
| * 27 |                  TABLE ACCESS FULL                | TA_TAGENCYEXPBATCH        |       1 |          9 |     3 | 00:00:01 |
| * 28 |                  INDEX RANGE SCAN                 | idx_sett_ta_tconfirm_*_F6 |       1 |            |     1 | 00:00:01 |
|   29 |                 TABLE ACCESS BY INDEX ROWID       | sett_ta_tconfirm_*_F6     |       1 |         71 |     1 | 00:00:01 |
| * 30 |                TABLE ACCESS FULL                  | TA_TFUNDINFO              |     401 |       4812 |    18 | 00:00:01 |
| * 31 |            TABLE ACCESS FULL                      | TA_TFUNDINFO              |     401 |       6416 |    18 | 00:00:01 |
| * 32 |           TABLE ACCESS FULL                       | TA_TNETVALUEDAY           |    2411 |      43398 |    18 | 00:00:01 |
|   33 |         TABLE ACCESS FULL                         | TA_TFUNDCODECHANGE        |       1 |         34 |     2 | 00:00:01 |
|   34 |        TABLE ACCESS FULL                          | TA_TBUSINCHANGEOUT        |      83 |       1079 |     3 | 00:00:01 |
|   35 |       TABLE ACCESS FULL                           | TA_TERRORMESS             |     311 |      10885 |     3 | 00:00:01 |
|   36 |     TABLE ACCESS FULL                             | TA_TFUNDCODECHANGE        |       1 |         31 |     2 | 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C_PARAMITEM"='C_FUNDACCO3CHG')
* 3 - access("C_PARAMITEM"='C_FUNDACCO3CHG')
* 4 - access("A"."C_TENANTID"="E"."C_TENANTID"(+) AND "A"."C_TARGETSHARETYPE"="E"."C_SHARETYPE"(+) AND "A"."C_OTHERCODE"="E"."C_FUNDCODE"(+))
* 4 - filter("B"."C_TACODE"=CASE WHEN ("E"."C_FUNDCODE"(+) IS NOT NULL) THEN "A"."C_TACODE" ELSE "A"."C_TACODE" END )
* 6 - access("A"."C_TENANTID"="C"."C_TENANTID"(+) AND "A"."C_CAUSE"="C"."C_CAUSE"(+))
* 7 - access("A"."C_TENANTID"="D"."C_TENANTID"(+) AND "A"."C_OUTBUSINFLAG"="D"."C_REQUESTFLAG"(+) AND "A"."C_BUSINFLAG"="D"."C_BUSINFLAG"(+))
* 8 - access("A"."C_TENANTID"="B"."C_TENANTID"(+) AND "A"."C_TACODE"="B"."C_TACODE"(+) AND "A"."C_SHARETYPE"="B"."C_SHARETYPE"(+) AND "A"."C_FUNDCODE"="B"."C_FUNDCODE"(+))
* 10 - access("A"."C_TENANTID"="C"."C_TENANTID"(+) AND "A"."C_TACODE"="C"."C_TACODE"(+) AND "A"."D_CDATE"="C"."D_CDATE"(+) AND "A"."C_OTHERCODE"="C"."C_FUNDCODE"(+))
* 11 - access("A"."C_TENANTID"="FI"."C_TENANTID"(+) AND "A"."C_TACODE"="FI"."C_TACODE"(+) AND "A"."C_FUNDCODE"="FI"."C_FUNDCODE"(+))
* 13 - access("A"."D_CDATE"="D"."D_CDATE" AND "A"."C_CSERIALNO"="D"."C_CSERIALNOTOTAL" AND "A"."C_TENANTID"="D"."C_TENANTID")
* 14 - access("D"."C_TENANTID"="ICBC"."C_TENANTID"(+) AND "D"."C_TACODE"="ICBC"."C_TACODE"(+) AND "D"."C_NETNO"="ICBC"."C_NETNO"(+) AND "D"."C_AGENCYNO"="ICBC"."C_AGENCYNO"(+) AND
  "D"."C_FUNDACCO"="ICBC"."C_FUNDACCO"(+))
* 15 - filter("D"."C_TENANTID"='*' AND "D"."F_CONFIRMSHARES">0)
* 18 - access("C_FUNDACCO"=COALESCE( (SELECT "C_PARAMVALUE" FROM "TA_TEXTPARAMETER" "TA_TEXTPARAMETER" WHERE "C_PARAMITEM"='ICBCFundacco' AND "C_PARAMCLASS"='TADeal'),'***') AND "C_TENANTID"='*')
* 18 - filter("C_TENANTID"='*')
* 19 - filter("C_PARAMCLASS"='TADeal')
* 20 - access("C_PARAMITEM"='ICBCFundacco')
* 21 - filter(("A"."C_AGENCYNO"<>'207' OR "A"."C_BUSINFLAG"<>'13' AND "A"."C_BUSINFLAG"<>'16') AND "A"."C_BUSINFLAG"||"A"."C_OUTBUSINFLAG"<>'15088' AND "A"."C_ISDETAIL"='1' AND
  "A"."C_TAFLAG"||"A"."C_STATUS"<>'12' AND "A"."C_AGENCYNO"||"A"."C_TAFLAG"||"A"."C_BUSINFLAG"||"A"."C_CAUSE"<>'002170GHYH' AND
  "A"."C_AGENCYNO"||"A"."C_TAFLAG"||"A"."C_BUSINFLAG"||"A"."C_CAUSE"<>'002171GHYH' AND "A"."C_TACODE"='F6' AND "A"."C_TENANTID"='*')
* 23 - filter("A"."C_TENANTID"='*' AND "A"."C_TACODE"='F6')
* 24 - access("CHG"."C_FUNDCODE"="FI"."C_FUNDCODE" AND "CHG"."C_TACODE"="FI"."C_TACODE" AND "CHG"."C_TENANTID"="FI"."C_TENANTID")
* 27 - filter("AI"."C_AGENCYNO"="A"."C_AGENCYNO" AND "AI"."C_TACODE"='F6' AND "AI"."C_TENANTID"='*' AND "AI"."C_TACODE"="A"."C_TACODE" AND "AI"."C_TENANTID"="A"."C_TENANTID")
* 28 - access("CHG"."D_CDATE"="A"."D_CDATE" AND "CHG"."C_REQUESTNO"="A"."C_REQUESTNO" AND "CHG"."C_AGENCYNO"="A"."C_AGENCYNO" AND "CHG"."C_TACODE"="A"."C_TACODE" AND
  "CHG"."C_TENANTID"="A"."C_TENANTID")
* 28 - filter(("CHG"."C_BUSINFLAG"='13' OR "CHG"."C_BUSINFLAG"='14') AND "CHG"."C_TACODE"='F6' AND "CHG"."C_TENANTID"='*' AND "CHG"."C_TACODE"="A"."C_TACODE" AND "CHG"."C_TENANTID"="A"."C_TENANTID"
  AND "CHG"."C_AGENCYNO"="AI"."C_AGENCYNO")
* 30 - filter("FI"."C_TACODE"='F6' AND "FI"."C_TENANTID"='*' AND "FI"."C_TACODE"="A"."C_TACODE" AND "FI"."C_TENANTID"="A"."C_TENANTID")
* 31 - filter("FI"."C_TACODE"(+)='F6' AND "FI"."C_TENANTID"(+)='*')
* 32 - filter("C"."C_TACODE"(+)='F6' AND "C"."C_TENANTID"(+)='*')


Note
-----
- dynamic sampling used for this statement

对应的select部分如下:

SELECT a.c_tenantid,        a.c_tacode,         c_tradeacco,
                   CASE CONCAT(CONCAT(CONCAT('F6' ,a.c_agencyno),c_taflag),a.c_businflag) WHEN '66003153' THEN ' ' ELSE c_cityno END c_cityno,
                   a.c_fundcode,       a.d_requestdate,    d_requesttime,          a.c_businflag,
                   c_requestno,        c_moneytype,        f_shares,               f_balance,
                   f_confirmshares,    f_confirmbalance,
                   CASE a.c_fundacco WHEN ' ' THEN ' ' ELSE (CASE (SELECT t.c_paramvalue FROM ta_textparameter t WHERE c_paramitem='C_FUNDACCO3CHG')
                   WHEN '1' THEN CONCAT('T',SUBSTR(a.c_fundacco,4,9)) ELSE a.c_fundacco END) END c_fundacco,
                   c_custtype,          d_hopedate,         a.c_agencyno,             d_cdate,
                   f_stamptax,          f_bsharefare,       f_changefillfare,         f_agentfare,
                   f_fundfare,          f_interesttax,      f_factinterest,           f_sumfare,
                   c_oricserialno,      f_agio,             c_netno,                  c_otheragency,
                   c_othernetno,
                   CASE CONCAT(CONCAT(a.c_agencyno,a.c_businflag),a.c_adjustcause) WHEN '002704' THEN ' ' ELSE a.c_othertradeacco END c_othertradeacco,
                   CASE a.c_otheracco WHEN ' ' THEN ' ' ELSE (CASE (SELECT t.c_paramvalue FROM ta_textparameter t WHERE c_paramitem='C_FUNDACCO3CHG')
                   WHEN '1' THEN CONCAT('T',SUBSTR(a.c_otheracco,4,9)) ELSE a.c_otheracco END) END c_otheracco,
                   c_cserialno,         c_bonustype,        c_freezecause,            d_freezeenddate,
                   c_orirequestno,      c_chargetype,       f_netvalue,               c_datadetailflag,
                   c_targetchargetype,  c_changeflag,       f_interestshare,          f_backbalance,
                   f_changefare,        f_profitbalance,    f_chincome_abs,           c_incomeflag,
                   f_breachfare,        f_breachfare4fund,  f_raiseinerest,           c_requestendflag,
                   c_exceedflag,        d_registdate,       c_childnetno,             c_taflag,
                   c_cserialnototal,    f_othernetvalue,    f_othercfmshares,         c_protocolno,
                   CASE a.c_sharetypes WHEN 'AB' THEN a.c_fundcode ELSE COALESCE(b.c_outfundcode,a.c_fundcode) END c_txtfundcode,
                   COALESCE(c.c_outcause,a.c_cause) c_txtcause,
                   COALESCE(d.c_confirmflag,CONCAT('1', SUBSTR(a.c_outbusinflag,2,2))) c_txtbusinflag,
                   CASE a.c_sharetypes WHEN 'AB' THEN a.c_othercode ELSE COALESCE(e.c_outfundcode,a.c_othercode) END c_txtothercode,
                   CASE CONCAT(TRIM(COALESCE(c.c_describe,a.c_errordetail)), ' ') WHEN ' ' THEN '0000' ELSE COALESCE(c.c_describe, a.c_errordetail) END c_txterrordetail,
                   ' ' c_custno,        a.c_adjustflag,     a.f_profitcompensation,   a.d_originaldate,
                   a.c_combcode,        a.c_specification,  a.c_forceredemptiontype,  a.f_otherfee2
              FROM (SELECT /*+full(a) full(d) use_hash(d) */ 
                           CASE a.c_fundacco WHEN COALESCE(icbc.c_fundacco,'***' ) THEN COALESCE(icbc.c_tradeacco,a.c_tradeacco) ELSE a.c_tradeacco END c_tradeacco,
                           CASE a.c_agencyno WHEN '002' THEN LPAD(TRIM(a.c_netno),4,'0') ELSE a.c_cityno END c_cityno, a.c_fundcode c_fundcode,
                           CASE CONCAT(CONCAT(a.c_iversion,a.c_businflag),a.c_taflag)
                             WHEN '400031' THEN getrealdays(a.c_tenantid, a.c_fundcode, a.c_agencyno, 20100513,
                                                -1*getsysvalue(a.c_tenantid, a.c_tacode, '*', '*', a.c_fundcode, 'FundTnConfirm', '1'))
                                           ELSE a.d_requestdate
                             END d_requestdate,
                           a.d_requesttime, a.c_tacode, a.c_tenantid,
                           CASE CONCAT(a.c_businflag,a.c_taflag) WHEN '031' THEN '53' ELSE (CASE CONCAT(CONCAT(a.c_iversion,a.c_cause),a.c_businflag) WHEN '400200154' THEN '50' ELSE a.c_businflag END) END c_businflag,
                           CASE CONCAT(a.c_agencyno,a.c_taflag)
                             WHEN '0061' THEN (CASE a.c_outbusinflag WHEN '081' THEN a.c_freezeno ELSE ' ' END)
                             WHEN '0021' THEN (CASE a.c_businflag
                                                 WHEN '50' THEN (CASE a.c_requestno WHEN ' ' THEN a.c_cserialno ELSE a.c_requestno END)
                                                 WHEN '54' THEN (CASE a.c_requestno WHEN ' ' THEN a.c_cserialno ELSE a.c_requestno END)
                                                           ELSE ' '
                                                END)
                                         ELSE (CASE a.c_requestno WHEN ' ' THEN a.c_cserialno ELSE a.c_requestno END)
                            END c_requestno,
                           COALESCE(fi.c_moneytype,'156') c_moneytype,
                           CASE
                             WHEN CONCAT(a.c_businflag,a.c_status) IN ('041','051','061') THEN a.f_confirmshares
                             WHEN CONCAT(CONCAT(CONCAT('0',a.c_businflag),a.c_agencyno),a.c_status) = '1130021' THEN  (CASE a.c_iversion WHEN '400' THEN a.f_shares ELSE a.f_confirmshares END)
                             WHEN (CONCAT(a.c_iversion,a.c_agencyno) = '400002') AND (CONCAT(a.c_status,a.c_businflag) = '103') THEN a.f_confirmshares /*+ a.f_unshares*/
                             ELSE a.f_shares
                           END f_shares,
                           a.f_balance,
                           CASE a.c_iversion WHEN '400' THEN d.f_confirmshares ELSE (CASE /*COALESCE(tai.c_changeonstep,'0')*/CONCAT(CONCAT(CONCAT('0',a.c_businflag),a.c_agencyno),a.c_status)
                           WHEN '1130021' THEN /*COALESCE(chgin.f_confirmshares,0)*/ 0 ELSE d.f_confirmshares END) END  f_confirmshares,
                           CASE d.c_businflag
                             WHEN '70' THEN 0
                             WHEN '71' THEN 0
                             WHEN '13' THEN (CASE a.c_iversion WHEN '400' THEN 1 ELSE 0 END)*(d.f_tradefare+d.f_backfare+d.f_otherfare1)+d.f_confirmbalance
                             WHEN '16' THEN (CASE a.c_iversion WHEN '400' THEN 1 ELSE 0 END)*(d.f_tradefare+d.f_backfare+d.f_otherfare1)+d.f_confirmbalance
                                       ELSE d.f_confirmbalance
                            END f_confirmbalance,
                           a.c_fundacco, a.c_custtype, a.d_hopedate d_hopedate, a.c_agencyno, a.d_outputdate d_cdate,
                           a.c_status, d.f_tradefare, d.f_stamptax, d.f_tafare, d.f_otherfare1, d.f_backfare,
                           CASE a.c_businflag WHEN '03' THEN d.f_tradefare ELSE 0 END f_redeemfare,
                           CASE d.c_businflag WHEN '03' THEN d.f_backfare  ELSE 0 END f_bsharefare,
                           CASE d.c_businflag WHEN '13' THEN d.f_backfare  ELSE 0 END f_changefillfare,
                           0 f_agentfare,
                           0 f_fundfare,
                           CASE a.c_businflag
                             WHEN '54' THEN (CASE a.f_confirmbalance-a.f_frozenbalance WHEN 0 THEN 0 ELSE a.f_interesttax END)
                             WHEN '50' THEN 0
                                       ELSE a.f_interesttax
                            END f_interesttax,
                           0 f_factinterest,
                           (d.f_tradefare+d.f_stamptax+d.f_tafare+d.f_otherfare1+d.f_backfare+d.f_breachfare+d.f_profitbalance) f_sumfare,
                           CASE WHEN
                             'F6' = '27' AND d.c_agencyno = '227' AND a.c_adjustcause<>'1'
                           THEN
                             (CASE a.c_businflag WHEN '15' THEN ' ' WHEN '16' THEN ' ' WHEN '05' THEN ' ' WHEN '70' THEN ' ' WHEN '21' THEN '' ELSE d.c_oricserialno END)
                            ELSE
                              d.c_oricserialno
                            END c_oricserialno,
                           a.f_agio,a.c_netno, a.c_otheragency,a.c_othernetno,a.c_othertradeacco,
                           CASE a.c_businflag
                             WHEN '13' THEN a.c_othercode
                             WHEN '16' THEN a.c_othercode
                             WHEN '70' THEN (CASE a.c_adjustcause WHEN '5' THEN a.c_othercode WHEN 'h' THEN a.c_othercode ELSE ' ' END)
                             WHEN '71' THEN (CASE a.c_adjustcause WHEN '5' THEN a.c_othercode WHEN 'h' THEN a.c_othercode ELSE ' ' END)
                             ELSE ' '
                           END c_othercode,
                           a.c_otheracco, d.c_cserialno,
                           CASE a.c_businflag WHEN '07' THEN a.c_bonustype ELSE ' ' END c_bonustype,
                           a.c_freezecause, a.d_freezeenddate, a.f_frozenbalance,
                           CASE a.c_fundacco
                             WHEN COALESCE(icbc.c_fundacco,'***') THEN (CASE a.c_businflag
                                                                        WHEN '70' THEN a.c_tradeacco
                                                                        WHEN '71' THEN a.c_tradeacco
                                                                                  ELSE (CASE CONCAT(a.c_agencyno,a.c_taflag) WHEN '0021' THEN ' ' ELSE a.c_orirequestno END)
                                                                         END)
                                                                  ELSE (CASE CONCAT(a.c_agencyno,a.c_taflag) WHEN '0021' THEN ' ' ELSE a.c_orirequestno END)
                            END c_orirequestno,
                           d.c_sharetype,
                           CASE d.c_sharetype WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE d.c_sharetype END c_chargetype,
                           a.f_netvalue,
                           CASE a.c_businflag WHEN '13' THEN a.c_othershare WHEN '16' THEN a.c_sharetype WHEN '70' THEN a.c_sharetype WHEN '71' THEN a.c_sharetype ELSE ' ' END c_targetsharetype,
                           '1' c_datadetailflag,
                           CASE a.c_businflag
                             WHEN '13' THEN (CASE a.c_othershare WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_othershare END)
                             WHEN '16' THEN (CASE a.c_sharetype  WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_sharetype END)
                             WHEN '70' THEN (CASE a.c_sharetype  WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_sharetype END)
                             WHEN '71' THEN (CASE a.c_sharetype  WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE a.c_sharetype END)
                                       ELSE ' '
                            END c_targetchargetype,
                           CASE a.c_businflag WHEN '13' THEN '0'  WHEN '16' THEN '1' WHEN '12' THEN '0' WHEN '14' THEN '0' WHEN '15' THEN '1' ELSE ' ' END c_changeflag,
                           0 f_interestshare, 0 f_backbalance,
                           CASE a.c_businflag WHEN '13' THEN d.f_tradefare ELSE 0 END f_changefare,
                           d.f_profitbalance, 0 f_chincome_abs, '0' c_incomeflag, d.f_breachfare, 0 f_breachfare4fund,
                           0 f_raiseinerest, a.c_requestendflag,
                           CASE a.c_requestendflag WHEN '1' THEN '0' ELSE a.c_exceedflag END c_exceedflag,
                           COALESCE(d.d_registdate,a.d_cdate) d_registdate,
                           SUBSTR(a.c_cause,5,100) c_errordetail,
                           a.c_childnetno,
                           CASE CONCAT(CONCAT(a.c_iversion,a.c_outbusinflag),a.c_businflag) WHEN '40002605' THEN '1' ELSE a.c_taflag END c_taflag,
                           CASE a.c_iversion
                             WHEN '400' THEN (CASE d.c_businflag
                                                WHEN '16' THEN (CASE a.c_tacode   WHEN 'F6'     THEN chgout.c_cserialno ELSE d.c_cserialnototal END)   /*3 c_tacode*/
                                                WHEN '15' THEN (CASE a.c_agencyno WHEN a.c_otheragency THEN d.c_cserialnoout   ELSE d.c_cserialnototal END)
                                                          ELSE d.c_cserialnototal
                                                END)
                                          ELSE d.c_cserialnototal
                            END c_cserialnototal,
                           CASE a.c_businflag
                             WHEN '14' THEN (CASE CONCAT(a.c_iversion,a.c_outbusinflag) WHEN '400033'THEN (CASE a.c_agencyno WHEN a.c_otheragency THEN '133' ELSE '135' END) ELSE a.c_outbusinflag END)
                             WHEN '15' THEN (CASE CONCAT(a.c_iversion,a.c_outbusinflag) WHEN '400033'THEN (CASE a.c_agencyno WHEN a.c_otheragency THEN '133' ELSE '134' END) ELSE a.c_outbusinflag END)
                             WHEN '12' THEN (CASE CONCAT(a.c_iversion,a.c_outbusinflag) WHEN '400033'THEN '133' ELSE a.c_outbusinflag END)
                             WHEN '13' THEN (CASE a.c_iversion WHEN '400' THEN (CASE (CASE a.c_tacode WHEN ' ' THEN 'F6' ELSE a.c_tacode END) WHEN 'F6' THEN '136' ELSE '038' END) ELSE (CASE /*nvl(tai.c_changeonstep,'0')*/'0' WHEN '1' THEN '***' ELSE a.c_outbusinflag END) END)
                             WHEN '16' THEN (CASE a.c_iversion WHEN '400' THEN (CASE (CASE a.c_tacode WHEN ' ' THEN 'F6' ELSE a.c_tacode END) WHEN 'F6' THEN '136' ELSE '038' END) ELSE (CASE /*nvl(tai.c_changeonstep,'0')*/'0' WHEN '1' THEN '***' ELSE a.c_outbusinflag END) END)
                             WHEN '04' THEN (CASE a.c_iversion WHEN '400' THEN '126' ELSE (CASE a.c_agencyno WHEN '006' THEN '126' ELSE a.c_outbusinflag END) END)
                             WHEN '05' THEN (CASE a.c_iversion WHEN '400' THEN '127' ELSE (CASE CONCAT(a.c_agencyno,a.c_outbusinflag) WHEN '006026' THEN '*' ELSE a.c_outbusinflag END) END)
                             WHEN '06' THEN (CASE a.c_iversion WHEN '400' THEN (CASE a.c_outbusinflag WHEN '026' THEN '126' ELSE a.c_outbusinflag END) ELSE (CASE CONCAT(a.c_agencyno,a.c_outbusinflag) WHEN '006026' THEN '126' ELSE a.c_outbusinflag END) END)
                                       ELSE a.c_outbusinflag
                            END c_outbusinflag,
                           a.f_otherprice f_othernetvalue,
                           CASE /*tai.c_changeonstep*/CONCAT(CONCAT('0',a.c_businflag),a.c_status)  WHEN '1131' THEN (CASE a.c_agencyno WHEN '002'THEN 0 ELSE /*COALESCE(chgin.f_confirmshares,0)*/ 0 END) ELSE 0 END f_othercfmshares,
                           CASE CONCAT(a.c_iversion,a.c_agencyno) WHEN '400002' THEN (CASE INSTR('039059060061',a.c_outbusinflag) WHEN 0 THEN '' ELSE (CASE CONCAT(TRIM(a.c_otheracco),' ') WHEN ' ' THEN a.c_protocolno ELSE TRIM(a.c_otheracco) END) END) ELSE a.c_protocolno END c_protocolno,
                           a.c_custno,
                           CASE WHEN (a.c_adjustcause = '5') AND (a.c_agencyno = '004') THEN '8' ELSE '' END c_adjustflag,
                           0 f_profitcompensation,  d.d_originaldate, ' ' c_combcode, a.c_adjustcause, a.c_memo c_specification,
                           a.c_cause, a.c_forceredemptiontype,
                           CASE d.c_sharetype WHEN 'C' THEN d.f_otherfare1 ELSE 0 END f_otherfee2,
                           a.c_sharetypes 
                      FROM "sett_ta_tconfirm_*_F6" a
                           LEFT JOIN ta_tfundinfo fi
                                  ON (a.c_fundcode  = fi.c_fundcode
                                 AND  a.c_tacode    = fi.c_tacode
                                 AND  a.c_tenantid  = fi.c_tenantid)
                           LEFT JOIN ta_tnetvalueday c
                                  ON (a.c_othercode = c.c_fundcode
                                 AND  a.d_cdate     = c.d_cdate
                                 AND  a.c_tacode    = c.c_tacode
                                 AND  a.c_tenantid  = c.c_tenantid)
                           LEFT JOIN (SELECT /*+ no_merge */chg.d_cdate, chg.c_cserialno, chg.c_requestno,
                                             chg.c_agencyno, chg.c_tacode, chg.c_tenantid
                                        FROM "sett_ta_tconfirm_*_F6" chg ,
                                             ta_tagencyexpbatch ai,
                                             ta_tfundinfo fi
                                       WHERE chg.c_agencyno  = ai.c_agencyno
                                         AND chg.c_tacode    = ai.c_tacode
                                         AND chg.c_tenantid  = ai.c_tenantid
                                         AND chg.c_fundcode  = fi.c_fundcode
                                         AND chg.c_businflag IN ('13','14')
                                         AND chg.c_tacode    = fi.c_tacode
                                         AND chg.c_tenantid  = fi.c_tenantid) chgout
                                  ON (a.c_requestno = chgout.c_requestno
                                 AND  a.d_cdate     = chgout.d_cdate
                                 AND  a.c_agencyno  = chgout.c_agencyno
                                 AND  a.c_tacode    = chgout.c_tacode
                                 AND  a.c_tenantid  = chgout.c_tenantid),
                           ta_tconfirmdetail d
                           LEFT JOIN (SELECT /*+ no_merge */c_fundacco, c_agencyno, c_netno,
                                             c_tradeacco, c_tacode, c_tenantid
                                        FROM ta_tacconet
                                       WHERE c_fundacco = COALESCE((SELECT c_paramvalue
                                                                      FROM ta_textparameter
                                                                     WHERE c_paramclass = 'TADeal'
                                                                       AND c_paramitem = 'ICBCFundacco'),'***' )) icbc
                                  ON (d.c_fundacco = icbc.c_fundacco
                                 AND  d.c_agencyno = icbc.c_agencyno
                                 AND  d.c_netno    = icbc.c_netno
                                 AND  d.c_tacode   = icbc.c_tacode
                                 AND  d.c_tenantid = icbc.c_tenantid)
                     WHERE a.c_tacode        = 'F6'
                       AND a.c_tenantid      = '*'
                       AND a.d_cdate         = d.d_cdate
                       AND a.c_cserialno     = d.c_cserialnototal
                       AND a.c_tenantid      = d.c_tenantid
                       AND d.f_confirmshares > 0
                       AND CONCAT(a.c_taflag, a.c_status) <>'12'
                       AND CONCAT(a.c_businflag, a.c_outbusinflag) <> '15088'
                       AND CONCAT(CONCAT(CONCAT(a.c_agencyno,a.c_taflag),a.c_businflag),a.c_cause)<>'002170GHYH'
                       AND CONCAT(CONCAT(CONCAT(a.c_agencyno,a.c_taflag),a.c_businflag),a.c_cause)<>'002171GHYH'
                       AND a.c_isdetail    = '1'
                       AND (a.c_agencyno <> '207' OR a.c_businflag NOT IN ('13','16'))
                   ) a
                   LEFT JOIN ta_tfundcodechange b
                          ON (a.c_fundcode        = b.c_fundcode
                         AND  a.c_sharetype       = b.c_sharetype
                         AND  a.c_tacode          = b.c_tacode
                         AND  a.c_tenantid        = b.c_tenantid)
                   LEFT JOIN ta_terrormess c
                          ON (a.c_cause           = c.c_cause
                         AND  a.c_tenantid        = c.c_tenantid)
                   LEFT JOIN ta_tbusinchangeout d
                          ON (a.c_businflag       = d.c_businflag
                         AND  a.c_outbusinflag    = d.c_requestflag
                         AND  a.c_tenantid        = d.c_tenantid)
                   LEFT JOIN ta_tfundcodechange e
                          ON (a.c_othercode       = e.c_fundcode
                         AND  a.c_targetsharetype = e.c_sharetype
                         AND  a.c_tacode          = b.c_tacode
                         AND  a.c_tenantid        = e.c_tenantid);
 Plan Hash Value  : 

-------------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                        | Name                    | Rows    | Bytes      | Cost  | Time     |
-------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                                 |                         |       1 |        756 | 57603 | 00:11:32 |
|  * 1 |   INDEX RANGE SCAN                               | IDX_TEXTPARAMETER_VALUE |       2 |         36 |     1 | 00:00:01 |
|  * 2 |   INDEX RANGE SCAN                               | IDX_TEXTPARAMETER_VALUE |       2 |         36 |     1 | 00:00:01 |
|  * 3 |   HASH JOIN OUTER                                |                         |       1 |        756 | 57603 | 00:11:32 |
|    4 |    VIEW                                          |                         |       1 |        725 | 57601 | 00:11:32 |
|  * 5 |     HASH JOIN OUTER                              |                         |       1 |        740 | 57601 | 00:11:32 |
|  * 6 |      HASH JOIN OUTER                             |                         |       1 |        705 | 57598 | 00:11:32 |
|  * 7 |       HASH JOIN OUTER                            |                         |       1 |        692 | 57594 | 00:11:32 |
|    8 |        VIEW                                      |                         |       1 |        658 | 57592 | 00:11:32 |
|  * 9 |         HASH JOIN OUTER                          |                         |       1 |        904 | 57592 | 00:11:32 |
| * 10 |          HASH JOIN OUTER                         |                         |       1 |        886 | 57574 | 00:11:31 |
| * 11 |           HASH JOIN OUTER                        |                         |       1 |        826 | 28866 | 00:05:47 |
| * 12 |            HASH JOIN                             |                         |       1 |        810 | 28848 | 00:05:47 |
| * 13 |             HASH JOIN OUTER                      |                         |       1 |        295 |     3 | 00:00:01 |
| * 14 |              TABLE ACCESS FULL                   | TA_TCONFIRMDETAIL       |       1 |        245 |     2 | 00:00:01 |
|   15 |              VIEW                                |                         |       1 |         50 |     1 | 00:00:01 |
|   16 |               TABLE ACCESS BY GLOBAL INDEX ROWID | TA_TACCONET             |       1 |         40 |     1 | 00:00:01 |
| * 17 |                INDEX RANGE SCAN                  | PK_TACCONET             |       1 |            |     1 | 00:00:01 |
| * 18 |                 TABLE ACCESS BY INDEX ROWID      | TA_TEXTPARAMETER        |       1 |         25 |     1 | 00:00:01 |
| * 19 |                  INDEX RANGE SCAN                | IDX_TEXTPARAMETER_VALUE |     170 |            |     1 | 00:00:01 |
| * 20 |             TABLE ACCESS FULL                    | sett_ta_tconfirm_*_F6   | 2201315 | 1133677225 | 28839 | 00:05:47 |
| * 21 |            TABLE ACCESS FULL                     | TA_TFUNDINFO            |     401 |       6416 |    18 | 00:00:01 |
|   22 |           VIEW                                   |                         |    1149 |      68940 | 28708 | 00:05:45 |
| * 23 |            HASH JOIN                             |                         |    1149 |     105708 | 28708 | 00:05:45 |
| * 24 |             TABLE ACCESS FULL                    | TA_TFUNDINFO            |     401 |       4812 |    18 | 00:00:01 |
| * 25 |             HASH JOIN                            |                         |    1152 |      92160 | 28690 | 00:05:45 |
| * 26 |              TABLE ACCESS FULL                   | TA_TAGENCYEXPBATCH      |      99 |        891 |     3 | 00:00:01 |
| * 27 |              TABLE ACCESS FULL                   | sett_ta_tconfirm_*_F6   |    1164 |      82644 | 28687 | 00:05:45 |
| * 28 |          TABLE ACCESS FULL                       | TA_TNETVALUEDAY         |    2411 |      43398 |    18 | 00:00:01 |
|   29 |        TABLE ACCESS FULL                         | TA_TFUNDCODECHANGE      |       1 |         34 |     2 | 00:00:01 |
|   30 |       TABLE ACCESS FULL                          | TA_TBUSINCHANGEOUT      |      83 |       1079 |     3 | 00:00:01 |
|   31 |      TABLE ACCESS FULL                           | TA_TERRORMESS           |     311 |      10885 |     3 | 00:00:01 |
|   32 |    TABLE ACCESS FULL                             | TA_TFUNDCODECHANGE      |       1 |         31 |     2 | 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("C_PARAMITEM"='C_FUNDACCO3CHG')
* 2 - access("C_PARAMITEM"='C_FUNDACCO3CHG')
* 3 - access("A"."C_TENANTID"="E"."C_TENANTID"(+) AND "A"."C_TARGETSHARETYPE"="E"."C_SHARETYPE"(+) AND "A"."C_OTHERCODE"="E"."C_FUNDCODE"(+))
* 3 - filter("B"."C_TACODE"=CASE WHEN ("E"."C_FUNDCODE"(+) IS NOT NULL) THEN "A"."C_TACODE" ELSE "A"."C_TACODE" END )
* 5 - access("A"."C_TENANTID"="C"."C_TENANTID"(+) AND "A"."C_CAUSE"="C"."C_CAUSE"(+))
* 6 - access("A"."C_TENANTID"="D"."C_TENANTID"(+) AND "A"."C_OUTBUSINFLAG"="D"."C_REQUESTFLAG"(+) AND "A"."C_BUSINFLAG"="D"."C_BUSINFLAG"(+))
* 7 - access("A"."C_TENANTID"="B"."C_TENANTID"(+) AND "A"."C_TACODE"="B"."C_TACODE"(+) AND "A"."C_SHARETYPE"="B"."C_SHARETYPE"(+) AND "A"."C_FUNDCODE"="B"."C_FUNDCODE"(+))
* 9 - access("A"."C_TENANTID"="C"."C_TENANTID"(+) AND "A"."C_TACODE"="C"."C_TACODE"(+) AND "A"."D_CDATE"="C"."D_CDATE"(+) AND "A"."C_OTHERCODE"="C"."C_FUNDCODE"(+))
* 10 - access("A"."C_TENANTID"="CHGOUT"."C_TENANTID"(+) AND "A"."C_TACODE"="CHGOUT"."C_TACODE"(+) AND "A"."C_AGENCYNO"="CHGOUT"."C_AGENCYNO"(+) AND "A"."D_CDATE"="CHGOUT"."D_CDATE"(+) AND
  "A"."C_REQUESTNO"="CHGOUT"."C_REQUESTNO"(+))
* 11 - access("A"."C_TENANTID"="FI"."C_TENANTID"(+) AND "A"."C_TACODE"="FI"."C_TACODE"(+) AND "A"."C_FUNDCODE"="FI"."C_FUNDCODE"(+))
* 12 - access("A"."D_CDATE"="D"."D_CDATE" AND "A"."C_CSERIALNO"="D"."C_CSERIALNOTOTAL" AND "A"."C_TENANTID"="D"."C_TENANTID")
* 13 - access("D"."C_TENANTID"="ICBC"."C_TENANTID"(+) AND "D"."C_TACODE"="ICBC"."C_TACODE"(+) AND "D"."C_NETNO"="ICBC"."C_NETNO"(+) AND "D"."C_AGENCYNO"="ICBC"."C_AGENCYNO"(+) AND
  "D"."C_FUNDACCO"="ICBC"."C_FUNDACCO"(+))
* 14 - filter("D"."C_TENANTID"='*' AND "D"."F_CONFIRMSHARES">0)
* 17 - access("C_FUNDACCO"=COALESCE( (SELECT "C_PARAMVALUE" FROM "TA_TEXTPARAMETER" "TA_TEXTPARAMETER" WHERE "C_PARAMITEM"='ICBCFundacco' AND "C_PARAMCLASS"='TADeal'),'***') AND "C_TENANTID"='*')
* 17 - filter("C_TENANTID"='*')
* 18 - filter("C_PARAMCLASS"='TADeal')
* 19 - access("C_PARAMITEM"='ICBCFundacco')
* 20 - filter("A"."C_BUSINFLAG"||"A"."C_OUTBUSINFLAG"<>'15088' AND "A"."C_ISDETAIL"='1' AND "A"."C_TAFLAG"||"A"."C_STATUS"<>'12' AND
  "A"."C_AGENCYNO"||"A"."C_TAFLAG"||"A"."C_BUSINFLAG"||"A"."C_CAUSE"<>'002170GHYH' AND "A"."C_AGENCYNO"||"A"."C_TAFLAG"||"A"."C_BUSINFLAG"||"A"."C_CAUSE"<>'002171GHYH' AND ("A"."C_AGENCYNO"<>'207' OR
  "A"."C_BUSINFLAG"<>'13' AND "A"."C_BUSINFLAG"<>'16') AND "A"."C_TACODE"='F6' AND "A"."C_TENANTID"='*')
* 21 - filter("FI"."C_TACODE"(+)='F6' AND "FI"."C_TENANTID"(+)='*')
* 23 - access("CHG"."C_FUNDCODE"="FI"."C_FUNDCODE" AND "CHG"."C_TACODE"="FI"."C_TACODE" AND "CHG"."C_TENANTID"="FI"."C_TENANTID")
* 24 - filter("FI"."C_TACODE"='F6' AND "FI"."C_TENANTID"='*')
* 25 - access("CHG"."C_AGENCYNO"="AI"."C_AGENCYNO" AND "CHG"."C_TACODE"="AI"."C_TACODE" AND "CHG"."C_TENANTID"="AI"."C_TENANTID")
* 26 - filter("AI"."C_TACODE"='F6' AND "AI"."C_TENANTID"='*')
* 27 - filter(("CHG"."C_BUSINFLAG"='13' OR "CHG"."C_BUSINFLAG"='14') AND "CHG"."C_TACODE"='F6' AND "CHG"."C_TENANTID"='*')
* 28 - filter("C"."C_TACODE"(+)='F6' AND "C"."C_TENANTID"(+)='*')

解决方法也很简单,只需要固定执行计划即可,只是需要注意下这种情况会出现即可,不要直接select拷贝到insert select就认为肯定相同。

上一篇:Android Framework | 消息机制的冷门知识点,你能答出来几个?


下一篇:全网最新最全首届“陇剑杯”网络安全大赛完整WIRTEUP --- ios(7题)