巨大的挑战-oracle 语句优化 数据量在300万左右 现执行要1小时-汗

CREATE OR REPLACE procedure NADORA1.prcMake_New_020
as
begin
insert into KZICHUTI_JDUSHZI1
(
DBK_REC_IDD,
DBK_DAT_SSI_HZK,
DBK_DAT_SSI_JKK,
DBK_DAT_UPD_HZK,
DBK_DAT_UPD_JKK,
DBK_FIL_001,
ZMK_COD,
KNZ_COD,
KBN_COD,
BNR_BNG,
KNA_MOJ,
ITR_BNG,
CKD,
FKA_NND,
HAS_NND,
HKZ_JDS_MHN,
KII_COD,
KAZ_CTI_SQS,
KAZ_COD,
KAZ_HRY_GEK_COD,
KAZ_COD_HNK_YYY,
KAZ_COD_HNK_MMM,
KAZ_COD_HNK_DDD,
KAZ_COD_KER_YYY,
KAZ_COD_KER_MMM,
KAZ_COD_KER_DDD,
KAZ_COD_SNI_YYY,
KAZ_COD_SNI_MMM,
KAZ_COD_SNI_DDD,
ZIR_COD,
CTI_YYY,
CTI_MMM,
CAL_GAK,
GEK,
ZGK,
TRI_YYY,
TRI_MMM,
TRI_DDD,
CTI_ZGN_GAK,
TAI_YYY,
TAI_MMM,
TAI_JUN,
CTI_NND,
SYD_TRK_YYY,
SYD_TRK_MMM,
NER_COD,
TEP_FLG,
RDT_KAZ_FLG,
GRN_FGU,
GRN_PER,
NNP_KJN_005,
YBI
)
select
a.DBK_REC_IDD DBK_REC_IDD,
a.DBK_DAT_SSI_HZK DBK_DAT_SSI_HZK,
a.DBK_DAT_SSI_JKK DBK_DAT_SSI_JKK,
a.DBK_DAT_UPD_HZK DBK_DAT_UPD_HZK,
a.DBK_DAT_UPD_JKK DBK_DAT_UPD_JKK,
a.DBK_FIL_001 DBK_FIL_001,
(case when NVL(b.NEW_ZMK_COD,' ') = ' ' then a.ZMK_COD else b.NEW_ZMK_COD end) ZMK_COD,
(case when NVL(substr(b.NEW_KNZ_COD,2,1),' ') = ' ' then a.KNZ_COD else substr(b.NEW_KNZ_COD,2,1) end) KNZ_COD,
substr(b.NEW_KZI_BNG,1,1) KBN_COD,
(case when NVL(substr(b.NEW_KZI_BNG,2,3),' ') = ' ' then a.BNR_BNG else cast(substr(b.NEW_KZI_BNG,2,3) as char(3)) end) BNR_BNG,
(case when NVL(substr(b.NEW_KZI_BNG,5,1),' ') = ' ' then a.KNA_MOJ else cast(substr(b.NEW_KZI_BNG,5,1) as nvarchar2(1)) end) KNA_MOJ,
(case when NVL(substr(b.NEW_KZI_BNG,6,4),' ') = ' ' then a.ITR_BNG else cast(substr(b.NEW_KZI_BNG,6,4) as char(4)) end) ITR_BNG,
(case when NVL(substr(b.NEW_KZI_BNG,10,1),' ') = ' ' then a.CKD else cast(substr(b.NEW_KZI_BNG,10,1) as char(1)) end) CKD,
(case when NVL(substr(b.NEW_KBT,1,4),' ') = ' ' then a.FKA_NND else cast(substr(b.NEW_KBT,1,4) as number) end) FKA_NND,
(case when NVL(substr(b.NEW_KBT,5,4),' ') = ' ' then a.HAS_NND else cast(substr(b.NEW_KBT,5,4) as number) end) HAS_NND,
(case when NVL(substr(b.NEW_KBT,9,1),' ') = ' ' then a.HKZ_JDS_MHN else substr(b.NEW_KBT,9,1) end) HKZ_JDS_MHN,
(case when nvl(b.OLD_CTG,'') = '' then a.KII_COD else OLD_CTG end) KII_COD,
a.KAZ_CTI_SQS KAZ_CTI_SQS,
a.KAZ_COD KAZ_COD,
a.KAZ_HRY_GEK_COD KAZ_HRY_GEK_COD,
a.KAZ_COD_HNK_YYY KAZ_COD_HNK_YYY,
a.KAZ_COD_HNK_MMM KAZ_COD_HNK_MMM,
a.KAZ_COD_HNK_DDD KAZ_COD_HNK_DDD,
a.KAZ_COD_KER_YYY KAZ_COD_KER_YYY,
a.KAZ_COD_KER_MMM KAZ_COD_KER_MMM,
a.KAZ_COD_KER_DDD KAZ_COD_KER_DDD,
a.KAZ_COD_SNI_YYY KAZ_COD_SNI_YYY,
a.KAZ_COD_SNI_MMM KAZ_COD_SNI_MMM,
a.KAZ_COD_SNI_DDD KAZ_COD_SNI_DDD,
a.ZIR_COD ZIR_COD,
a.CTI_YYY CTI_YYY,
a.CTI_MMM CTI_MMM,
a.CAL_GAK CAL_GAK,
a.GEK GEK,
a.ZGK ZGK,
a.TRI_YYY TRI_YYY,
a.TRI_MMM TRI_MMM,
a.TRI_DDD TRI_DDD,
a.CTI_ZGN_GAK CTI_ZGN_GAK,
a.TAI_YYY TAI_YYY,
a.TAI_MMM TAI_MMM,
a.TAI_JUN TAI_JUN,
a.CTI_NND CTI_NND,
a.SYD_TRK_YYY SYD_TRK_YYY,
a.SYD_TRK_MMM SYD_TRK_MMM,
a.NER_COD NER_COD,
a.TEP_FLG TEP_FLG,
a.RDT_KAZ_FLG RDT_KAZ_FLG,
a.GRN_FGU GRN_FGU,
a.GRN_PER GRN_PER,
a.NNP_KJN_005 NNP_KJN_005,
a.YBI YBI
from
KZICHUTI_JDUSHZI aKZICHUTI_JDUSHZI1
left join M_KEY b on
a.ZMK_COD = b.OLD_ZMK_COD and
a.KNZ_COD = substr(b.OLD_KNZ_COD,1,1) and
a.BNR_BNG = substr(b.OLD_KZI_BNG,1,3) and
a.KNA_MOJ = substr(b.OLD_KZI_BNG,4,1) and
a.ITR_BNG = substr(b.OLD_KZI_BNG,5,4) and
a.CKD = substr(b.OLD_KZI_BNG,9,1) and
a.FKA_NND = substr(b.OLD_KBT,1,4) and
a.HAS_NND = substr(b.OLD_KBT,5,4) and
a.HKZ_JDS_MHN = substr(b.OLD_KBT,9,1);
exception
when others then
dbms_output.put_line('other execption raised');
end;