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

xueqs 2009-05-20 02:00:27
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;
...全文
288 点赞 收藏 28
写回复
28 条回复
friendjin 2009年06月01日
1.insert /*+ append */ into KZICHUTI_JDUSHZI1
2.优化连接操作,可以调整物理设计,比方说分区啊,通过分区内连接来实现即可

不过你现在300万数据量需要一个小时也太夸张了,检查以下你的数据分布模式特征吧
回复 点赞
ruihuahan 2009年05月31日
数据量在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 一下。

回复 点赞
yangsharp 2009年05月31日
好长的sql,看看先。
回复 点赞
supercrazy008 2009年05月31日
如果select出来的数据很多的话,在insert语句加上no loggig关键字,不写日志效率会提高很多。
回复 点赞
suncrafted 2009年05月27日
1、where语句中,过滤越多的语句,放在越后面
2、case when 用decode代替
3、select中的2个表建立合适的索引
回复 点赞
zzyzgydotnet 2009年05月27日
up
回复 点赞
oonxt 2009年05月27日
楼上正解。
回复 点赞
xmanliming 2009年05月25日
表是否走了索引?你的sql看起来像统计一个表报,怕浪费时间的话 写成过程放在后台执行 每天都要统计的话就配置上crontab
回复 点赞
wskbuaa1984 2009年05月25日
应该要在某些字段加上个索引吧.这样就快了些.如果你已经加了索引,就尝试优化一下sql语句吧
回复 点赞
sp4 2009年05月25日
换成decode也没有什么用,但是执行一小时还是太长了。

逻辑结构应该还是有问题,最好给组织个具体数据,表名字好长,不知道是不是我看错。不好下手
回复 点赞
mmqingfeng 2009年05月24日
没提供表结构,没提供索引,没提供数据量,没提供执行计划
上来就问为什么

哎,无语啊……
回复 点赞
davidbjh 2009年05月21日
关注,SQL语句爱好者.
回复 点赞
inthirties 2009年05月21日
关注
回复 点赞
liuyi8903 2009年05月21日
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部分帖出来看看.

回复 点赞
pathuang68 2009年05月21日
回复 点赞
superhsj 2009年05月21日
支持case when 换成decode
回复 点赞
kevinpan2009 2009年05月21日
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试试
回复 点赞
mzl_mzl 2009年05月21日
从楼主说的数据量在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的执行计划贴出来,这样有针对性的帮你优化
回复 点赞
lwmonster 2009年05月20日
帮顶,不知道哪的问题,我觉得这个语句不是很复杂啊
回复 点赞
feifei19850830 2009年05月20日
用如果数据源表很大的话建议用并行操作
execute immediate 'alter session enable palallel DML'
或者先把用到字段放在一个小的临时表里,然后在往事实表里插入数据
回复 点赞
发动态
发帖子
Oracle 高级技术
创建于2007-09-28

2929

社区成员

1.8w+

社区内容

Oracle 高级技术相关讨论专区
社区公告
暂无公告