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就认为肯定相同。
