之前有提到，一个查询独立执行的时候走的是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就认为肯定相同。
