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

[xueqs]

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;

[feifei19850830]

用如果数据源表很大的话建议用并行操作
execute immediate 'alter session enable palallel DML'
或者先把用到字段放在一个小的临时表里，然后在往事实表里插入数据

[lwmonster]

帮顶，不知道哪的问题，我觉得这个语句不是很复杂啊

[mzl_mzl]

从楼主说的数据量在300万，那么KZICHUTI_JDUSHZI 表是大表
从你的sql看，做了全表访问！这个没有办法
从你左连接M_KEY 表来看，不妨创建substr(xx)函数索引，
例如a.KNZ_COD = substr(b.OLD_KNZ_COD,1,1) 连接中创建M_KEY的substr(b.OLD_KNZ_COD,1,1)函数索引
这样可以走索引，访问数据可能会快些
但因为你的KZICHUTI_JDUSHZI表走了全表访问，故不会快很多！

最好能把你的那断sql的执行计划贴出来，这样有针对性的帮你优化

[kevinpan2009]

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);

把能够排除掉越多的越放在下面，SQL语句中限定条件的执行顺序是自下而上的，还有如2楼的，可以把WHEN换成DECODE试试

[superhsj]

支持case when 换成decode

[pathuang68]

请楼主参考：Oracle Sql 语句性能调优

[liuyi8903]

from
KZICHUTI_JDUSHZI

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);

这两个表的大小什么情况呢？

索引情况怎么样的？

表的统计信息是正确？

方便把执行计划帖出来吗？

只需要把select部分帖出来看看．

[inthirties]

关注

[davidbjh]

关注，SQL语句爱好者.

[mmqingfeng]

没提供表结构，没提供索引，没提供数据量，没提供执行计划
上来就问为什么

哎，无语啊……

[sp4]

换成decode也没有什么用，但是执行一小时还是太长了。

逻辑结构应该还是有问题，最好给组织个具体数据，表名字好长，不知道是不是我看错。不好下手

[wskbuaa1984]

应该要在某些字段加上个索引吧.这样就快了些.如果你已经加了索引,就尝试优化一下sql语句吧

[xmanliming]

表是否走了索引？你的sql看起来像统计一个表报，怕浪费时间的话 写成过程放在后台执行 每天都要统计的话就配置上crontab

[oonxt]

楼上正解。

[zzyzgydotnet]

up

[suncrafted]

1、where语句中，过滤越多的语句，放在越后面
2、case when 用decode代替
3、select中的2个表建立合适的索引

[supercrazy008]

如果select出来的数据很多的话，在insert语句加上no loggig关键字，不写日志效率会提高很多。

[yangsharp]

好长的sql，看看先。

[ruihuahan]

数据量在300万左右，inseert, 没有 where 过滤。

楼主要做什么？数据迁移？

可以试试 direct load insert 语法来加快速度：

INSERT /*+ APPEND */ INTO tab_a
SELECT xx,xxx,... FROM tab_b;
COMMIT;

还可以临时修改表的 logging mode：
ALTER TABLE tab_a NOLOGGING;

direct load insert 的其他参数用法，楼主可以 google 一下。

[friendjin]

1.insert /*+ append */ into KZICHUTI_JDUSHZI1
2.优化连接操作，可以调整物理设计，比方说分区啊，通过分区内连接来实现即可

不过你现在300万数据量需要一个小时也太夸张了，检查以下你的数据分布模式特征吧