insert into TAjz_jdushjouhu
(
turkno_kbncd,
turkno_bnrino,
turkno_knmj,
turkno_ichrnno,
rrkymd,
rirekiNo,
jichtiCd,
shkturkgyumnchj,
kusngyumnchj,
kusnsystemnchj,
kusncompmi,
kusnuserid,
yukoFlag,
kssijouti,
kyujchticd,
shdturknngtu,
turkb,
shknb,
bnpishrb,
gyumshbtucd,
yutbnpijouhucd,
ktskstino,
ribtukbnno,
bnpikijoucd,
tiinkbn,
tiin1,
tiin2,
sksiryu1,
sksiryu2,
shryujuuryu,
shryusujuuryu1,
shryusujuuryu2,
shryutks,
shryungs,
shryuhb,
nnryucd,
tshkcd,
hishtugstkgucd,
ktskcd,
ktskno,
bnpiskbtucd,
jdushgndukktsk,
hikryushbtucd,
jdushhikryu,
mkcdbnpijouhu,
shmi,
shdino,
kzicdjdushzi,
kzicdhnkub,
kzicdktugb,
kzicdsnsib,
knzicd,
shyukiti,
yuttukiyucd,
hkkuhryuflg,
jdushzirtucd,
zigk,
joutibtt,
shmifmiflg,
grnkkiktishukbn,
bskzikbn,
eijcd,
syunhnky,
shtkgiin,
shknmnryub,
yutsnkkshcd,
snkkshshbtucd,
grnkrtufgu,
grnkrtupsnt,
tinnpshtkricd,
nnpkjuntssi,
kngitnshturrkymd,
kngitnshtunohyucd,
kngitnshtubnrino,
kngitnshtuknmj,
kngitnshtuichrnno,
knnitnnyuymd,
knnitnnyunohyucd,
knnitnnyubnrino,
knnitnnyuknmj,
knnitnnyuichrnno,
knnitnnyufkjshdino
)
select
a.KBN_COD turkno_kbncd,
a.BNR_BNG turkno_bnrino,
a.KNA_MOJ turkno_knmj,
a.ITR_BNG turkno_ichrnno,
fncConvertDate(a.RRK_YYY,a.RRK_MMM,a.RRK_DDD) rrkymd,
1 rirekiNo,
'000000' jichtiCd,
fncSetHzk_Jkk(a.DBK_DAT_SSI_HZK, a.DBK_DAT_SSI_JKK) shkturkgyumnchj,
fncSetHzk_Jkk(a.DBK_DAT_UPD_HZK, a.DBK_DAT_UPD_JKK) kusngyumnchj,
fncSetHzk_Jkk(a.DBK_DAT_UPD_HZK, a.DBK_DAT_UPD_JKK) kusnsystemnchj,
'ikou' kusncompmi,
'ikou' kusnuserid,
'1' yukoFlag,
'0' kssijouti,
'000000' kyujchticd,
fncConvertDate(a.SYD_TRK_YYY,a.SYD_TRK_MMM,0) shdturknngtu,
fncConvertDate(a.TRK_YYY,a.TRK_MMM,a.TRK_DDD) turkb,
fncConvertDate(a.SKN_YYY,a.SKN_MMM,a.SKN_DDD) shknb,
fncConvertDate(a.BNP_SYR_YYY,a.BNP_SYR_MMM,a.BNP_SYR_DDD) bnpishrb,
a.GYM_SBT_COD gyumshbtucd,
fncChangeCode('17',a.YOT_COD) yutbnpijouhucd,
a.KTS_SIT_BNG ktskstino,
a.RIB_KBN_BNG ribtukbnno,
fncChangeCode('18',a.KEJ_COD) bnpikijoucd,
fncChangeCode('19',a.TEI_KBN) tiinkbn,
a.TEI_1 tiin1,
a.TEI_2 tiin2,
a.SSK_1 sksiryu1,
a.SSK_2 sksiryu2,
a.SYA_JRY shryujuuryu,
0 shryusujuuryu1,
0 shryusujuuryu2,
a.SYA_TKS shryutks,
a.SYA_NGS shryungs,
a.SYA_HBA shryuhb,
fncChangeCode('20',a.NER_COD) nnryucd,
fncChangeCode('21',a.TOS_COD) tshkcd,
(case when nvl(a.HGT_COD,' ') <> ' ' then fncChangeCode('22',a.HGT_COD) else fncChangeCode('22',b.税率特例コード) end) hishtugstkgucd,
fncChangeCode('23',a.KTS_COD) ktskcd,
a.KTS_BNG ktskno,
fncChangeCode('24',a.GND_SBT_COD) bnpiskbtucd,
a.GND_KTS jdushgndukktsk,
a.HKR_SBT_COD hikryushbtucd,
a.HKR jdushhikryu,
fncChangeCode('25',a.MAR_COD) mkcdbnpijouhu,
a.SME shmi,
a.SDY_BNG shdino,
(case when nvl(a.KAZ_COD,' ') <> ' ' then
case when nvl(a.KAZ_COD,' ') = 'N'
then
case when nvl(a.KAZ_HRY_GEK_COD,' ') = '5'
then
'55'
else
fncChangeCode('32',a.KAZ_HRY_GEK_COD)
end
else
fncChangeCode('31',a.KAZ_COD)
end
else
case when nvl(a.KAZ_HRY_GEK_COD,' ') = '5'
then
'75'
end
end) kzicdjdushzi,
fncConvertDate(a.KAZ_COD_HEN_YYY,a.KAZ_COD_HEN_MMM,a.KAZ_COD_HEN_DDD) kzicdhnkub,
fncConvertDate(a.KAZ_COD_KTG_YYY,a.KAZ_COD_KTG_MMM,a.KAZ_COD_KTG_DDD) kzicdktugb,
fncConvertDate(a.KAZ_COD_SNI_YYY,a.KAZ_COD_SNI_MMM,a.KAZ_COD_SNI_DDD) kzicdsnsib,
'0' || a.KNZ_COD knzicd,
(case when nvl(b.shyushkbn,' ') = '1' then '1'
when nvl(b.shyushkbn,' ') = '0' then
case when nvl(a.RIS_COD,' ') = '1' then '4'
when nvl(a.RIS_COD,' ') = '0' then
case when nvl(b.減免等コード,' ') = '2' then '3'
else '2'
end
end
else
'6'
end) shyukiti,
fncChangeCode('26',a.YOT_KBN) yuttukiyucd,
(case when a.HKO_TIS_FLG = '' then '' when a.HKO_TIS_FLG = '1' then '1' else null end) hkkuhryuflg,
a.ZIR_COD jdushzirtucd,
a.ZGK zigk,
a.JTI_BIT joutibtt,
a.SME_FMI_FLG shmifmiflg,
fncChangeCode('27',a.RDT_KAZ_FLG) grnkkiktishukbn,
fncChangeCode('28',a.YOT_KBN) bskzikbn,
fncChangeCode('29',a.YOT_COD) eijcd,
null syunhnky,
null shtkgiin,
fncConvertDate(a.SKN_YYY,a.SKN_MMM,a.SKN_DDD) shknmnryub,
null yutsnkkshcd,
null snkkshshbtucd,
a.GRN_FGU grnkrtufgu,
a.GRN_PER grnkrtupsnt,
null tinnpshtkricd,
a.NNP_KJN_005 nnpkjuntssi,
fncConvertDate(trim(a.KGI_TEN_RRK_YYY),trim(a.KGI_TEN_RRK_MMM),trim(a.KGI_TEN_RRK_DDD)) kngitnshturrkymd,
a.KGI_TEN_BGH_COD kngitnshtunohyucd,
a.KGI_TEN_BNR_BNG kngitnshtubnrino,
a.KGI_TEN_KNA_MOJ kngitnshtuknmj,
a.KGI_TEN_ITR_BNG kngitnshtuichrnno,
fncConvertDate(trim(a.KNI_TNY_YYY),trim(a.KNI_TNY_MMM),trim(a.KNI_TNY_DDD)) knnitnnyuymd,
a.KNI_TNY_BGH_COD knnitnnyunohyucd,
a.KNI_TNY_BNR_BNG knnitnnyubnrino,
a.KNI_TNY_KNA_MOJ knnitnnyuknmj,
a.KNI_TNY_ITR_BNG knnitnnyuichrnno,
a.KNI_TNY_FKD_SDY_BNG knnitnnyufkjshdino
from
JDOSYA_JOHO a
left join SHYUKITI_JDUSHZI b on
a.KBN_COD = b.kbncd and
a.BNR_BNG = b.bnrinocd and
a.KNA_MOJ = b.knmj and
a.ITR_BNG = b.ichrnno;
(
turkno_kbncd,
turkno_bnrino,
turkno_knmj,
turkno_ichrnno,
rrkymd,
rirekiNo,
jichtiCd,
shkturkgyumnchj,
kusngyumnchj,
kusnsystemnchj,
kusncompmi,
kusnuserid,
yukoFlag,
kssijouti,
kyujchticd,
shdturknngtu,
turkb,
shknb,
bnpishrb,
gyumshbtucd,
yutbnpijouhucd,
ktskstino,
ribtukbnno,
bnpikijoucd,
tiinkbn,
tiin1,
tiin2,
sksiryu1,
sksiryu2,
shryujuuryu,
shryusujuuryu1,
shryusujuuryu2,
shryutks,
shryungs,
shryuhb,
nnryucd,
tshkcd,
hishtugstkgucd,
ktskcd,
ktskno,
bnpiskbtucd,
jdushgndukktsk,
hikryushbtucd,
jdushhikryu,
mkcdbnpijouhu,
shmi,
shdino,
kzicdjdushzi,
kzicdhnkub,
kzicdktugb,
kzicdsnsib,
knzicd,
shyukiti,
yuttukiyucd,
hkkuhryuflg,
jdushzirtucd,
zigk,
joutibtt,
shmifmiflg,
grnkkiktishukbn,
bskzikbn,
eijcd,
syunhnky,
shtkgiin,
shknmnryub,
yutsnkkshcd,
snkkshshbtucd,
grnkrtufgu,
grnkrtupsnt,
tinnpshtkricd,
nnpkjuntssi,
kngitnshturrkymd,
kngitnshtunohyucd,
kngitnshtubnrino,
kngitnshtuknmj,
kngitnshtuichrnno,
knnitnnyuymd,
knnitnnyunohyucd,
knnitnnyubnrino,
knnitnnyuknmj,
knnitnnyuichrnno,
knnitnnyufkjshdino
)
select
a.KBN_COD turkno_kbncd,
a.BNR_BNG turkno_bnrino,
a.KNA_MOJ turkno_knmj,
a.ITR_BNG turkno_ichrnno,
fncConvertDate(a.RRK_YYY,a.RRK_MMM,a.RRK_DDD) rrkymd,
1 rirekiNo,
'000000' jichtiCd,
fncSetHzk_Jkk(a.DBK_DAT_SSI_HZK, a.DBK_DAT_SSI_JKK) shkturkgyumnchj,
fncSetHzk_Jkk(a.DBK_DAT_UPD_HZK, a.DBK_DAT_UPD_JKK) kusngyumnchj,
fncSetHzk_Jkk(a.DBK_DAT_UPD_HZK, a.DBK_DAT_UPD_JKK) kusnsystemnchj,
'ikou' kusncompmi,
'ikou' kusnuserid,
'1' yukoFlag,
'0' kssijouti,
'000000' kyujchticd,
fncConvertDate(a.SYD_TRK_YYY,a.SYD_TRK_MMM,0) shdturknngtu,
fncConvertDate(a.TRK_YYY,a.TRK_MMM,a.TRK_DDD) turkb,
fncConvertDate(a.SKN_YYY,a.SKN_MMM,a.SKN_DDD) shknb,
fncConvertDate(a.BNP_SYR_YYY,a.BNP_SYR_MMM,a.BNP_SYR_DDD) bnpishrb,
a.GYM_SBT_COD gyumshbtucd,
fncChangeCode('17',a.YOT_COD) yutbnpijouhucd,
a.KTS_SIT_BNG ktskstino,
a.RIB_KBN_BNG ribtukbnno,
fncChangeCode('18',a.KEJ_COD) bnpikijoucd,
fncChangeCode('19',a.TEI_KBN) tiinkbn,
a.TEI_1 tiin1,
a.TEI_2 tiin2,
a.SSK_1 sksiryu1,
a.SSK_2 sksiryu2,
a.SYA_JRY shryujuuryu,
0 shryusujuuryu1,
0 shryusujuuryu2,
a.SYA_TKS shryutks,
a.SYA_NGS shryungs,
a.SYA_HBA shryuhb,
fncChangeCode('20',a.NER_COD) nnryucd,
fncChangeCode('21',a.TOS_COD) tshkcd,
(case when nvl(a.HGT_COD,' ') <> ' ' then fncChangeCode('22',a.HGT_COD) else fncChangeCode('22',b.税率特例コード) end) hishtugstkgucd,
fncChangeCode('23',a.KTS_COD) ktskcd,
a.KTS_BNG ktskno,
fncChangeCode('24',a.GND_SBT_COD) bnpiskbtucd,
a.GND_KTS jdushgndukktsk,
a.HKR_SBT_COD hikryushbtucd,
a.HKR jdushhikryu,
fncChangeCode('25',a.MAR_COD) mkcdbnpijouhu,
a.SME shmi,
a.SDY_BNG shdino,
(case when nvl(a.KAZ_COD,' ') <> ' ' then
case when nvl(a.KAZ_COD,' ') = 'N'
then
case when nvl(a.KAZ_HRY_GEK_COD,' ') = '5'
then
'55'
else
fncChangeCode('32',a.KAZ_HRY_GEK_COD)
end
else
fncChangeCode('31',a.KAZ_COD)
end
else
case when nvl(a.KAZ_HRY_GEK_COD,' ') = '5'
then
'75'
end
end) kzicdjdushzi,
fncConvertDate(a.KAZ_COD_HEN_YYY,a.KAZ_COD_HEN_MMM,a.KAZ_COD_HEN_DDD) kzicdhnkub,
fncConvertDate(a.KAZ_COD_KTG_YYY,a.KAZ_COD_KTG_MMM,a.KAZ_COD_KTG_DDD) kzicdktugb,
fncConvertDate(a.KAZ_COD_SNI_YYY,a.KAZ_COD_SNI_MMM,a.KAZ_COD_SNI_DDD) kzicdsnsib,
'0' || a.KNZ_COD knzicd,
(case when nvl(b.shyushkbn,' ') = '1' then '1'
when nvl(b.shyushkbn,' ') = '0' then
case when nvl(a.RIS_COD,' ') = '1' then '4'
when nvl(a.RIS_COD,' ') = '0' then
case when nvl(b.減免等コード,' ') = '2' then '3'
else '2'
end
end
else
'6'
end) shyukiti,
fncChangeCode('26',a.YOT_KBN) yuttukiyucd,
(case when a.HKO_TIS_FLG = '' then '' when a.HKO_TIS_FLG = '1' then '1' else null end) hkkuhryuflg,
a.ZIR_COD jdushzirtucd,
a.ZGK zigk,
a.JTI_BIT joutibtt,
a.SME_FMI_FLG shmifmiflg,
fncChangeCode('27',a.RDT_KAZ_FLG) grnkkiktishukbn,
fncChangeCode('28',a.YOT_KBN) bskzikbn,
fncChangeCode('29',a.YOT_COD) eijcd,
null syunhnky,
null shtkgiin,
fncConvertDate(a.SKN_YYY,a.SKN_MMM,a.SKN_DDD) shknmnryub,
null yutsnkkshcd,
null snkkshshbtucd,
a.GRN_FGU grnkrtufgu,
a.GRN_PER grnkrtupsnt,
null tinnpshtkricd,
a.NNP_KJN_005 nnpkjuntssi,
fncConvertDate(trim(a.KGI_TEN_RRK_YYY),trim(a.KGI_TEN_RRK_MMM),trim(a.KGI_TEN_RRK_DDD)) kngitnshturrkymd,
a.KGI_TEN_BGH_COD kngitnshtunohyucd,
a.KGI_TEN_BNR_BNG kngitnshtubnrino,
a.KGI_TEN_KNA_MOJ kngitnshtuknmj,
a.KGI_TEN_ITR_BNG kngitnshtuichrnno,
fncConvertDate(trim(a.KNI_TNY_YYY),trim(a.KNI_TNY_MMM),trim(a.KNI_TNY_DDD)) knnitnnyuymd,
a.KNI_TNY_BGH_COD knnitnnyunohyucd,
a.KNI_TNY_BNR_BNG knnitnnyubnrino,
a.KNI_TNY_KNA_MOJ knnitnnyuknmj,
a.KNI_TNY_ITR_BNG knnitnnyuichrnno,
a.KNI_TNY_FKD_SDY_BNG knnitnnyufkjshdino
from
JDOSYA_JOHO a
left join SHYUKITI_JDUSHZI b on
a.KBN_COD = b.kbncd and
a.BNR_BNG = b.bnrinocd and
a.KNA_MOJ = b.knmj and
a.ITR_BNG = b.ichrnno;
执行计划什么的,表是否被分析过,都有哪些索引,数据量都是多少最好是把表结构和相关索引信息都帖一下.
然后设置以下事件. 找到相关的trace文件帖出来.alter session set events '10046 trace name context forever,level 8';select
a.KBN_COD turkno_kbncd,
a.BNR_BNG turkno_bnrino,
a.KNA_MOJ turkno_knmj,
a.ITR_BNG turkno_ichrnno,
fncConvertDate(a.RRK_YYY,a.RRK_MMM,a.RRK_DDD) rrkymd,
1 rirekiNo,
'000000' jichtiCd,
fncSetHzk_Jkk(a.DBK_DAT_SSI_HZK, a.DBK_DAT_SSI_JKK) shkturkgyumnchj,
fncSetHzk_Jkk(a.DBK_DAT_UPD_HZK, a.DBK_DAT_UPD_JKK) kusngyumnchj,
fncSetHzk_Jkk(a.DBK_DAT_UPD_HZK, a.DBK_DAT_UPD_JKK) kusnsystemnchj,
'ikou' kusncompmi,
'ikou' kusnuserid,
'1' yukoFlag,
'0' kssijouti,
'000000' kyujchticd,
fncConvertDate(a.SYD_TRK_YYY,a.SYD_TRK_MMM,0) shdturknngtu,
fncConvertDate(a.TRK_YYY,a.TRK_MMM,a.TRK_DDD) turkb,
fncConvertDate(a.SKN_YYY,a.SKN_MMM,a.SKN_DDD) shknb,
fncConvertDate(a.BNP_SYR_YYY,a.BNP_SYR_MMM,a.BNP_SYR_DDD) bnpishrb,
a.GYM_SBT_COD gyumshbtucd,
fncChangeCode('17',a.YOT_COD) yutbnpijouhucd,
a.KTS_SIT_BNG ktskstino,
a.RIB_KBN_BNG ribtukbnno,
fncChangeCode('18',a.KEJ_COD) bnpikijoucd,
fncChangeCode('19',a.TEI_KBN) tiinkbn,
a.TEI_1 tiin1,
a.TEI_2 tiin2,
a.SSK_1 sksiryu1,
a.SSK_2 sksiryu2,
a.SYA_JRY shryujuuryu,
0 shryusujuuryu1,
0 shryusujuuryu2,
a.SYA_TKS shryutks,
a.SYA_NGS shryungs,
a.SYA_HBA shryuhb,
fncChangeCode('20',a.NER_COD) nnryucd,
fncChangeCode('21',a.TOS_COD) tshkcd,
(case when nvl(a.HGT_COD,' ') <> ' ' then fncChangeCode('22',a.HGT_COD) else fncChangeCode('22',b.税率特例コード) end) hishtugstkgucd,
fncChangeCode('23',a.KTS_COD) ktskcd,
a.KTS_BNG ktskno,
fncChangeCode('24',a.GND_SBT_COD) bnpiskbtucd,
a.GND_KTS jdushgndukktsk,
a.HKR_SBT_COD hikryushbtucd,
a.HKR jdushhikryu,
fncChangeCode('25',a.MAR_COD) mkcdbnpijouhu,
a.SME shmi,
a.SDY_BNG shdino,
(case when nvl(a.KAZ_COD,' ') <> ' ' then
case when nvl(a.KAZ_COD,' ') = 'N'
then
case when nvl(a.KAZ_HRY_GEK_COD,' ') = '5'
then
'55'
else
fncChangeCode('32',a.KAZ_HRY_GEK_COD)
end
else
fncChangeCode('31',a.KAZ_COD)
end
else
case when nvl(a.KAZ_HRY_GEK_COD,' ') = '5'
then
'75'
end
end) kzicdjdushzi,
fncConvertDate(a.KAZ_COD_HEN_YYY,a.KAZ_COD_HEN_MMM,a.KAZ_COD_HEN_DDD) kzicdhnkub,
fncConvertDate(a.KAZ_COD_KTG_YYY,a.KAZ_COD_KTG_MMM,a.KAZ_COD_KTG_DDD) kzicdktugb,
fncConvertDate(a.KAZ_COD_SNI_YYY,a.KAZ_COD_SNI_MMM,a.KAZ_COD_SNI_DDD) kzicdsnsib,
'0' || a.KNZ_COD knzicd,
(case when nvl(b.shyushkbn,' ') = '1' then '1'
when nvl(b.shyushkbn,' ') = '0' then
case when nvl(a.RIS_COD,' ') = '1' then '4'
when nvl(a.RIS_COD,' ') = '0' then
case when nvl(b.減免等コード,' ') = '2' then '3'
else '2'
end
end
else
'6'
end) shyukiti,
fncChangeCode('26',a.YOT_KBN) yuttukiyucd,
(case when a.HKO_TIS_FLG = '' then '' when a.HKO_TIS_FLG = '1' then '1' else null end) hkkuhryuflg,
a.ZIR_COD jdushzirtucd,
a.ZGK zigk,
a.JTI_BIT joutibtt,
a.SME_FMI_FLG shmifmiflg,
fncChangeCode('27',a.RDT_KAZ_FLG) grnkkiktishukbn,
fncChangeCode('28',a.YOT_KBN) bskzikbn,
fncChangeCode('29',a.YOT_COD) eijcd,
null syunhnky,
null shtkgiin,
fncConvertDate(a.SKN_YYY,a.SKN_MMM,a.SKN_DDD) shknmnryub,
null yutsnkkshcd,
null snkkshshbtucd,
a.GRN_FGU grnkrtufgu,
a.GRN_PER grnkrtupsnt,
null tinnpshtkricd,
a.NNP_KJN_005 nnpkjuntssi,
fncConvertDate(trim(a.KGI_TEN_RRK_YYY),trim(a.KGI_TEN_RRK_MMM),trim(a.KGI_TEN_RRK_DDD)) kngitnshturrkymd,
a.KGI_TEN_BGH_COD kngitnshtunohyucd,
a.KGI_TEN_BNR_BNG kngitnshtubnrino,
a.KGI_TEN_KNA_MOJ kngitnshtuknmj,
a.KGI_TEN_ITR_BNG kngitnshtuichrnno,
fncConvertDate(trim(a.KNI_TNY_YYY),trim(a.KNI_TNY_MMM),trim(a.KNI_TNY_DDD)) knnitnnyuymd,
a.KNI_TNY_BGH_COD knnitnnyunohyucd,
a.KNI_TNY_BNR_BNG knnitnnyubnrino,
a.KNI_TNY_KNA_MOJ knnitnnyuknmj,
a.KNI_TNY_ITR_BNG knnitnnyuichrnno,
a.KNI_TNY_FKD_SDY_BNG knnitnnyufkjshdino
from
JDOSYA_JOHO a
left join SHYUKITI_JDUSHZI b on
a.KBN_COD = b.kbncd and
a.BNR_BNG = b.bnrinocd and
a.KNA_MOJ = b.knmj and
a.ITR_BNG = b.ichrnno;