C/C++教程
oracle insert select和select执行计划不同解决
本文主要是介绍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就认为肯定相同。
这篇关于oracle insert select和select执行计划不同解决的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
增量更新怎么做？-icode9专业技术文章分享11-23
-
压缩包加密方案有哪些？-icode9专业技术文章分享11-23
-
用shell怎么写一个开机时自动同步远程仓库的代码？-icode9专业技术文章分享11-23
-
webman可以同步自己的仓库吗？-icode9专业技术文章分享11-23
-
在 Webman 中怎么判断是否有某命令进程正在运行？-icode9专业技术文章分享11-23
-
如何重置new Swiper?-icode9专业技术文章分享11-23
-
oss直传有什么好处？-icode9专业技术文章分享11-23
-
如何将oss直传封装成一个组件在其他页面调用时都可以使用？-icode9专业技术文章分享11-23
-
怎么使用laravel 11在代码里获取路由列表？-icode9专业技术文章分享11-23
-
怎么实现ansible playbook 备份代码中命名包含时间戳功能？-icode9专业技术文章分享11-22
-
ansible 的archive 参数是什么意思？-icode9专业技术文章分享11-22
-
ansible 中怎么只用archive 排除某个目录？-icode9专业技术文章分享11-22
-
exclude_path参数是什么作用？-icode9专业技术文章分享11-22
-
微信开放平台第三方平台什么时候调用数据预拉取和数据周期性更新接口？-icode9专业技术文章分享11-22
-
uniapp 实现聊天消息会话的列表功能怎么实现？-icode9专业技术文章分享11-22
栏目导航
